+ Reply to Thread
Results 1 to 3 of 3

Search common values with different physical locations

  1. #1
    Forum Contributor
    Join Date
    02-13-2012
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    103

    Search common values with different physical locations

    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.
    Attached Files Attached Files
    Last edited by gtudor; 02-14-2012 at 05:59 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,137

    Re: Search common values with different physical locations

    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
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    02-13-2012
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    103

    Re: Search common values with different physical locations

    WOW, that is a long formula. I will have a go at reproducing it myself. Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1