Convert poorly formatted vendor data into usable data

Hi All,

I am trying to find a way to take data from vendors that my company utilizes and turn their contact info into usable data. We are taking data from a very old software and putting it into a new system, and the way this system exports data isn't at all friendly to usable data. I attached a file that demonstrated the issue. On the "sample" tab, there is a list of vendors to show how the data is laid out in its current format. This is a very small list of anonymized data, in the actual export there are 100's of these vendors going down the page in the exact same format. The result page shows how i'd like the data to look, and i used the first vendor as an example. If anyone has any suggestions, or needs more clarification, I'd be very appreciative.

Regards,
Nathan

If you want to do this through formulas, I would use the INDEX function to act as an OFFSET.

A2 =INDEX(Sample!D:D,ROWS($A$1:$A1)*22-18)
B2 =INDEX(Sample!F:F,ROWS($A$1:$A1)*22-21)
C2 =INDEX(Sample!I:I,ROWS($A$1:$A1)*22-18)
D2 =INDEX(Sample!C:C,ROWS($A$1:$A1)*22-15)
E2 =INDEX(Sample!D:D,ROWS($A$1:$A1)*22-11)

etc.

You want the end section (i.e. 22-n) to be equal to the row that the first instance of what you're looking for is on.
e.g.
22-18 = 4 (First instance of ID is in row 4)
22-21 = 1 (First instance of Vendor is in row 1)
etc.

Drag the formulas down the column as far as needed.

I'll let you do the rest.

This is perfect! Thank you so much, exactly what I needed.

You're welcome, happy to help. Thanks for the rep!

If that solved your question, please mark this thread as SOLVED.