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

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
681
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
681
Office Version
  1. 2019
Platform
  1. Windows
Sorry, just trying to wrap my head around this.

It does look like there are too many inconsistencies. PO# and Part# does repeat and match so the only way to accurately flag a change that occurred to an entire row (all areas Date, Status, Qty, New) would be to Match like you had above A2&"|"&B2&"|"&C2&"|"&D2 with ranges $A$2:$A$20&"|"$B$2:$B$20&"|"$C$2:$C$20&"|" $D$2:$D$20 and return the word "flag".

I don't see a way to accurately note what change occurred.

If you have any further suggestions please feel free to post them, otherwise I think I will just use the original formula you gave me for now.

I would like to know if there was a way to single out the new entries however. Those would be the items that do not have a single match I believe...
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
I suppose I am not understanding. Couldnt we take the PO and Part # combination and match using that?
Yes you can use PO and Part.
But this could happen.

I'm going to try to explain it.
Assuming the following example. I want to find PO 123, Part al56 (left side), on the right side.

varios 10jul2020.xlsm
ABCDEFGHIJKLM
1DEMANDMASTER
2POPartDateStQtyPOPartDateStQtyPO Line
3123al5610-julFirm1123al5611-julFirm110
4123al5610-julFore120
5123al5610-julFirm230
6123al5612-julFore140
7123al5613-julFore350
8
Hoja14


On the right side there are 5 records with PO 123 and Part al56.
The 5 have a data that does not match (in yellow).
Which of the 5 answers would you put in your result?
_______________________________________________________________________________________________________________________________

If you add the PO Line to the key, then you will have only one result, you can see, the only mistake would be the quantity.

varios 10jul2020.xlsm
ABCDEFGHIJKLM
1DEMANDMASTER
2POPartDateStQtyPO LinePOPartDateStQtyPO Line
3123al5610-julFirm130123al5611-julFirm110
4123al5610-julFore120
5123al5610-julFirm230
6123al5612-julFore140
7123al5613-julFore350
8
Hoja14 (2)


_______________________________________________________________________________________________________________________________
I hope my proposal is clearer now.
_______________________________________________________________________________________________________________________________
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
681
Office Version
  1. 2019
Platform
  1. Windows
Actually PO line can be repeating as well, so yes this would not work. I am fine with the formula that marks any changes with "flag", however can you think of a way to at least mark the ones that are new?
New items will be new part numbers or PO#'s that are not on the Master Forecast list at all.
If a separate formula would work to identify the new items just looking at PO# and/or Part # I think I should be good.

Thank you for all of your help.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Actually PO line can be repeating as well

Each system has its characteristics. In my system the PO-Line is not repeated in the same order. I find it incredible that in your system, in the same order the PO-Line are repeated.

Continuing with my example, then you want a result like this:
date: 11jul, st: Fore, qty: 2, date: 12jul, st: Fore, date: 13jul, St: Fore, Qty:3
According to my example, these are all the differences that exist with the PO 123 part al56.

I don't have the excel version to show various results in one formula.
For those results, you could create a macro.
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
681
Office Version
  1. 2019
Platform
  1. Windows
Each system has its characteristics. In my system the PO-Line is not repeated in the same order. I find it incredible that in your system, in the same order the PO-Line are repeated.

Continuing with my example, then you want a result like this:
date: 11jul, st: Fore, qty: 2, date: 12jul, st: Fore, date: 13jul, St: Fore, Qty:3
According to my example, these are all the differences that exist with the PO 123 part al56.

I don't have the excel version to show various results in one formula.
For those results, you could create a macro.


Yes this system is messy and very inconsistent (trying to make order from chaos ;))

Due to the amount of repeating information it looks like there will still be a certain amount of manual work...for now.

I am thinking for now the formula to flag it when there is a change will do, however could I also have assistance with another separate formula that if the PO# and/or Part # doesn't exist in the Master Forecast spreadsheet to flag as "new"?

This will be a workaround for now, or at least draw our attention to changes/possible new entries.

Thank you DanteAmor for all of your help and explanations.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,241
Messages
5,600,501
Members
414,383
Latest member
konmtu

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