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

#### TheUnconquered

##### New Member
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

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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?

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.

Ok, you can do that with conditional formatting like
+Fluff 1.xlsm
AB
1Acct # Set 1Acct # Set 2
240004000
340204065
440304066
540404100
640504130
740604300
840654600
940664610
1040705000
1140805005
1240905006
1341005010
1441105025
1541205030
1641305040
1741405050
1841505060
1941605090
2050005100
2150255110
2245505120
2346005130
2450605150
2543005510
2651505516
2750055517
2850065520
2950305521
30
31
32
33
Data
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B100Expression=AND(B2<>"",COUNTIFS(\$A\$2:\$A\$100,B2)=0)textNO

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.

Ok, how about
+Fluff 1.xlsm
ABC
1Acct # Set 1Acct # Set 2
240004000TRUE
340204065TRUE
440304066TRUE
540404100TRUE
640504130TRUE
740604300TRUE
840654600TRUE
940664610FALSE
1040705000TRUE
1140805005TRUE
1240905006TRUE
1341005010FALSE
1441105025TRUE
1541205030TRUE
1641305040FALSE
1741405050FALSE
1841505060TRUE
1941605090FALSE
2050005100FALSE
2150255110FALSE
2245505120FALSE
2346005130FALSE
2450605150TRUE
2543005510FALSE
2651505516FALSE
2750055517FALSE
2850065520FALSE
2950305521FALSE
30
Data
Cell Formulas
RangeFormula
C2:C29C2=COUNTIFS(A:A,B2)>0
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B100Expression=AND(B2<>"",COUNTIFS(\$A\$2:\$A\$100,B2)=0)textNO

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!

You're welcome & thanks for the feedback.

Threads
1,207,438
Messages
6,078,554
Members
446,348
Latest member
ncm3208

### 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

### 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