Better way than Sort by Colors?

dmoore86088

New Member
Joined
Jun 27, 2014
Messages
12
Hello team,
I work with some extremely large excel files. One of the things that I have to do is to compare thousands of account numbers from different sources to find all that are common. Right now, I use Conditional Formatting -> Highlight Cells Rules -> Duplicate values. Once all matching account numbers are highlighted, I sort by color, so that they are all in one place. This takes an extremely long time!!

Is there a better way of identifying the same account numbers, and then collecting them all in one place? Is there a 'count by color' formula?

Many thanks!!
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,846
Office Version
  1. 2019
Platform
  1. Windows
What is your criteria for matching account numbers? Perhaps if you showed us some examples of what you are doing, then someone here can assist you with an easier means to meet your end. Be sure to list all your different criteria for matching to make this most efficient.
 

dmoore86088

New Member
Joined
Jun 27, 2014
Messages
12
Hello Alan,
Thank you for your comment. Here's an example:

OriginalAll Accounts: Vlookup
48,280 46,789
05628000004030562810052204
05628000342090562800000403
05628000410080562800001003
05628000507070562800001419
05628001001030562800001613
05628001186050562800001712
05628001214100562800001811

<colgroup><col><col></colgroup><tbody>
</tbody>

These are account numbers from two different sources. My task is to identify the numbers which do not appear in one column when compared to the 2nd column. Currently, I try to use 'Conditional Formatting -> Highlight duplicate values'. Then I sort by color. It works, but as you can see, there are 10's of thousands of numbers, and excel could take many hours to get the job done.

I'm simply looking for a better way. I'm beginning to believe that it might not be practical in excel.....your thoughts?

Thanks again!
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,768
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

There's a workbook at https://app.box.com/shared/elrnbidnr7 that would convert that to this:

Row\Col
A​
B​
1​
Original​
Lookup​
2​
05628000004030562800000403
3​
0562800001003
4​
0562800001419
5​
0562800001613
6​
0562800001712
7​
0562800001811
8​
0562800034209
9​
0562800041008
10​
0562800050707
11​
0562800100103
12​
0562800118605
13​
0562800121410
14​
0562810052204
 

dmoore86088

New Member
Joined
Jun 27, 2014
Messages
12
Thanks Tyron! It works great. Weird reaction though, it doesn't seem to recognize colors established by Conditional Formatting. But it's very helpful in other drills I'm doing. Thanks again!
 

dmoore86088

New Member
Joined
Jun 27, 2014
Messages
12

ADVERTISEMENT

Interesting.....would you know of any instructions? I'm not following the description in the spreadsheet. Thanks again!
 

Tyron

Active Member
Joined
Dec 5, 2012
Messages
258
Hey dmoore86066,

Instructions, Not really to many instructions except what seems to be at the top of the post on the site indicating about the color count.

The way I use it is this.

=COLORFUNCTION(N232,A1:GY144,FALSE)
N232 is a helper cell which I have changed to the color that I want it to check for. In this case it is Orange.
A1:GY144 is the range I want it to look for the above color
FALSE: Indicates that I want it to count the colored cells. If I use TRUE instead then it counts the values within those colored cells and SUMS it.

later

Ty
 

Tyron

Active Member
Joined
Dec 5, 2012
Messages
258
Hey dmoore86088,

No problem. One other thing. There could be other ways of doing this as well if you were able to append a special marker of some type to the account numbers. If you are able to exchange the number for a name or word that would also make things easier as well. As I am not familiar with the totality of your worksheet I dare not suggest things that may mess something up.

But, from time to time, I try to look at the data from a different perspective if I can. Remember computers are able to do anything unless they flat out tell you they aren't willing. lol. Something I learned at an old job.

later

Ty
 

Watch MrExcel Video

Forum statistics

Threads
1,108,493
Messages
5,523,267
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top