Transpose, Insert Row, VLOOKUP? Desired Data hard to work out...

[Overkill]

Hello Good People of Excel Forum,

I've attached two pictures, "CurrentData"
CurrentData.PNG
and "DesiredData."
DesiredData.PNG
Self explanatory? The problem, in simple words, is that I need to get to a sort of transposed and clean version of the original data (6000 lines).

The original data is varied so that this formula won't work:

Please Login or Register  to view this content.

Maybe I missed an obvious way to just transpose and VLOOKUP or copy>paste the data into a workable format, but I haven't come across a quick, macro-finish. My more recent idea was that I could insert blank rows between values in Column A until the total number of rows between each cell with data = a constant. I think 14 rows between each "Customer #" (SAMPLE1 to SAMPLE2) would give me a chance to use the formula from above:

Please Login or Register  to view this content.

I don't have any idea if or how this could be done. Any ideas would be great. Thanks a bunch.

[Bernie Deitrick]

Insert a new row 1, with your headers that match the labels in column B (if you need to match them) and in cell D2, enter the formula

=IF($A2<>"",IF(COUNTIF(OFFSET($A2,0,0,COLUMN(B1),1),"<>")=1,OFFSET($A2,COLUMN(A2),2),""),"")

and copy to the right for as many columns as you expect the maximum rows per entry.

Then, for those columns that take their data - in order, and not based on matching the labels - from column B, change the ,2 of the last offset to ,1.

Then, when you need to match the header, change the formula to this: in this example, the cell is I2 (to do a vlookup based on the label in I1, like PHONE: ) This also assumes PHONE: will be within 14 rows of the label in column A:

=IF($A2<>"",IF(COUNTIF(OFFSET($A2,0,0,COLUMN(G1),1),"<>")=1,IFERROR(VLOOKUP(I$1,OFFSET($A2,0,1,14,2),2,FALSE),""),""),"")

Copy that row of formulas down to match your data set, copy all of the columns and paste values over the formulas, and finally sort everything based on column D. And then delete rows where D is blank.

[Overkill]

Thanks for the response. I will try this after I finish another project and verify if solved. Much appreciated!

[Overkill]

Awesome. Solved very quickly with your help. Thanks so much.