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

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
678
Office Version
  1. 2019
Platform
  1. 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.
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
678
Office Version
  1. 2019
Platform
  1. Windows
I still do not know, you did not answer my questions from post #6.
Could you give an example with more data.

I answered on post 7. Sorry it was brief.

Yes the part number appears multiple times. The data sources are currently a mess and this may be changing.
My boss has already changed his mind multiple times in regards to this but what I am trying to accomplish pretty much remains the same...

Basically I am currently researching to see what is possible. (My bosses want to change the main Database spreadsheet (Master Forecaster) and we have a meeting about this in a few hours).

Currently another employee takes 2 spreadsheets, one called Demand Detail (which is emailed to her from another source) and the main spreadsheet (Master Forecaster) and compares them for changes in 5 columns. She then makes a note in the notes section on Master Forecaster of the change.
This takes days to accomplish as there is that much data. I wanted to start off small by flagging which items have changed but I would like to take it one step farther and have the formula "Note" what changed, since there can only be a Maximum of 5 changes: Date, Qty, Status, PO#, or New/Multiple changes

The only item that will never change is the part number, however it will repeat multiple times. The PO number will also remain consistent but there is a 15% chance that it may change, if this does happen however the part number will remain the same.

My end goal would be to make a macro that enters in 2 formulas, 1 on each book referencing each other:

the first formula would be on the Demand Detail Workbook in column AB or AC to indicate what the change was (see column AC for this example)
The 2nd formula would be in column U on the Master Forecaster. I would reverse the formula you gave me previously, but instead of returning the word "flag" it would return the result from the first formula above on the Demand Workbook

Here is the dropbox link of the edited copies of the 2 workbooks (I removed the sensitive information).


If the link makes anyone uncomfortable see below for pictures.

I hope I am making sense. Thank you for all of your help!!

Note: I do not have to worry about the changing Workbook/sheet names anymore.
 

Attachments

  • Pic-1.jpg
    Pic-1.jpg
    195 KB · Views: 1
  • Pic-2.jpg
    Pic-2.jpg
    244.3 KB · Views: 1

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Where I work, search for this type must include the PO line number

1594396897186.png


1594397403029.png


I recommend you use these 3 data as a key: PO, PO Line and Part No.

1. If the key exists:
1.1 Verify the Date, Status and QTY.
1.2 Then we could already know if any of these data is different (Date, Status, Qty).

2. If the key does not exist, then highlight as Flag (red).
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
678
Office Version
  1. 2019
Platform
  1. Windows
Is there a way instead of highlighting in red that we can have the result be text that states Date, Status or Qty?
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
678
Office Version
  1. 2019
Platform
  1. Windows
I am also worried about the very small percentage of the chance that the PO# may change. More often than not both part and PO are consistent though.
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
678
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Date, Status, Qty, and Multiple/New.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Is there a way instead of highlighting in red that we can have the result be text that states Date, Status or Qty?
I mean it is a serious error that a PO is not found.
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
678
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

It wouldn't be that the PO is not found, it may be that the PO# has changed. It is a small percentage that it happens but if it does it is fine if it marks it as "Multiple/New"
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
678
Office Version
  1. 2019
Platform
  1. Windows
Like what you said previously, maybe it can be written like if the Part/PO# match check if there is a change in Date, Status, Qty, if there is a change, return what the change was as text: Date, Status, or Qty.
If there are multiple changes or if there was no Part/PO# match, mark as "Multiple/New".

Is there a way to do this?

We can single out the small % of a PO# change manually, as it would flag it as Multiple/New anyway.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
maybe it can be written like if the Part/PO# match check if
I suggest you also consider the PO line number.
A PO can have multiple PO line numbers, so you should only compare to one, otherwise you would have false results.
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
678
Office Version
  1. 2019
Platform
  1. Windows
I suppose I am not understanding. Couldnt we take the PO and Part # combination and match using that?

Also would the original formula that you gave me still work to flag changes without false results?

=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")
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,455
Messages
5,596,231
Members
414,047
Latest member
debbos

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top