Search common values with different physical locations

**gtudor** · 02-14-2012, 05:56 AM

We are trying to work out a formula for our purchase order system.

Currently we are receiving part ship PO's that are then placed in different locations, when searching for the locations it is only detailing the latest shipment. I would like the formula to search all of the fields (in sheet2) to display all locations to each unique PO numner.

I have included an example to help.

Thanks in advance.

**Pete_UK** · 02-14-2012, 06:31 AM

I have amended your file to give you up to 5 locations, by adding this formula to Q6 of Sheet2:

=IF(F6="","-",F6&"_"&COUNTIF(F$6:F6,F6))

and then copying it down beyond your data (the hyphens show this). In Sheet1 I have inserted 4 new columns and put this formula in K6:

=IF($C6="","",IF(ISNA(MATCH($C6&"_"&COLUMN(A$1),Sheet2!$Q:$Q,0)),"",INDEX(Sheet2!$M:$M,MATCH($C6&"_"&COLUMN(A$1),Sheet2!$Q:$Q,0))))

This is copied into L6:O6, and then copied down (I've only done 5 rows). I've added two more POs for testing.

Hope this helps.

Pete