Move cell contents from rows into specific columns

[johnnywinter]

I can only get the attached XLS in a "report" layout from a database, there are reasons it will not be furnished in any other format- it is for 4,000 records- 32,000 rows and we get an updated version monthly, so reformatting it manually to 1 row per record is a chore.


It basically is an XLS that repeats every 8 rows (each 8 rows is for 1 record and the layout is the exact same every 8 rows)
but some cells contain "field names" and some cells contain "data"
one row has the "field name" in the cell immediately above the "data'
and the other 7 rows have the "field name" and "data" in adjoining cells on the same row

I would like to create an xls with the "field names" as column headings and have just 1 row per record with just the "data" in those cells.

The attached XLS has 3 records-- I used Bold black text for "field name" and highlighted the "data" cells in yellow.... I used red text to show the first row of the next record.

I have some experience with macros and VBA, and understand what needs to be done, but do not know how to get it repeat

Please point me in the right direction.

Thanks,

JW

[jmac1947]

Hello JW

I have some experience with macros and VBA, and understand what needs to be done, but do not know how to get it repeat

I have taken you at your word and havent put much actual functional code into the macro in the attached file.

There are many options for processing the input file, I have just taken a very simple approach to demonstrate the principles (I might consider arrays as a means of storing the fields instead of individual variables, not much difference except maybe less lines of code)

Happy to help further if you get stuck

jmac

JWsample macro.xlsm

[johnnywinter]

jmac1947: thanks for the push in the right direction.

Thanks

[jmac1947]

Hi JW,
you are welcome, as I said, happy to help further if you get stuck