How to compare 2 tables (master and data) and show the extra cells in the data on the master

[Phyles]

I've setup my Project staff tracking sheet against their time sheets - but I cant see who is new on the data table

1. I have 2 lists of names on 2 sheets (master and data) in 1 xls file.
2. I currently use =XLOOKUP($A2,'RPV Data'!$B:$B,'RPV Data'!$A:$A, "...") on the master sheet to find what names match - master table to data table.
3. I was hoping to do a nested xlookup (or another formula) to also show what extra names are in data table but not in master table. ie jon Doe only in data only.
4. Then I can add Jon Doe if apporved to master table to be shown in the above #2 point.
5. So far trying multi fomulars I can only get matched etc but not the cell contents ie Jon Doe.

 

[JamesCanale]

This formula will show all names that aren't in the other table. So it won't tell you which table it's in, but maybe your business rules mean that extra names can only ever be in the data table.

MrExcelPlayground16.xlsx
	A	B	C
1	John		John
2	Fred		Fred
3	Harry		Harry
4	Harry		Sam
5	Sam		Sally
6	John		Mary
7	Fred		Mervin
8	Sally		George
9	Mary
Sheet26

with the following outputting a list of the two names that are only in one column.

=UNIQUE(VSTACK(UNIQUE(A1:A9),UNIQUE(C1:C8)),,TRUE)

 

[Phyles]

This formula will show all names that aren't in the other table. So it won't tell you which table it's in, but maybe your business rules mean that extra names can only ever be in the data table.

MrExcelPlayground16.xlsx
	A	B	C
1	John		John
2	Fred		Fred
3	Harry		Harry
4	Harry		Sam
5	Sam		Sally
6	John		Mary
7	Fred		Mervin
8	Sally		George
9	Mary
Sheet26

with the following outputting a list of the two names that are only in one column.

=UNIQUE(VSTACK(UNIQUE(A1:A9),UNIQUE(C1:C8)),,TRUE)

Hi, Thats the closest I've been - see screen dump - It picks up the additional in data table at the top of the list, then it shows whats not on master table using SN as my trigger - this works if there is no way to only show extra in data table - Cheers.

 

[AhoyNC]

Maybe something like this.

Data Sheet

Book3
	A	B	C
1	Data		Not in Master
2	Fred		Fred
3	Mike		June
4	June		Dawn
5	Mary
6	Judy
7	Dawn
Data

Cell Formulas
Range	Formula
C2:C4	C2	=FILTER(A2:A7,ISNA(MATCH(A2:A7,Master!A2:A5,0)))
Dynamic array formulas.

Master Sheet

Book3
	A
1	Master
2	Sam
3	Mike
4	Mary
5	Judy
Master

 

[Phyles]

Maybe something like this.

Data Sheet

Book3
	A	B	C
1	Data		Not in Master
2	Fred		Fred
3	Mike		June
4	June		Dawn
5	Mary
6	Judy
7	Dawn
Data

Cell Formulas
Range	Formula
C2:C4	C2	=FILTER(A2:A7,ISNA(MATCH(A2:A7,Master!A2:A5,0)))
Dynamic array formulas.

Master Sheet

Book3
	A
1	Master
2	Sam
3	Mike
4	Mary
5	Judy
Master

Hi, Tks for the quick reply - Looks like a good answer - I tried this and rewrote to suit my tables - but I get #CALC! - =FILTER('RPV Data'!B:B,ISNA(MATCH('RPV Data'!B:B,Staff!A:A,0))) - I also did try with a cell range but same error.

 

[Phyles]

Hi, Tks for the quick reply - Looks like a good answer - I tried this and rewrote to suit my tables - but I get #CALC! - =FILTER('RPV Data'!B:B,ISNA(MATCH('RPV Data'!B:B,Staff!A:A,0))) - I also did try with a cell range but same error.

Hi Update to the above =filter... - it does work, I jus added a new cell - but I need to add text if possible "No new" to stop to #CLAC! message

 

[Phyles]

Hi Update to the above =filter... - it does work, I jus added a new cell - but I need to add text if possible "No new" to stop to #CLAC! message

=FILTER('RPV Data'!B:B,ISNA(MATCH('RPV Data'!B:B,Staff!A:A,0)),"No New") - Works perfect - Tks for your help.

 

[AhoyNC]

You're welcome.
It's best not to use whole column references it can slow down and/or cause errors. If your data would never go below say row 100000 then use A2:A100000 etc,