How to check the acct #'s for two sets of data are the same

[TheUnconquered]

I'm trying to figure out a formula or way to check if there are new account numbers in "Set 2" that are not in "Set 1".

I've pasted a subsection of the data below. I've just copy and pasted this over. Sorry if this is not the best way to provide an example.

Acct # Set 1	Acct # Set 2
4000​	4000
4020​	4065
4030​	4066
4040​	4100
4050​	4130
4060​	4300
4065​	4600
4066​	4610
4070​	5000
4080​	5005
4090​	5006
4100​	5010
4110​	5025
4120​	5030
4130​	5040
4140​	5050
4150​	5060
4160​	5090
5000​	5100
5025​	5110
4550​	5120
4600​	5130
5060​	5150
4300​	5510
5150​	5516
5005​	5517
5006​	5520
5030​	5521

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also do you want to list the accounts, or just highlight them?

 

[TheUnconquered]

Thanks for that. Just updated my profile. It's Office 2016. I'm on a Windows PC.

I think highlighting them is fine. I need to get the Set one accounts to include all account numbers so I'll need to go back manually and insert rows where set 1 is missing acct numbers and add them, but I think I need to do that manually so highlighting them is fine. There's shouldn't be many to add.

 

[Fluff]

Ok, you can do that with conditional formatting like

+Fluff 1.xlsm
	A	B
1	Acct # Set 1	Acct # Set 2
2	4000	4000
3	4020	4065
4	4030	4066
5	4040	4100
6	4050	4130
7	4060	4300
8	4065	4600
9	4066	4610
10	4070	5000
11	4080	5005
12	4090	5006
13	4100	5010
14	4110	5025
15	4120	5030
16	4130	5040
17	4140	5050
18	4150	5060
19	4160	5090
20	5000	5100
21	5025	5110
22	4550	5120
23	4600	5130
24	5060	5150
25	4300	5510
26	5150	5516
27	5005	5517
28	5006	5520
29	5030	5521
30
31
32
33
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B100	Expression	=AND(B2<>"",COUNTIFS($A$2:$A$100,B2)=0)	text	NO

 

[TheUnconquered]

I think this works fine. My only issue with the conditional formatting is that when I expanded this to the whole dataset nothing showed up as highlighted. This either means that there are no new accounts or I could've screwed up the conditional formatting formula. In this case, I think it's because all the accounts match, but your comment about pulling the account numbers out might be a better way to do it. Or is there's a way to do a third column that would have a formula that said if account 4610 (for example) doesn't match anything from all of column A then designate with a "FALSE" and if it matches with an account in column A then show "TRUE" or something like that and then carry that formula through the rest of the rows? Then I at least have a visual that says everything checks out.

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C
1	Acct # Set 1	Acct # Set 2
2	4000	4000	TRUE
3	4020	4065	TRUE
4	4030	4066	TRUE
5	4040	4100	TRUE
6	4050	4130	TRUE
7	4060	4300	TRUE
8	4065	4600	TRUE
9	4066	4610	FALSE
10	4070	5000	TRUE
11	4080	5005	TRUE
12	4090	5006	TRUE
13	4100	5010	FALSE
14	4110	5025	TRUE
15	4120	5030	TRUE
16	4130	5040	FALSE
17	4140	5050	FALSE
18	4150	5060	TRUE
19	4160	5090	FALSE
20	5000	5100	FALSE
21	5025	5110	FALSE
22	4550	5120	FALSE
23	4600	5130	FALSE
24	5060	5150	TRUE
25	4300	5510	FALSE
26	5150	5516	FALSE
27	5005	5517	FALSE
28	5006	5520	FALSE
29	5030	5521	FALSE
30
Data

Cell Formulas
Range	Formula
C2:C29	C2	=COUNTIFS(A:A,B2)>0

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B100	Expression	=AND(B2<>"",COUNTIFS($A$2:$A$100,B2)=0)	text	NO

 

[TheUnconquered]

So it's the same formula you used for the conditional formatting, but with a >0 shows it as True. Makes sense; works perfectly. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.