Converting like data in rows to column

[April.Ryniewicz]

I am not a macro expert and I've spent a better part of a day trying to find this online but can't seem to find anything that is as complex as I required. Basically this is what I need:

I have a spreadsheet that is a data dump from a system, the data comes out in separate rows for a single "fee type", I'm looking for a systematic (macro) way to convert the multiple rows into a single row and insert the 'differentiating' data into a column. Attached is an example of the data dump from the system and theoretically what I'd like the data to be converted to make it easier to query.

[JBeaucaire]

It's not as complex as you suspect. This can be done with some simple formulas. I've added a sheet2 and input the formulas that accomplish this.

You need only extend the data on sheet1, then put the UNIQUE codes from column A sheet1 into column A of sheet2. Then copy the rest of the cells with formulas downward to bring the results over.

The simplest way to get the unique codes from column A is to use the Data > Advanced Filter > Filter in place > Unique Values only. Then copy them and paste them into sheet two.

[April.Ryniewicz]

It's not as complex as you suspect. This can be done with some simple formulas. I've added a sheet2 and input the formulas that accomplish this.

You need only extend the data on sheet1, then put the UNIQUE codes from column A sheet1 into column A of sheet2. Then copy the rest of the cells with formulas downward to bring the results over.

The simplest way to get the unique codes from column A is to use the Data > Advanced Filter > Filter in place > Unique Values only. Then copy them and paste them into sheet two.

Hi,

Thank you for the quick response. The reason I'm asking for a macro is that this data set can be 10,000+ lines. I will try your suggestion and see how it works out

[JBeaucaire]

Whether the raw data is 100 rows or 10,000 rows, it's same amount of time, about 30 seconds, now that the template formulas are done for you.

1) Do an AdvancedFilter to extract the unique codes from column A and put them in column A of the new sheet.
2) Copy the remaining cells from row1 down as far as needed.


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.