+ Reply to Thread
Results 1 to 10 of 10

Multi partial match conditions across two sheets

  1. #1
    Registered User
    Join Date
    03-27-2020
    Location
    Tampa, FL
    MS-Off Ver
    O365
    Posts
    5

    Exclamation Multi partial match conditions across two sheets

    I have an index match that works; however, i cannot seem to figure out how to translate it to VBA code. Essentially, I have criteria from 4 columns that I am trying to match to 4 columns on a second spreadsheet. The second spreadsheet is a report that has character limits so most of the criteria is not an exact match.

    Here's my index - match array that works

    =IF(ISERROR(INDEX('2019'!C:C,MATCH("*"&Q5&"*"&"*"&C5&"*"&"*"&D5&"*"&"*"&M5&"*",'2019'!O:O&'2019'!N:N&'2019'!M:M&'2019'!P:P,0),1)),"No Match","Match")

    Essentially I am looking to see if there is a 'partial' match on all 4 criteria; then place "Match" in column "S" of that row otherwise "No Match" in that column

    Q5, C5, D5 and M5 are in Sheet 1; Sheet 2 = 2019, Columns O, N, M, P (respectively)

    I am looking to see if there is:
    A partial match of the Cell Q5 in sheet 2019 column O:O
    AND
    partial match of Cell C5 in sheet 2019 column N:N
    AND
    partial match of the Cell D5 in sheet 2019 column M:M
    AND
    partial match of the Cell M5 in sheet 2019 column P:P

    I will check each row in sheet 1 to see if there is a match and copy that match to a separate page or just put "Match" in column "S" of Sheet 1....

    Been trying to figure this out for too long .... Can anyone help?

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    1,605

    Re: Multi partial match conditions across two sheets

    Possibly...
    Please Login or Register  to view this content.
    ____________________________________________________________
    If I've been helpful, let me know. If I haven't, let me know that too.

  3. #3
    Registered User
    Join Date
    03-27-2020
    Location
    Tampa, FL
    MS-Off Ver
    O365
    Posts
    5

    Re: Multi partial match conditions across two sheets

    Thank you! You're the BEST!
    Last edited by JoZ_813; 04-10-2020 at 04:01 PM.

  4. #4
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    1,605

    Re: Multi partial match conditions across two sheets

    Glad I helped!

  5. #5
    Registered User
    Join Date
    03-27-2020
    Location
    Tampa, FL
    MS-Off Ver
    O365
    Posts
    5

    Re: Multi partial match conditions across two sheets

    Actually ... it didn't work Can you take another look? I've attached a workbook with just the columns populated.

    When I changed the return criteria if matched to return the Journal Line (Column C of PSoft) rather than "Match" it did not return the correct value.

    The rows with blue highlight should return "No Match" while the others should match

    I appreciate the 2nd look.
    Attached Files Attached Files

  6. #6
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    1,605

    Re: Multi partial match conditions across two sheets

    Question: Do all the matches need to be in the same row on the 2nd worksheet?

  7. #7
    Registered User
    Join Date
    03-27-2020
    Location
    Tampa, FL
    MS-Off Ver
    O365
    Posts
    5

    Re: Multi partial match conditions across two sheets

    Yes. If match found for Q on line 10, then the other conditions must be met on that same line to be considered a match and the value of column C is what would be populated in the criteria ws (ConcurExpense).
    Also, There should not be more than one journal ID for the match due to the $ (column N). The journal ID (Column C - PSoft) is the same when the $ are the same. Also, maybe this will help; matches will only be found where the source (Psoft - Column F) is "CON"

    Essentially, I am trying to see if all the ConcurExpense transactions have been posted in Peoplesoft (PSoft) and pulling back the journal ID to use in the next sequence ... which is to see if it has been has been billed to a third party (different worksheet).

    Thank you again for your help with this.

  8. #8
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    1,605

    Re: Multi partial match conditions across two sheets

    Try this different approach...
    Last edited by dangelor; 04-11-2020 at 11:35 PM. Reason: small change

  9. #9
    Registered User
    Join Date
    03-27-2020
    Location
    Tampa, FL
    MS-Off Ver
    O365
    Posts
    5

    Re: Multi partial match conditions across two sheets

    Tried it and made a few changes. It seems to work. Thanks!

    Sub ConcurExpensePSMATCH()
    Dim wC As Worksheet, wS As Worksheet
    Dim rC As Range, rS As Range
    Dim vC As Variant, vS As Variant
    Dim i As Long, j As Long

    Set wC = Worksheets("ConcurExpense")
    Set rC = wC.Cells(1, 1).CurrentRegion
    Set rC = rC.Offset(4).Resize(rC.Rows.Count + 4)
    vC = rC

    Set wS = Worksheets("PSoft")
    Set rS = wS.Cells(1, 1).CurrentRegion
    Set rS = rS.Offset(2).Resize(rS.Rows.Count + 2)
    vS = rS
    Application.ScreenUpdating = False

    For i = LBound(vC) To UBound(vC)
    For j = LBound(vS) To UBound(vS)
    If InStr(1, vS(j, 15), vC(i, 17), 1) And _
    InStr(1, vS(j, 14), vC(i, 18), 1) And _
    InStr(1, vS(j, 13), vC(i, 4), 1) And _
    InStr(1, vS(j, 16), vC(i, 13), 1) Then
    wC.Cells(i + 4, 19) = wS.Cells(j + 2, 3)
    End If
    Next j
    Next i

  10. #10
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    1,605

    Re: Multi partial match conditions across two sheets

    Thanks for letting me know. We both learned something.

+ 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. [SOLVED] Partial Match V-Lookup with two different sheets
    By Sweden12 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-04-2019, 01:39 PM
  2. Change Cell value format in all sheets based on multi conditions
    By shiva_raj in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-03-2019, 07:13 AM
  3. Google Sheets: Formula I can use with multi conditions
    By CullyM in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 07-12-2018, 01:06 PM
  4. Search variable number of sheets to find partial match on name
    By carolmwondering in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-19-2014, 01:44 PM
  5. Need a formula that will partial match data on multiple sheets
    By dmitch20 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-16-2013, 08:44 PM
  6. [SOLVED] Comparing list from two different sheets and returning partial row if No Match
    By loser420 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-23-2013, 01:56 PM
  7. Lookup multiple partial match conditions and return values
    By darklans in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 04-18-2012, 03:26 AM

Tags for this Thread

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