Counting unique values per row.

[Johndeer]

Hi, hopefully I can explain my situation, and what I need well enough.... Here goes...

So, I have somewhere around 50 or 60 unique numbers on sheet 1, and about 500 entries containing some of these numbers on sheet 2. The problem is that on sheet 2 some rows countain duplicate values that I need counted from sheet 1. I can not remove these duplicate values, because it is important that the data is not deleted.

So what I need is to be able to search sheet 2 for a unique value, and determine how many rows each of these values appears in. Not a total of how many times this value has appeared, but in how many rows it appears.

I know this has to be possible to do, but how?

 

[Marcelo Branco]

Hi,

Welcome to the forum

A question: in which columns are the numbers in Sheet2? A, B and C, for example?

and also: which rows?

M.

 

[Johndeer]

Thank you.

The numbers in sheet 2 are in columns D - O, and rows 2 - 478.

 

[Johndeer]

Still no luck. Anyone know the formula I should be using in this situation?

 

[Marcelo Branco]

Hi,

Say that Sheet1 A1 houses a number of interest (an unique number).

Maybe this in B1 to get the rows-count
=SUMPRODUCT(--(COUNTIF(OFFSET($D$2:$O$478,ROW($D$2:$O$478)-ROW($D$2),0,1,12),A1)>0))

If your unique numbers in Sheet 1 are in column A, beginning in A1, you can copy down this formula

HTH

M.

 

[Johndeer]

Thank you much.

 

[Marcelo Branco]

Thank you much.

You are welcome. Glad to help.

M.