Data clean up for pivot use from data dump

Hi all,
I have a problem trying to format a massive set of data that i have where it has about 2000 entries, for about 80 different companies each of which have the same sets of data each.
The layout is thus :
Company X1
Region / specifics R1
Data.........say 10 rows with 12 columns
Company X2
Region / Specifics R2
Data.... say 25 rows with 12 columns

What i want to do is to take this data and make it pivot friendly by dumping the company name X1 with every one of the 10 rows and similarly for R1 and X2, R2.
so then i have a massive
2000 entries by 14/15 columns with all the information.
(in the code, OCS # represents a heading for column A (old before we insert two columns
Most of this was pieced together from code on this site.
The inserting of columns works fine, but it never gets into the loop.
The tricky part for me is that not all the data has the same company name
Any tips?
Shr

Please Login or Register  to view this content.

HI

Assuming that your companies are in column A starting in row 2, and your Regions are in column B starting in row 2, and column C will always have data to determine your last row, then

Please Login or Register  to view this content.

should get you there.


rylo

Rylo,
Thanks for your response.
maybe I was not clear the present data is thus
cell E12, E29,E55, etc are different company names
ROW 15-20 are set of data for comp 1
Row 32-44 is for comp 2
and so on.
and the data elements are arranged like this
1Company xxx
2other info.......
13 heading row for data
14 Code - type - size - information
15 Code - type - size - information
16 Code - type - size - information

then same layout repeats for a diff company
11Company xxx
20other info.......
23 heading row for data
24 Code - type - size - information
25 Code - type - size - information
26 Code - type - size - information

Hence I need some way to look for the heading row - then go offset (X1,Y1) above it to find the company name and and offset (X2,Y2) for other info
Then repeat same info for next header row and then take each of those bits of information and dump it on adjacent columns
so then, the new column 14-16 will be
14 comName - Info - Code - type - size - info

I hope this is clear?
Thanks
Shr

Hi

Can you put up a sample file with a before and after sheet, that shows your starting data structure and how the data is to be reformatted.


rylo