Match multiple criteria between 2 workbooks and return "text" if there is no match

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
555
Office Version
2019
Platform
Windows
I will be building a simple Macro in an excel workbook that the user can open and run.
The user will have to have the first Workbook open when they run the macro so I can reference it as active workbook (for this example I will call it Workbook1, this workbook name can change) .
What the Macro will do is put a formula in column E workbook1.
It is the formula (in yellow area) that I need assistance with...for now.

1594229659025.png


What I need the formula to do is if A2,B2,C2 and D2 (on Active workbook1) all match I:J (on the last sheet of Workbook:Database),
leave cell blank. If they do not match, the result would be the word "flag"

A4&"|"&B4&"|"&C4&"|"&D4 MATCH I:I&"|"&J:J&"|"&K:K&"|"&L:L

I am also curious if the formula can reference the last sheet in a workbook without knowing the sheet name?
as the formula in Workbook 1 will be referencing the last created sheet in workbook: Database
*if not I will need to consider a work around for this, a code that determines the last sheet name and references it to a cell and then have that formula reference that cell??


Additional Question Relating to the above:

Note as per the picture:
The first row the status changed
the second row, nothing changed
The third row, the quantity changed
The 4th row, was new data entirely

Now if the first question wasn’t complicated enough: could we go one step further and add IF conditions?
Instead of just flagging the changes with the word "flag" could it be modified to display text as follows?:
If change is between column A and I: "Date Change"
If change is between column B and J: "Qty Change"
If change is between column C and K: "Status Change"
If change is between column D and L: "Part # Change"
If change is multiple: "multiple"

If this is too complicated then just the match between 2 workbooks with the word "flag" is fine and I will re-post the 2nd question.

Thank you to anyone who can help. I know how to do an index match with multiple criteria however this is a bit more involved.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,125
Office Version
2007
Platform
Windows
With formula, I can't find a way to know what the last sheet of a book is.
If you are going to use a macro to get the sheet, it would be convenient to create the macro to do the whole process. If you agree, I help you with the whole macro.
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
555
Office Version
2019
Platform
Windows
That sounds like a good plan. I will re-post regarding that later. For now I still need a formula so lets just say we know the sheet names and then worry about obtaining the sheet names via macro later.

Is this possible?

Thank you again for all of your help!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,125
Office Version
2007
Platform
Windows
Is an array formula:

varios 08jul2020.xlsm
ABCDE
1DateQtyStatusPartFlag
203-jun5ClosedAE2256Flag
304-jun3ClosedTX952 
405-jun2OpenYU834Flag
506-jun2OpenCF5542Flag
Hoja3
Cell Formulas
RangeFormula
E2:E5E2=IF(ISNUMBER(MATCH(A2&"|"&B2&"|"&C2&"|"&D2,[Database.xlsx]Sheet1!$A$2:$A$20&"|"&[Database.xlsx]Sheet1!$B$2:$B$20&"|"&[Database.xlsx]Sheet1!$C$2:$C$20&"|"&[Database.xlsx]Sheet1!$D$2:$D$20,0)),"","Flag")
Press CTRL+SHIFT+ENTER to enter array formulas.


I do not recommend comparing against the entire column: (MATCH I: I & "|" & J: J & "|" & K: K & "|" & L: L)
because it is an array formula and that would cause a bad performance in your sheet.
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
555
Office Version
2019
Platform
Windows
This works perfectly.

Is there a way to add text depending where there is no match? (My second question above)

Note as per the picture:
The first row the status changed
the second row, nothing changed
The third row, the quantity changed
The 4th row, was new data entirely

Instead of just flagging the changes with the word "flag" could it be modified to display text as follows?:
If change is between column A: "Date Change"
If change is between column B: "Qty Change"
If change is between column C: "Status Change"
If change is between column D: "Part # Change"
If change is multiple: "multiple/new"

Thank you for all of your help
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
555
Office Version
2019
Platform
Windows
Several times.

Currently I am trying your formula out on the real workbook but it is giving me an error:

1594316261252.png


The modified formula for the real workbooks is:

=IF(ISNUMBER(MATCH(A6&"|"&F6&"|"&G6&"|"&J6,[COMPANY NAME - Master forecast - INC.xlsx]JULY 2020!$F$10:$F$5000&"|"&[COMPANY NAME - Master forecast - INC.xlsx]JULY 2020!$H$10:$H$5000&"|"&[COMPANY NAME - Master forecast - INC.xlsx]JULY 2020!$A$10:$A$5000&"|"&[COMPANY NAME - Master forecast - INC.xlsx]JULY 2020!$G$10:$G$5000,0)),"","Flag")

Any idea why I am getting this error?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,125
Office Version
2007
Platform
Windows
last created sheet in workbook: Database
Your book was called "database" with no spaces in between.
Your book is now called "COMPANY NAME - Master forecast - INC" with blanks in between.
Then you have to add Apostrophe:

=IF(ISNUMBER(MATCH(A6&"|"&F6&"|"&G6&"|"&J6,
'[COMPANY NAME - Master forecast - INC.xlsx]JULY 2020'!$F$10:$F$5000&"|"&
'[COMPANY NAME - Master forecast - INC.xlsx]JULY 2020'!$H$10:$H$5000&"|"&
'[COMPANY NAME - Master forecast - INC.xlsx]JULY 2020'!$A$10:$A$5000&"|"&
'[COMPANY NAME - Master forecast - INC.xlsx]JULY 2020'!$G$10:$G$5000,0)),"","Flag")
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
555
Office Version
2019
Platform
Windows
Thank you very much!

Is there a way to modify the formula further regarding what I mentioned above? (Flagging the type of change)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,125
Office Version
2007
Platform
Windows
Is there a way to modify the formula further regarding what I mentioned above? (Flagging the type of change)
I still do not know, you did not answer my questions from post #6.
Could you give an example with more data.

The part number is a unique id, that is, we can use it as a key.
In the "database" book does it appear once or several times?
 

Watch MrExcel Video

Forum statistics

Threads
1,102,120
Messages
5,484,852
Members
407,470
Latest member
SusanJohnst

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top