Various Concat (or any other smart solution) needed -1000 lines messy table

I have four of these 10,000 lines long excel files that i need to cleanup. I've spent about 3 days pushing various VBA code modifications...but in most reached "solutions" I'm loosing quiet lot of data.

Please see example in the attachment

Need to somehow smartly concat all records or use any other solution to reach desired look of table.

Primary sort key (for the range) is column A. Each record has min 3-503 lines. Primary key is unique number that repeats only throughout record.

Columns B-E, all cells of same range in each column should concat into first row of record, respectively.

Column F has key values (up to 500 lines) and should remain in rows.

Data from columns B-E, should be copied after concat for all key values/primary key.

Any help or advice is highly appreciative.

Thanx in advance

Assumes that you do not have headers in row 1, key is column A and the final output looks like you showed - two errors fixed.

Please Login or Register  to view this content.