Better way than Sort by Colors?

[dmoore86088]

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

 

[Tyron]

Hey dmoore80688,

Sounds to me like a UDF that I found on the web. Some cut and pasting necessary and requires the use of Macro enabled workbook.

It has had some mixed results for some, but worked for me.

Check this web page out and see if it helps:

How to count and sum cells based on background color in Excel?

later

Ty

 

[alansidman]

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]

Hello Alan,
Thank you for your comment. Here's an example:

Original	All Accounts: Vlookup
48,280	46,789
0562800000403	0562810052204
0562800034209	0562800000403
0562800041008	0562800001003
0562800050707	0562800001419
0562800100103	0562800001613
0562800118605	0562800001712
0562800121410	0562800001811

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

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​	0562800000403	0562800000403
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]

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]

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

 

[Tyron]

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

 

[dmoore86088]

Thank you Ty!

 

[Tyron]

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