Conditional formatting: Comparing collumns

[ROLLTIDEJSSIE]

Good Morning,

I was just introduced to conditional formatting last week and here is what I am working on....I need to compare two columns that have account numbers and find out which accounts are in the same column. I know that there is a formula however I do not know what that formula is and how to apply it. Could someone assist me with that? I am using excel 2007...

Thank you for your help!


HAPPY MONDAY!

Jessica

[FDibbins]

Hi and welcome to the forum

Im a little confused here...you have 2 columns, but you want to find duplicates in the SAME column?

If you want to find duplicates in the same column, high-light the range, and in CF, new rule, select you can use "format only duplicate or unique values"

[ROLLTIDEJSSIE]

Here is what i did....i took two reports and just brought over the account numbers from each report....column "a" shows what we have and column "b" shows what our partner has. What i am wanting to do is to compare the two columns and find out what accounts are listed in both...

I was told there was a way to do so....

[FDibbins]

ok, that makes more sense

you could co this with =countif()>1 but it would be easier for me to suggest the formula if I could see a sample of what you are working with?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[ROLLTIDEJSSIE]

B00000112615 b00000114110
b00000112656 b00000114122
b00000112806 b00000114210
b00000112880 b00000114250
b00000112912 b00000114332
b00000112923 b00000114377
b00000113059 b00000114395
b00000113079 b00000114515
b00000113184 b00000114526
b00000113228 b00000114602
b00000113270 b00000114669
b00000113293 b00000114717
b00000113351 b00000115003
b00000113377 b00000115020
b00000113390 b00000115061
b00000113420 b00000115067
b00000113447 b00000115260
b00000113458 b00000115338
b00000113582 b00000115356
b00000113640 b00000115525
b00000113672 b00000115568
b00000113685 b00000115582
b00000113717 b00000115586
b00000113763 b00000115602
b00000113827 b00000115749
b00000113833 b00000115828
b00000113867 b00000115876
b00000113872 b00000115953
b00000113932 b00000115990
b00000113945 b00000116006
b00000113960 b00000116126
b00000113963 b00000116330
b00000114056 b00000116384
b00000114060 b00000116446
b00000114063 b00000116462
b00000114110 b00000116491
b00000114117 b00000116504
b00000114122 b00000116548
b00000114174 b00000116559
b00000114210 b00000116669
b00000114216 b00000116748
b00000114250 b00000116770
b00000114294 b00000116801
b00000114332 b00000116898
b00000114335 b00000116996
b00000114377 b00000117037
b00000114395 b00000117080
b00000114430 b00000117237
b00000114515 b00000117296
b00000114526 b00000117351
b00000114602 b00000117386
b00000114615 b00000117527
b00000114669 b00000117648
b00000114680 b00000117671
b00000114717 b00000117693
b00000114730 b00000117725
b00000114746 b00000117776
b00000114929 b00000117828
b00000115003 b00000117864
b00000115020 b00000117950
b00000115035 b00000118002
b00000115061 b00000118015
b00000115067 b00000118020
b00000115085 b00000118100
b00000115260 b00000118158
b00000115324 b00000118280
b00000115338 b00000118458
b00000115356 b00000118516
b00000115525 b00000118545
b00000115564 b00000118605
b00000115568 b00000118646
b00000115582 b00000118685
b00000115586 b00000118713
b00000115602 b00000118881
b00000115650 b00000119035
b00000115749 b00000119076
b00000115799 b00000119116
b00000115828 b00000119136
b00000115868 b00000119303
b00000115873 b00000119340
b00000115876 b00000119347
b00000115953 b00000119402
b00000115990 b00000119567
b00000116006 b00000119636
b00000116126 b00000119776
b00000116238 b00000119865
b00000116330 b00000119887
b00000116384 b00000119920
b00000116390 b00000119946
b00000116446 b00000119957
b00000116462 b00000119980
b00000116491 b00000119998
b00000116504 b00000120197
b00000116529 b00000120465
b00000116548 b00000120496
b00000116559 b00000120624
b00000116669 b00000120667
b00000116695 b00000120731
b00000116728 b00000120779
b00000116748 b00000120783
b00000116752 b00000120812
b00000116770 b00000120881
b00000116801 b00000120902
b00000116897 b00000120912
b00000116898 b00000120987
b00000116996 b00000121035
b00000117033 b00000121038
b00000117037 b00000121177
b00000117080 b00000121182
b00000117237 b00000121203
b00000117296 b00000121278
b00000117351 b00000121294
b00000117386 b00000121417
b00000117399 b00000121452
b00000117527 b00000121472
b00000117648 b00000121530
b00000117671 b00000121549
b00000117693 b00000121578
b00000117725 b00000121711
b00000117776 b00000121729
b00000117803 b00000121731
b00000117828 b00000121736
b00000117864 b00000121770
b00000117865 b00000121780
b00000117950 b00000121813
b00000117962 b00000121821
b00000118002 b00000122071
b00000118015 b00000122169
b00000118020 b00000122412
b00000118072 b00000122426
b00000118100 b00000122434
b00000118158 b00000122466
b00000118236 b00000122533
b00000118271 b00000122614
b00000118280 b00000122615
b00000118335 b00000122727
b00000118458 b00000122883
b00000118516 b00000122927
b00000118545 b00000122931
b00000118605 b00000122976
b00000118646 b00000123090
b00000118685 b00000123095
b00000118713 b00000123321
b00000118792 b00000123383
b00000118798 b00000123385
b00000118881 b00000123396
b00000119018 b00000123413
b00000119035 b00000123439
b00000119076 b00000123470
b00000119114 b00000123555



the accounts may start with different alpha's

[FDibbins]

It would have been easier iof you has uploaded the workbook (keep that in mind for the future), but be that as it may...

1. highlight the range you want to apply the conditional formatting to in column A
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =COUNTIF($B$1:$B$150,A1)>0 format fill as requited
5. repeat the above for column B, change the formula to =COUNTIF($A$1:$A$150,B1)>0

Hope that helps?

[ROLLTIDEJSSIE]

I tried to upload it however it said it was to large because there are over 80,000 account to compare just on one column...i hope this makes my life easier!!!

[naveen.t]

Hi Rolltidejisse,

I have attached the sample below. please take a look at it and let me know if you are looking for the same.

Regards,
Naveen

[ROLLTIDEJSSIE]

Well basically if the account number is in both columns a & b i would like for them to be highlighted and strike through them...so that i can remove the account from the list...

[FDibbins]

did you try the fomulas i suggested?

[naveen.t]

Hi Jessica,

1.select both colomns
2.click on conditional formatting then manage rules
3. click on new rule then format only duplicate or unique values
4.click on format, check mark the strikethrough option under the font tab
5.you can also add color under the fill tab by selecting any color and click on ok

Hope this will help you.