Windows 7 Forums

Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.

Windows 7: Excel: Combining Rows Based on An Equal Value in One Column

22 Jun 2012   #1

Windows 7 Home Premium 64-bit
Excel: Combining Rows Based on An Equal Value in One Column

I have attached two images of an Excel worksheet. I have gone to the trouble of taking multiple sets of three columns of associated data, that is data of x, y, z where y and z are associated with x. In building a new table with six sets of data, I put x1, x2, ..., x6 in a single column, and then spaced out y1/z1, y2/z2, ..., y6/z6 in separate columns before a sort. I then sorted on values x (all x1 to x6 in a single column).

The result is shown in the first image "pre-combine-rows"

You can see that in column "all x," many rows have identical values: for example there are five rows with value x = 218.9, and six rows with value x = 224.9. I want to combine all those rows, which combines also the values in the columns y1/z1 through y6/z6.

The result should look like the second image: "post-combine-rows"

20 rows has been combined into 5 rows.

This is a walk in the park for Excel, right? Does not even need a macro, right?

Is this one of these Database/Criteria setups?

Attached Thumbnails
Excel: Combining Rows Based on An Equal Value in One Column-pre-combine-rows.png   Excel: Combining Rows Based on An Equal Value in One Column-post-combine-rows.png  
My System SpecsSystem Spec
18 Jul 2012   #2

Windows 7 Home (x64)

Hi mavigozler,

You wouldn't require a macro to complete this feat. You can achieve the wanted result with formulas. If you still need help with this issue, I'll be glad to elaborate a formula for you.


Edit: You can also have a look at the Excel matrix feature...
My System SpecsSystem Spec
30 Aug 2012   #3

Windows XP Pro 32bit

I have the exact same situation.

It would be greatly appreciated if you can share how this can be best achieved with formulas . . .

My System SpecsSystem Spec

01 Sep 2012   #4

Windows 7 Home Premium 64-bit

I did spend some time learning VBA for Excel and I did write a successful script least it did what I wanted it to.

This was exported from Excel as a .bas file, but I renamed it to a .vbs so I could inspect it with my syntax highlighting editor (RJ Text Edit--recommended! As good or better than UltraEdit).

The macro you want is attached to this post. Just import it into your Excel macro workbook, or cut and paste it to the macros you create in Excel.

There is the on Sub and one Function. The Function is called by the Sub to check worksheets.


1. This macro is designed NOT TO DESTROY YOUR EXISTING WORKSHEET. It copies the selected area that you want sorted, creates a new worksheet, and then does the sorting there. You can destroy the other worksheet at your leisure.

2. It sorts on the the leftmost selected column: I did not go to the trouble of creating a sort where you can choose the column of or even levels of columns like a standard Excel sort, and then pre-processing in that way. I am sure it can be done by extending the functionality of the macro, and I applaud you if you want to have a go at it.

3. The code is not heavily commented. In a perfect world, I would write detailed documentation (perhaps create an interactive help dialog box) and I would set up an initializing dialog box which you would invoke. This user input would at least allow you to select the worksheet cell region to be sorted, and probably set other options. I can think of a million ways to make this very elegant and holding the hand of the user.

4. I noticed something in my data. In my leftmost column on which the sort was done, I would have numeric data which might be row 17: 587.2848 and the next number below it is row 18: 587.3343. These numbers were in ascending order, but I am not sure that is required for this macro. Anyway, I had number formatting set to "0.0", which means these round to the same number: 587.3. Now suppose that the 4th column from those two rows has data in it: you cannot crash two numbers together in that column! So I had the macro look at row 18 column 4, and determine if the row where all the numbers are combined and having the value 587.3 already had a number in the 4th column. If it does, I kept row 18 by itself and I style all the characters in that row as RED-COLORED BOLD font. That easily draws your attention to that specific problem. I would only see this happen 1 in 100 row values or even less. It depends on your data, and how you set the number format for rounding.

In the code, I have commented the NumberFormatting that you may need to tweak to get what you want.

5. Time delay at the end of sorting: after the sorting is visually finished as your macro works, there is a few seconds or so where the macro seems to be running still before it exits and releases the work to you. I did not try to determine what was causing the time delay.


==== THE PROBLEM ====
1. Suppose you have several analytical runs from an instrument, in which you have two-dimensional or two-coordinate data. E.g. in chromatography you have retention time data on the x-axis and signal data of some kind on the y-axis. If you use a mass spectrometer, you get a mass spectrum: mass-to-charge (m/z) on the x-axis and intensity/ion counts on the y-axis. You only need that data: x-y data.

2. Now suppose you have several different runs: run 1, run 2, run 3. What you would like to do in Excel is have one column of the x values, since they are same for each of the runs (but don't have to be) and you want to the right columns for each of the Y (signal/response) values. As an example, say each run has 10 points (10 x-y pairs). You will probably do the following:

a) On the Excel sheet, you put the run 1 data in the region A1:B10. You probably have run 2 data on C1:D10. Run 3 is E1:F10.
b) In order to unify run 2 and run 3 data with run 1, you cut-and-paste run 2 x data to A11:A20, and you cut-and-paste run 2 y data to C11:C20. Run 3 x data is cut and paste to A21:A30, and the y data is cut-and-paste to D21:D30. You must make sure that the paired data always inhabits the same rows and that data from another run is never mixed. If you have several runs, you will just keep doing this operation of cutting and pasting. Each y data on each run must have its own unique column!! But all the x data is in the same column.
c) when you are done, you must select to encompass all the cells. For the 3-run example, you would select cells A1:D30.
d) Now do a standard Excel smallest->largest on column A (all the x data).

At this point, Excel has nothing for you to unify the y data in which the x values are equal in the rows. This is where my macro comes in.

==== THE SOLUTION ====

4. It's simple. Just re-select A1:D30 region on which you did the standard Excel sort, and run the macro. Where the x values are equal in the number format, all the y values in the columns are combined into one row. It's all copied to a new worksheet, where the work is done, and your original data is not touched. You will hopefully notice that half of your rows or more are reduced in number, depending on your data and on the number of runs. The more runs (y data), the more the row count reduction.

If you don't get row reduction, then your x values are not equal according to Excel. If you see too many red values, your number format is probably too narrow...too few decimal points.

The main algorithm is in the big While/Wend loop. Equality tests are done in Excel, so it may depend on how Excel does numeric comparisons.

This thing might sort text too (but why?) It was made for numeric data generated in several runs of an instrument that generates the same type of data.

Good luck. I read through the parts of couple of books on Excel VBA and googled heavily to get examples and quick answers (there's a lot on the web). It might be worth it for someone to expand. For example, if I had the time, I would create a user dialog to initialize the setup.

Attached Files
File Type: vbs CombiningColumnarData.vbs (4.0 KB, 120 views)
My System SpecsSystem Spec
01 Sep 2012   #5

Windows 7 Home Premium 64-bit

Quote   Quote: Originally Posted by Peatawn View Post
Hi mavigozler,

You wouldn't require a macro to complete this feat. You can achieve the wanted result with formulas. If you still need help with this issue, I'll be glad to elaborate a formula for you.


Edit: You can also have a look at the Excel matrix feature...
So with Excel functions and formulas, you can transform what is on the image at left into the image on the right? Please, I am interested...
My System SpecsSystem Spec

 Excel: Combining Rows Based on An Equal Value in One Column

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Similar help and support threads
Thread Forum
WMC:- How do I increase # of rows in the EPG?
The standard 7-row WMC EPG on my Windows 7 64-bit system wastes a lot of screen space I want to increase number of rows to 9, 10 or 11 I've seen instructions for doing this on a couple of sites, but the reader comments indicate a high failure rate Can anyone point me to reliable instructions...
Media Center
Make Excel 2010 Automatically Organize by Column "A" with Header
Hi all, I'm working on an excel spreadsheet for my passwords. My columns are labeled website, username and password, respectively. I know how to go to data>sort to sort what I have so far, but is there a way for me to be able to put a new website, username and password at the bottom of the list,...
Microsoft Office
Document in Excel is only printing one column per page.
I am trying to print a document in Excel Starter 2010. The document contains the columns A to J and rows 1 to 466. When I try to print the document, only one column prints per page. I have tried selecting all the columns and changing the print selection but this just shrinks the column size, and...
Microsoft Office
Re: EXCEL- resorting rows so they each relate to original rows
Hi, I couldn't find an answer in the Forums for this in the past half hour of looking through them- there are so many, and I need an answer sooner than in the 4 or 5 days it will take me to get through them in HOPES of finding my answer. Sorry. So could someone who knows Excel in Windows 7...
Microsoft Office
How to combine fields in Access 2010 based on 2 equal fields?
Hi all, Iím really in need of some help from some Access experts! Iím a newbie to Access, but I got myself busy with quite a big project... Iím trying to map out the product supply to shops in the whole country. Iíve come quite far for a starter, but I keep having 3 questions, it would be great...
Excel 2010 - Accessing data based on conditions from other sheets
I've tried goggling this a bit and can't seem to find exactly what I'm looking for. I'm trying to setup a sheet that can run calculations based upon what someone else enters (ex: if they select 'option1', preferably from a drop down list, then two values will be set to the corresponding values,...
Microsoft Office

Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

© Designer Media Ltd

All times are GMT -5. The time now is 17:24.

Twitter Facebook Google+

Windows 7 Forums

Seven Forums Android App Seven Forums IOS App