Combining Column Values Into Single Row Based On One Column Having Equal Values In Multiple Rows

[mavigozler]

The title might be confusing but I have an example below to show what I want to do. In the image below ("pre-combine rows" image) I show a table of values which was generated by a multi-step process.

The steps in generating it are not really relevant to this problem, but for the curious, I detail the process at the end of the post.

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 image below ("post-combine-rows" image)

20 rows have been combined into 5 rows.


I imagine this is a walk in the park for Excel compared to some of the other procedures that can be done.


While I can read the VBA and Macros book, it will take a few days and much trial-and-error, and I am helping a student on a deadline. Thanks.

---

[The information below is not necessary or relevant to solving the problem, but is given for those wanting to know how the worksheet data came to look the "pre-combine" image]

How the "pre-combine" table data were made.

These values x, y, and z are data from a scientific instrument--a mass spectrometer---in which x is the mass-to-charge (symbolized as "m/z" in reality_), and y and z are the intensity of the value at x, expressed in different ways (y is absolute intensity, namely a count of ions, and z is the relative intensity, in which intensity is expressed as a percentage of the peak signal of the m/z having the greatest intensity)

So multiple datasets (instrument analyses) of these x-y-z associated values were generated.

Then:

1. Datasets were sorted from largest-to-smallest value based on column z (relative intensity).
2. The range of x-y-z cells where values of z from 100% to 7.5% were copied to another worksheet.
3. In pasting, the three columns were pasted with the x column in in the 'all x" column, and each dataset in a row just below the previously pasted dataset.
4. The y and z values were then separated (cut-and-pasted) into a column just one column to the right of the two columns containing y-z values of the previously cut-and-pasted y-z values of the previous dataset
5. The entire range of cells with the "all x" column and all the columns of y-z paired data were selected
6. A smallest-to-largest values sort was then done on the "all x" column, thus generating the table seen.

This was done manually for about 40-45 separate workbooks, for a total of perhaps 400-500 of these datasets.

If my VBA knowledge was similar to my knowledge of PHP/Javascript/HTML/CSS, I probably could have done this repetitive process automagically. The purpose of setting up this table is compare results between different analytical conditions.