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

[willow1985]

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.

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]

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).

Dropbox - File Deleted - Simplify your life

www.dropbox.com

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.

 

[DanteAmor]

Where I work, search for this type must include the PO line number

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]

Is there a way instead of highlighting in red that we can have the result be text that states Date, Status or Qty?

 

[willow1985]

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]

Date, Status, Qty, and Multiple/New.

 

[DanteAmor]

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]

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]

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]

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]

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")