Converting text to rows in a huge dataset

Question

https://drive.google.com/file/d/1rmgk5EyOR11zSIc5H0dNzLULpeim0G3T/view?usp=sharing
I've got a list of more than 1000 suppliers that are in the same format as in sheet 1, from an export.
I need to narrow this down to around 200 - I've got another spreadsheet with this list.
Sheet 2 shows the result I am trying to get. All I need to keep from sheet 1 is the supplier name and the info that is in C10 and D10 (from the top supplier)
I've played around with text to columns, transposing etc but had no luck, seems a rather difficult task - I think it may be easier if I could somehow get the text into rows besides the supplier code and supplier name, and then use v-lookup to remove the ones I don't need?

Justin Doward · Answer

Based on your example you can try a formula solution like this:

=OFFSET(INDIRECT("Sheet1!A" & MATCH(A1,Sheet1!$A$1:$A$12,FALSE)),0,1,1,1)
=OFFSET(INDIRECT("Sheet1!A" & MATCH(A1,Sheet1!$A$1:$A$12,FALSE)),9,1,1,1)
=OFFSET(INDIRECT("Sheet1!A" & MATCH(A1,Sheet1!$A$1:$A$12,FALSE)),9,2,1,1)
=OFFSET(INDIRECT("Sheet1!A" & MATCH(A1,Sheet1!$A$1:$A$12,FALSE)),9,3,1,1)

On sheet 2 these formula go in B1, C1, D1 and E1 from top to bottom, the match formula returns the row of the serial number then the offset function returns the required data (it requires the format to be standard throughout)
Justin

Converting text to rows in a huge dataset

One Answer

Add your own answers!

Ask a Question