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?

I have the exact same situation.

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

Cheers.

I did spend some time learning VBA for Excel and I did write a successful script finally...at 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.

These are IMPORTANT NOTES:

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.

SETUP:

==== 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.

Peatawn said:

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.

Regards,
Peatawn

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...