+ Reply to Thread
Results 1 to 15 of 15

Lookup a value based two criteria, but the source is in the same column

  1. #1
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Lookup a value based two criteria, but the source is in the same column

    Update 15.02.19 12:18: Re-written the whole thread for simplification.

    I apologise in advance. This issue can seem complicated. However, I'll try to simplify this as much as possible, without leaving out important criteria.

    I work in the quality engineering business, and I'll use the relevant terms here:
    Supplier number This is a number that identifies a specific supplier.
    Part number This is a number that identifies a specific part.
    Report number This is a number that identifies a specific report. (A report always contains a supplier, part, and creation date).

    What I want, is to lookup a report creation date and report number based on my criteria: Supplier and Part.
    Look at the image below. It's divided into three sections: The left is my source data, the middle is what criteria I have, and what I'm missing, and the right is what the solution should look like.
    IQg13Tw.png

    Column B contains the common denominator for the grouping logic. One report number contains 1 supplier and 1 or more parts.
    What I want to lookup the report date and number using only the SUPPLIER & PART criteria, as you can see in the middle/right section.

    Here's the Excel sheet with this sample for experimentation.
    https://drive.google.com/file/d/1q2Q...ew?usp=sharing


    Additional info:
    This problem would be much simpler if the SOURCE SHEET listed the information such that each row has a unique report value, with a supplier and part value. Such as:
    Row 1: Report creation date | Report number | Supplier number | Part number
    Row 2: 01.01.2018 11529 3026 805425
    Row 3: etcetc...

    Then I would just use a INDEX(MATCH(1;(supplier array = supplier)*(part array = part)).
    However, here the supplier and part information is scattered on the same column over multiple rows.
    Attached Files Attached Files
    Last edited by 27POP27; 05-15-2019 at 06:20 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,526

    Re: LOOKUP a value based on two criteria on different rows but same column

    In L2 =INDEX($A$2:$A$25,MATCH(K2,$D$2:$D$25,)) and copy down

    In M2 =INDEX($B$2:$B$25,MATCH(K2,$D$2:$D$25,)) and copy down.

  3. #3
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: LOOKUP a value based on two criteria on different rows but same column

    Quote Originally Posted by alansidman View Post
    In L2 =INDEX($A$2:$A$25,MATCH(K2,$D$2:$D$25,)) and copy down

    In M2 =INDEX($B$2:$B$25,MATCH(K2,$D$2:$D$25,)) and copy down.
    Hi Alan,
    Thank you for your answer.

    I should have further explained:
    One part number can have multiple suppliers, and multiple report numbers. Thus it's important that the part and supplier number matches for each J and K row.
    Also, the same supplier and part can also have multiple reports, and I want to list the date of the most recent report. Thus supplier+part has to match the same report number and date.

    This would complicate this matter further I'm afraid

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,526

    Re: LOOKUP a value based on two criteria on different rows but same column

    The issue I have with your request is that the Supplier and Part Number are in the same field. For a solution, we (you) will need to separate these into separate fields for the same record. What distinguishes one from the other. ie. Supplier is always five digits in length, Part number has a hyphen. Is this true. If this is the case I can split the field out, but how will one know that a particular supplier should be assigned to a particular part. I do not see any correlation. Can you advise a relationship that we can depend on?

    Alan

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,526

    Re: LOOKUP a value based on two criteria on different rows but same column

    I have figured out a way to align the data, if my understanding of your sheet is on target. The supplier info applies to the records above it. If this is true, then using Power Query I was able to transform your data into a table that then allowed me to pivot your data and present a table as shown below.

    Data Range
    A
    B
    C
    D
    E
    F
    1
    NCR Creation Date
    Part
    30419
    3343
    3209
    3072
    2
    8/24/2016
    1.120-00016
    11736
    3
    8/24/2016
    110508-13551
    11735
    4
    8/24/2016
    130902-02255
    5
    8/24/2016
    310401-01314
    11736
    6
    8/24/2016
    400602-00695
    11735
    7
    8/25/2016
    502371
    11739
    8
    8/25/2016
    522698
    11738
    9
    8/25/2016
    522700
    11738
    10
    8/26/2016
    527860
    11741
    11
    8/26/2016
    528645
    11741
    12
    8/26/2016
    528646
    11741
    13
    8/26/2016
    528700
    11741
    14
    8/26/2016
    528979
    11741
    15
    8/26/2016
    528980
    11741
    16
    8/26/2016
    528981
    11741
    17
    8/26/2016
    528982
    11741

    I did this with the following Mcode in Power Query

    this renamed your table as Table1

    Please Login or Register  to view this content.
    File is attached for your review
    Attached Files Attached Files

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,526

    Re: LOOKUP a value based on two criteria on different rows but same column

    An alternative presentation

    v F G H
    2 NCR Creation Date Supplier Part
    3 8/24/2016
    4 30419
    5 1.120-00016
    6 110508-13551
    7 130902-02255
    8 310401-01314
    9 400602-00695
    10 8/25/2016
    11 3209
    12 502371
    13 3343
    14 522698
    15 522700
    16 8/26/2016
    17 3072
    18 527860
    19 528645
    20 528646
    21 528700
    22 528979
    23 528980
    24 528981
    25 528982

  7. #7
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: LOOKUP a value based on two criteria on different rows but same column

    Quote Originally Posted by alansidman View Post
    An alternative presentation

    v F G H
    2 NCR Creation Date Supplier Part
    3 8/24/2016
    4 30419
    5 1.120-00016
    6 110508-13551
    7 130902-02255
    8 310401-01314
    9 400602-00695
    10 8/25/2016
    11 3209
    12 502371
    13 3343
    14 522698
    15 522700
    16 8/26/2016
    17 3072
    18 527860
    19 528645
    20 528646
    21 528700
    22 528979
    23 528980
    24 528981
    25 528982
    Hi again,

    A supplier number can be 4 or 5 digits, and the part number can be 8, 15 or even text. Thus, the most reliable way to identify a part from supplier if by it's neighboring column always to the left.
    And yes, the all part numbers above a supplier number are co-related. However, the best defining characteristic of all values that are in a shared group is by the report number.

    Your "Alternate representation" seems to follow the logic of what I refer to. However, I'd advise using the "Report no." instead of the NCR creation date as a group identifier. Since multiple reports can have the same date.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,526

    Re: LOOKUP a value based on two criteria on different rows but same column

    Here is the file once again with a new Pivot Table. You can manipulate it as needed. I have changed it as you have requested. I have added a slicer also that allows you to filter Suppliers.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: LOOKUP a value based on two criteria on different rows but same column

    Quote Originally Posted by alansidman View Post
    Here is the file once again with a new Pivot Table. You can manipulate it as needed. I have changed it as you have requested. I have added a slicer also that allows you to filter Suppliers.
    Hi. I'm so sorry but there seems to be a misunderstanding.
    I'm not after a pivot table. I'm after being able to lookup only the report date and report number based on the supplier+part (+ latest report) criteria.
    (In this workbook the NCR number = Non-conformance report number).

    I've attached the actual workbook I'll be using. I've deleted/hidden all unnecessary info.
    What I want here, is to simply fill out the U and V columns in the "Inspection List" sheet.

    Does this make more sence?
    Attached Files Attached Files

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,526

    Re: LOOKUP a value based on two criteria on different rows but same column

    Yes. I understand. I am able to get the file in an order that allows for the lookups by having the Supplier on the same line as the product but I am unable to perform a look up as you requested. My VBA crashes my system. Hopefully, some one else will be able to make it work.

  11. #11
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Lookup a value based two criteria, but the source is in the same column

    Will the two associated rows on the NCR Check worksheet always be next to each other?
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

  12. #12
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,526

    Re: Lookup a value based two criteria, but the source is in the same column

    Using Power Query again, I can get the Vendor and the Part Number and the Report Number all on the same line. Here is the MCode and the file. However, when I apply Index/Match to the sheet, I get an N/A. Hopefully someone here can fix that

    Mcode
    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,728

    Re: Lookup a value based two criteria, but the source is in the same column

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Read this to understand why we (and other sites like us) consider this to be important.

    Because this thread already has several responses I will post the link but please remember this for future threads.

    https://www.mrexcel.com/forum/excel-...-same-row.html
    Jeff
    | | || || || || | |:| | || ||
    Read the rules
    Use code tags to [code]enclose your code![/code]

  14. #14
    Registered User
    Join Date
    11-12-2018
    Location
    Norway
    MS-Off Ver
    Excel 2016
    Posts
    36

    Re: Lookup a value based two criteria, but the source is in the same column

    Quote Originally Posted by 6StringJazzer View Post
    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Read this to understand why we (and other sites like us) consider this to be important.

    Because this thread already has several responses I will post the link but please remember this for future threads.

    https://www.mrexcel.com/forum/excel-...-same-row.html
    My apologies but this thread seemed to die off (combined with that my original version of this thread was up to misunderstandings), thus I created one there. I'll refer cross-threads at once in the future.
    (Also, for some reason, I'm not able to edit this original post. Now when I click edit the thread text edit page is all empty. If I'd save this I'd delete the text/pics/attachments in the post.)

    I've received a solution on the other cross-thread, see this: (By Marcelo Branco): https://www.mrexcel.com/forum/excel-...-same-row.html

    If a admin is able, please edit my original post to "SOLVED" with a link to this solution.

  15. #15
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,728

    Re: Lookup a value based two criteria, but the source is in the same column

    Quote Originally Posted by 27POP27 View Post
    for some reason, I'm not able to edit this original post. Now when I click edit the thread text edit page is all empty.
    This is a known bug in our system that happens randomly to some posts. When this happens a Moderator cannot edit your post either.

    You do not have to edit your post to mark it solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..." (I see your post is already SOLVED but this is for next time.)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Lookup based on multiple criteria and return value in column
    By Ariff_Chowdhury in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-01-2019, 10:08 PM
  2. VBA to Lookup all Rows based on Column Header
    By matt85webb in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-14-2018, 07:23 PM
  3. How to count rows where one column meets criteria based on another column
    By gavsenior in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-08-2018, 07:30 AM
  4. [SOLVED] Lookup and return Heading of column based on criteria (sample data attached)
    By kai. in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-01-2018, 04:46 AM
  5. Replies: 1
    Last Post: 04-30-2018, 12:23 PM
  6. Replies: 2
    Last Post: 10-10-2015, 02:22 AM
  7. Replies: 1
    Last Post: 11-20-2010, 05:38 AM

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