Macro to pull info from column down to other cells

[luajambeiro]

Hi!

I wonder if someone can help me with a macro that I can't seem to figure out.

I export a report from another system into Excel and it comes out with headers on the top of the left column and the data related to those headers on the right column below. I'd like to know if there's a macro that can pull the headers down to be next to the data until it reaches the next header and then repeats the same for that one and so on and then deletes the original headers. The count of rows for each category can be different every time I generate the report as is the total row count.

So basically, "Case A" is the header in A2 and Bottles, wood, boxes, etc. are the items for this category that are in B3, B4, B5, etc. I'd like a macro where Case A gets placed in A3, A4, A5, etc and then deleted from A2, so it's only to the left of the actual items from that category.

I uploaded the excel file here to show how it looks and below is how I'd like it to look after I run the macro.

How I want the report to look:

Case A Bottles 1,000.00
Case A Wood 1,000.00
Case A Boxes 6,000.00
Case A Furniture 1,000.00
Case A Media 3,000.00
Case A Paper 2,000.00
Case B DX 300.00
Case B Domain 3,300.00
Case B Bio 39,000.00
Case B Surgical 700.00
Case B Test 3,000.00
Case C Engineer 1,000.00
Case C Planning 3,500.00
Case C Mgmt 1,000.00
Case C Coding 1,750.00


Thanks in advance!

[SjonR]

try this one:

Please Login or Register  to view this content.

[luajambeiro]

Thanks so much SjonR. It works! Just one question.. Is there something I can add to the macro to delete the blank rows that are left after it deletes the original headers so that there are no spaces between each category?

[Fluff13]

How about

Please Login or Register  to view this content.

[luajambeiro]

Awesome! Thanks so much!!

[SjonR]

try:

Please Login or Register  to view this content.

[Fluff13]

Awesome! Thanks so much!!

You're welcome & thanks for the feedback