Find unique field in one column and extract data in previous columns

Hi
I have invoices extracted on an excel sheet. There are around 4000 invoices. The invoice number (unique field) is in column F and the data i want to extract is in column D, starting 5 rows below the invoice nummber. The data to be extracted varies in quantum i.e. it spreads from to 2 to 10 rows below the invoice number. Please tell me a solution of finding the data through the invoice number, and displaying the data in column D.

Thanks.

Are you able to provide an example or fabricate one for us?

I understand invoices can contain a lot of sensitive information, but you greatly increase your chances of receiving assistance if you're able to provide a work sample or at least make one up.

For Forum.xlsx

Oh, should have done it earlier.

I have attached the sample file. The data to find is highlighted in red, and the data to be extracted is highlighted in blue.

Thanks.

You highlighted a number of rows on your example.

Are you still just targetting the dimensions in Column D for each invoice?

No the whole blue highlighted area (columns B to I). However, i think the formula would work column wise and that should be perfect. For instance, i will have to input a formula in one cell to extract column B data, then in the next cell to extract column C data and so on.

Almost done. This is difficult, pretty much at the limit of my abilities.

I'm using MATCH to find what row the Invoice is on, using a dynamic range for the first relevant column, and search that column for the first occurence of "Product", after the first match of that invoice.

On the bright side, I totally got to learn how to use INDIRECT which is something I've been meaning to educate myself on.

So far I have:

L2 = invoice to match, M1 = Product

=MATCH(L2,INDIRECT(CHAR(COLUMN(B1)+64)&(ROW(B1)+MATCH(M1,$I$1:$I$50000,0))&":"&CHAR(COLUMN(B1)+64)&ROW($B$50000)),0)

This finds the first occurence of the word Product after a successful match to the invoice.

Now I need a 5 minute break. Then I'll write a dynamic range formula for the Index and the rest will be copy and paste.


Finished product will look something like:

=INDEX(INDIRECT(CHAR(COLUMN(B1)+64)&(ROW(B1)+MATCH(M1,$I$1:$I$50000,0))&":"&CHAR(COLUMN(B1)+64)&ROW($B$50000)),MATCH(L2,INDIRECT(CHAR(COLUMN(B1)+64)&(ROW(B1)+MATCH(M1,$I$1:$I$50000,0))&":"&CHAR(COLUMN(B1)+64)&ROW($B$50000)),0)+ROW(A1))

I put the solution and result in another sheet"solution" see if this is what you needed

Just about done.

See the attached. This formula is in 03, and then copied and pasted across 8 columns and down 10 rows.

=IF(INDEX(INDIRECT("B"&(1+MATCH($N$2,$I$1:$I$50000,0))&":"&CHAR(COLUMN(D1)+64)&ROW($B$4000)),MATCH(O$2,INDIRECT(CHAR(COLUMN(B1)+64)&(1+MATCH($N$2,$I$1:$I$50000,0))&":"&CHAR(COLUMN(B1)+64)&ROW($B$50000)),0)+ROW(A1),COLUMN(A1))=0,"",INDEX(INDIRECT("B"&(1+MATCH($N$2,$I$1:$I$50000,0))&":"&CHAR(COLUMN(D1)+64)&ROW($B$4000)),MATCH(O$2,INDIRECT(CHAR(COLUMN(B1)+64)&(1+MATCH($N$2,$I$1:$I$50000,0))&":"&CHAR(COLUMN(B1)+64)&ROW($B$50000)),0)+ROW(A1),COLUMN(A1)))


I just need to work in another dynamic range for the end range, so that you don't get garbage after any instance of Total Quantity in Column D.

INDIRECT is hard.xlsx

I am stumped. I was unable to replace the end of the first range's number value with:

(MATCH("Total Quantity",INDIRECT(CHAR(COLUMN($D1)+64)&(1+MATCH($N$2,$I$1:$I$50000,0))&":"&CHAR(COLUMN($D1)+64)&ROW($B$4000)),0))

althought I got it to work fine in my own example.

I have no idea how you did what you did, but it's awesome.

Thank you everyone! u guys are amazing in here! freaking mind blowing..
yeah daffodil11, amazing job haseeb ... ur solution worked perfectly! nice..