Consolidating & Transposing Data

[misty0705]

I have a spreadsheet with a lot of data on it. I basically want to take four of he columns and put them on a new sheet. I want to consolidate them and take one of the columns and put the data in a row instead of a column.

Example of what it looks like..

Column 1 Column 2 Column 3 Column 4
212 B041 $2,000.00 5001
212 B042 $2,000.00 5002
212 B043 $2,000.00 5002
212 B045 $2,000.00 5002
212 B046 $450.00 5003
212 B046 $700.00 5003
213 B047 $200.00 5004
214 B048 $700.00 5005
214 B049 $1,100.00 5005
214 B049 $1,100.00 5005
214 B049 $1,200.00 5005
214 B049 $3,000.00 5005
214 B049 $1,320.00 5005
214 B049 $600.00 5005


Example of what I want it to look like
Column 1 Column 2 Column 4 Column 3
212 B041 5001 $2,000.00
212 B042 5002 $2,000.00 $2,000.00 $2,000.00 $450.00 $700.00
213 B047 5004 $200.00
214 B048 5005 $700.00 $1,100.00 $1,100.00 $1,200.00$3,000.00 $1,320.00 $600.00

Thanks for your help!

[JBeaucaire]

Why did you put 5002 and 5003 entries in the same row, while all the other rows are exclusive? Was that an error?

[misty0705]

that was a typo...i was trying to put a quick example together

[JBeaucaire]

I that case, try this macro... it will duplicate the sheet and reformat it as per your example:

Please Login or Register  to view this content.

============
How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use.

Press Alt-F8 and select ReformatData from the macro list.