Comparing two tables for changes

[Dnm88]

Hi all!

I have two tables that I need to compare. The second table is a monthly update to the first one. It consists of various columns, some of them stay the same (like trade names, ingredients), some of them differ (price changes, manufacturer changes) from month to month. On top of that, each entry is assigned one of three possible categories (let's call them A, B or C) and some entries move from one category to another when it gets updated every month.

The lists are technically ordered alphabetically, but the order changes from month to month as new entries are added and some old ones are removed.

It would be ideal if there was a way of:

- listing all the entries that changed from one category to another (from B to C for example) whilst making sure that all the other details of the entry match perfectly (like the trade name and ingredients)

- listing all the new entries

- listing all the removed entries


I tried playing with =CONCATENATE to extract the information from relevant cells and then used =VLOOKUP to compare the entries between those tables, plus another column that returns TRUE or FALSE based on whether the entries match. Unfortunately, this doesn't seem to work 100%, feels a bit clunky and doesn't achieve all the points I listed above.

Is there another way of doing it without having to resort to addons and plugins?

 

[Toadstool]

Hi DNM88,

This may go some way to satisfy your request:

DNM88.xlsx
	A	B	C	D	E	F
1	Product	Category	Trade Name	Ingredients	Price	Manufacturer
2	DD098	C	Duck Down	Feathers	$28.00	Quack Inc
3	FIG003	B	Figgy Pudding	Honey, figs	$12.00	Baker Bros
4	TR999	C	Four Candles	Fork handles	$43.00	Ronnieton
5	GIN001	A	Mothers Ruin	Gin	$23.00	Stiller
6	TH773	A	Toad in the Hole	Sausage, batter	$12.00	Toadstool Ltd
7
OldMonth

DNM88.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Product	Category	Trade Name	Ingredients	Price	Manufacturer		Changed Category	Trade Name	Ingredients	Price	Manufacturer
2	DD098	C	Duck Down	Feathers	$28.00	Quack Inc
3	GIN001	B	Mothers Ruin	Gin	$55.00	Alcool Inc		GIN001			Changed	Changed
4	BK042	A	Bees Knees	Honey	$233.00	BuzzCorp
5	FIG003	A	Figgy Pudding	Honey, nuts, figs	$12.00	Baker Bros		FIG003		Changed
6	CM032	B	Cats Meow	Purrs, licks	$22.00	Kitty Inc
7
NewMonth

Cell Formulas
Range	Formula
H2:H7	H2	=IF(IFERROR(INDEX(OldMonth!$B$2:$B$9999,MATCH(A2,OldMonth!$A$2:$A$9999,0)),B2)<>B2,NewMonth!A2,"")
I2:L7	I2	=IF($H2="","",IF(C2<>INDEX(OldMonth!C$2:C$9999,MATCH($A2,OldMonth!$A$2:$A$9999,0)),"Changed",""))

DNM88.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	New Products								Removed Products
2	New Row	Product	Category	Trade Name	Ingredients	Price	Manufacturer		Removed Row	Product	Category	Trade Name	Ingredients	Price	Manufacturer
3	3	BK042	A	Bees Knees	Honey	233	BuzzCorp		3	TR999	C	Four Candles	Fork handles	43	Ronnieton
4	5	CM032	B	Cats Meow	Purrs, licks	22	Kitty Inc		5	TH773	A	Toad in the Hole	Sausage, batter	12	Toadstool Ltd
5
Results

Cell Formulas
Range	Formula
A3:A5	A3	=IFERROR(AGGREGATE(15,6,ROW(NewMonth!$A$2:$A$9999)-ROW(NewMonth!$A$1)/((ISNA(MATCH(NewMonth!$A$2:$A$9999,OldMonth!$A$2:$A$9999,0)))*(NewMonth!$A$2:$A$9999<>"")),ROW()-ROW($A$2)),"")
B3:G5	B3	=IF($A3="","",INDEX(NewMonth!A:A,$A3+1))
I3:I5	I3	=IFERROR(AGGREGATE(15,6,ROW(OldMonth!$A$2:$A$9999)-ROW(OldMonth!$A$1)/((ISNA(MATCH(OldMonth!$A$2:$A$9999,NewMonth!$A$2:$A$9999,0)))*(OldMonth!$A$2:$A$9999<>"")),ROW()-ROW($A$2)),"")
J3:O5	J3	=IF($I3="","",INDEX(OldMonth!A:A,$I3+1))

 

[Dnm88]

Hi Toadstool!

That’s great, thank you for your help! Sorry for a delay, I was away from the office so only got a chance to try it today.

I was playing with the first for formula you provided so that if it detects that the product changed its category it then returns the old value so that I can compare it easily with the new value. So ideally I’d have one column that tells me if the category has changed and then another column to tell me what it changed from, as I am ultimately after isolating the ones that changed from only specific categories to a different set of specific categories, so that I can update those categories on another system (hope that makes sense?). Unfortunately, the results of my changed formula don’t seem to be what I was after, although I have a feeling I might’ve mixed something up. I tried figuring out how the formula works based on the example you provided, but not 100% I got it all right.

This is what I tried:

=IF(IFERROR(INDEX(OldMonth!$L$2:$L$9999,MATCH(B2,OldMonth!$B$2:$B$9999,0)),"")<>B2,OldMonth!L2,"")

Column B contains product name, Column L contains category.

Really grateful fro your help!

 

[Toadstool]

Hi Dnm88,

My test data just put the headings in the sequence you mentioned them. If you can use XL2BB or just an image of your headings then I can give a more appropriate example.

Here's the NewMonth tab with modified comparison columns (the other 2 tabs have not changed at all). If a Category has changed then it reports the old value for Category. If the Category has changed it then checks the other columns for changes and reports the old value.
NOTE: It only checks for other changes if the Category has changed, e.g. DD098 Duck Down had Ingredients of "Feathers" and now has Ingredients of "Some Feathers" but is not reported because the Category has not changed).

DNM88-2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Product	Category	Trade Name	Ingredients	Price	Manufacturer		Changed Category	Old Category	Old Trade Name	Old Ingredients	Old Price	Old Manufacturer
2	DD098	C	Duck Down	Some Feathers	$28.00	Quack Inc
3	GIN001	B	Mothers Ruin	Gin	$55.00	Alcool Inc		GIN001	A			23	Stiller
4	BK042	A	Bees Knees	Honey	$233.00	BuzzCorp
5	FIG003	A	Figgy Pudding	Honey, nuts, figs	$12.00	Baker Bros		FIG003	B		Honey, figs
6	CM032	B	Cats Meow	Purrs, licks	$22.00	Kitty Inc
7
NewMonth

Cell Formulas
Range	Formula
J2:M6	J2	=IF($H2="","",IF(C2<>INDEX(OldMonth!C$2:C$9999,MATCH($A2,OldMonth!$A$2:$A$9999,0)),INDEX(OldMonth!C$2:C$9999,MATCH($A2,OldMonth!$A$2:$A$9999,0)),""))
J7:M7	J7	=IF($H7="","",IF(C7<>INDEX(OldMonth!C$2:C$9999,MATCH($A7,OldMonth!$A$2:$A$9999,0)),"Changed",""))
I2:I6	I2	=IF($H2="","",INDEX(OldMonth!B$2:B$9999,MATCH($A2,OldMonth!$A$2:$A$9999,0)))
H2:H7	H2	=IF(IFERROR(INDEX(OldMonth!$B$2:$B$9999,MATCH(A2,OldMonth!$A$2:$A$9999,0)),B2)<>B2,NewMonth!A2,"")

 

[Dnm88]

Hi Toadstool!

I've included an example of my table layout below:

Sl #	Trade Name	Form	Pack Size	Pharmacy Price	Public Price	Active Ingredient	Strength	Company	Source	Agent	Category
1	(GI) 0.9% W/V SODIUM CHLORIDE ***	Isotonic Solution for Irrigation	1000ml LDPE Bottle	8,74	10,50	Sodium Chloride	0.9% w/v	ABCT LLC	ABC	DRUG STORE	CD
2	(GI) 0.9% W/V SODIUM CHLORIDE ***	Isotonic Solution for Irrigation	500ml LDPE Bottle	4,37	5,00	Sodium Chloride	0.9% w/v	ADT LLC	CDA	XDA DRUG STORE	P
3	0.2% CIPROFLOXACIN in 0.9% W/V SODIUM CHLORIDE INJECTION USP	Infusion/Solution for	100ml Plastic Bag	16,88	20,50	Ciprofloxacin (as lactate)	2mg/ml	XAT PHARMA	QADS	PHARMA STORES	POM

I had some luck with listing new and removed products (for some reason still had some false positive results), I couldn't get this formula to work:

H2	=IF(IFERROR(INDEX(OldMonth!$B$2:$B$9999,MATCH(A2,OldMonth!$A$2:$A$9999,0)),B2)<>B2,NewMonth!A2,"")

Keep getting an error message both before and after replacing the column letters

Based on the layout of my tables my goal is to:

- ideally be able to list all the products (based on the trade name column) that have changed categories and be able to see both the old and new one for comparison. One potential issue is that the Trade Name column can have minor differences (like added asterisks for example) from month to month, even though it's exactly the same product. Alternatively, if I could make the formula search by the Active Ingredient column values instead and provide me with a list of ingredients that changed category (with matching trade names next to it) - this would be fine as well.

- be able to list all the additions/removals, which your formula helped with and I'm guessing the false positive results came from those minor differences in the Trade Name column

Hope this makes it a bit more clear!

 

[Toadstool]

Hi Dnm88,

I've changed my examples to use your column headings.

If a Category changes and the only difference between Trade Names is the number of "*" then I've used SUBSTITUTE to remove all asterisks for the comparison, so it should only report if something else changed.

Here are my 3 tabs again in the new formats:

DNM88-3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Sl #	Trade Name	Form	Pack Size	Pharmacy Price	Public Price	Active Ingredient	Strength	Company	Source	Agent	Category
2	1	(GI) 0.9% W/V SODIUM CHLORIDE ***	Isotonic Solution for Irrigation	1000ml LDPE Bottle	8.74	10.5	Sodium Chloride	0.9% w/v	ABCT LLC	ABC	DRUG STORE	CD
3	2	(GI) 0.9% W/V SODIUM CHLORIDE ***	Isotonic Solution for Irrigation	500ml LDPE Bottle	4.37	5	Sodium Chloride	0.9% w/v	ADT LLC	CDA	XDA DRUG STORE	P
4	3	0.2% CIPROFLOXACIN in 0.9% W/V SODIUM CHLORIDE INJECTION USP	Infusion/Solution for	100ml Plastic Bag	16.88	20.5	Ciprofloxacin (as lactate)	2mg/ml	XAT PHARMA	QADS	PHARMA STORES	POM
5	6	(GI) 1.2% Drugidon	Tablet	x 22	$44.00	$88.00	Drugidone	88%	Downy	ADD	Careful	CD
6	5	Xanax	Capsule	x 50	$55.00	$99.00	Sleepioxide	2mg/ml	Downy	ADD	Careful	CD
7
OldMonth

DNM88-3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Sl #	Trade Name	Form	Pack Size	Pharmacy Price	Public Price	Active Ingredient	Strength	Company	Source	Agent	Category		Changed Category	Old Category	Old Trade Name	Old Form	Old Pack Size	Old Pharmacy Price	Old Public Price	Old Active Ingredient	Old Strength	Old Company	Old Source	Old Agent
2	1	(GI) 0.9% W/V SODIUM CHLORIDE ********	Isotonic Solution for Irrigation	1000ml LDPE Bottle	8.74	10.50	Sodium Chloride	0.9% w/v	ABCT LLC	ABC	DRUG STORE	XX		1	CD
3	2	(GI) 0.9% W/V SODIUM CHLORIDE ***	Isotonic Solution for Irrigation	500ml LDPE Bottle	4.37	5.00	Sodium Chloride	0.9% w/v	ADT LLC	CDA	XDA DRUG STORE	P
4	6	(GI) 1.2% Drugidon	Tablet	x 22	44.00	88.00	Drugidone	0.88	Downy	ADD	Careful	CD
5	4	1% Olanzapine	Tablet	x 50	22.00	44.00	Sulphobeneazo Acid	0.12	Hallucino Corp	ADD	Careful	X
6	5	Xanax Extra	Capsule	x 5	55.00	255.00	Sleepioxide	2mg/ml	Downy	ADD	Careful	Y		5	CD	Xanax		x 50		99.00
7
NewMonth

Cell Formulas
Range	Formula
N2:N7	N2	=IF(IFERROR(INDEX(OldMonth!$L$1:$L$10001,MATCH(A2,OldMonth!$A$1:$A$10001,0)),L2)<>L2,NewMonth!A2,"")
O2:O7	O2	=IF($N2="","",INDEX(OldMonth!L$1:L$10001,MATCH($A2,OldMonth!$A$1:$A$10001,0)))
P2:P7	P2	=IF($N2="","",IF(SUBSTITUTE(B2,"*","")<>SUBSTITUTE(INDEX(OldMonth!B$1:B$10001,MATCH($A2,OldMonth!$A$1:$A$10001,0)),"*",""),INDEX(OldMonth!B$1:B$10001,MATCH($A2,OldMonth!$A$1:$A$10001,0)),""))
Q2:Y7	Q2	=IF($N2="","",IF(C2<>INDEX(OldMonth!C$1:C$10001,MATCH($A2,OldMonth!$A$1:$A$10001,0)),INDEX(OldMonth!C$1:C$10001,MATCH($A2,OldMonth!$A$1:$A$10001,0)),""))

DNM88-3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	New Products														Removed Products
2	New Row	Sl #	Trade Name	Form	Pack Size	Pharmacy Price	Public Price	Active Ingredient	Strength	Company	Source	Agent	Category		Removed Row	Sl #	Trade Name	Form	Pack Size	Pharmacy Price	Public Price	Active Ingredient	Strength	Company	Source	Agent	Category
3	4	4	1% Olanzapine	Tablet	x 50	22	44	Sulphobeneazo Acid	0.12	Hallucino Corp	ADD	Careful	X		3	3	0.2% CIPROFLOXACIN in 0.9% W/V SODIUM CHLORIDE INJECTION USP	Infusion/Solution for	100ml Plastic Bag	16.88	20.5	Ciprofloxacin (as lactate)	2mg/ml	XAT PHARMA	QADS	PHARMA STORES	POM
4
5
Results

Cell Formulas
Range	Formula
A3:A5	A3	=IFERROR(AGGREGATE(15,6,ROW(NewMonth!$A$2:$A$9999)-ROW(NewMonth!$A$1)/((ISNA(MATCH(NewMonth!$A$2:$A$9999,OldMonth!$A$2:$A$9999,0)))*(NewMonth!$A$2:$A$9999<>"")),ROW()-ROW($A$2)),"")
B3:M5	B3	=IF($A3="","",INDEX(NewMonth!A:A,$A3+1))
O3:O5	O3	=IFERROR(AGGREGATE(15,6,ROW(OldMonth!$A$2:$A$9999)-ROW(OldMonth!$A$1)/((ISNA(MATCH(OldMonth!$A$2:$A$9999,NewMonth!$A$2:$A$9999,0)))*(OldMonth!$A$2:$A$9999<>"")),ROW()-ROW($O$2)),"")
P3:AA5	P3	=IF($O3="","",INDEX(OldMonth!A:A,$O3+1))

 

[Dnm88]

This worked perfectly, thank you for your help!

 

[Toadstool]

You're welcome!