# Eliminating Duplicates and Correlating them to another Value

#### KDR11

##### New Member
I'm trying to create a table that will provide values based on duplicates being counted as 1.

For the totals I've achieved this by using the following formula and pressing ctrl+shift+enter:

=SUM(IF(FREQUENCY(IF(LEN(Table!A:A)>0,MATCH(Table!A:A,Table!A:A,0),""), IF(LEN(Table!A:A)>0,MATCH(Table!D:D,Table!A:A,0),""))>0,1))

Where Table is the worksheet that contains the data, and row A is the company name. The above formula allows me to have blank cells and will also allow the amount of data to vary.

I'm having a problem, however, assigning a different argument.

For instance if:

 A B Red Y Red Y Blue Y Red Y Green Y Red Y Green Y Blue Y Yellow Z Green Y Purple Z Orange Z Black X White X Black X Purple Z

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

My total will correctly arrive at "8" because that's the total number of, in this case, colors involved.

Now I want to know how many colors there are that are associated with "X", "Y", or "Z"

X = 2
Y = 3
Z = 3

How can I accomplish this while still being allowed to have an infinite area within the column that does not count blanks and allows for text?

Thank you!

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Replies
1
Views
321
Replies
5
Views
288
Replies
0
Views
605
Replies
7
Views
535
Replies
3
Views
97

1,219,905
Messages
6,150,901
Members
450,991
Latest member
ExcelDoer

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

### Which adblocker are you using?

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

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