How to

[al5led]

Hi

i have monthly exercises in my work to find the missing data in two sheets (sheet A and sheet B) and categorize them (for example Hotel, Restaurant, Offices)
i want to create an Excel file to find the missing data between two sheets and the result should be showing the missing in each sheet and categorize in the third sheet named (Missing Data Dashboard) as an attached example link file. example of Missing Data Dashboard
so the missing data dashboard will provide the missing data of each sheet and categorize them comparing with the other sheet (compare the data on sheet1 with sheet2, and, compare the data on sheet2 with sheet1).

please find the solution for me as I get tired of monthly long steps with more than 1000 cells to compare it.

thank you.

 

[AhoyNC]

Here is one way to compare lists.

Book2
	A	B	C	D	E
1	List 1	List 2		Missing from List 1	Missing from List 2
2	Name	Name		Andrew	Mike
3	John	Andrew		Judy	Ralph
4	Mike	Ann		Kathleen
5	Julie	John		Kevin
6	Tony	Judy		Lisa
7	Ralph	Julie		Shelley
8	Ann	Kathleen		Thomas
9	Sam	Kevin		Trish
10	Steve	Lisa
11		Sam
12		Shelley
13		Steve
14		Thomas
15		Tony
16		Trish
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=FILTER($B$3:$B$16,ISNA(MATCH($B$3:$B$16,$A$3:$A$10,0)),"No Missing Data")
E2:E3	E2	=FILTER($A$3:$A$10,ISNA(MATCH($A$3:$A$10,$B$3:$B$16,0)),"No Missing Data")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:B16	Expression	=ISNA(MATCH(B3,$A$3:$A$10,0))	text	NO
A3:A10	Expression	=ISNA(MATCH(A3,$B$3:$B$16,0))	text	NO