On Jan 15, 3:14 pm, A Newton wrote:
On Jan 15, 1:54 pm, feltra wrote:



Hi,

If you havent found the formula to populate cells in col Q based for
unique values in Col C, here's the formula that will do exactly as
you want. You can then simply count the 1s in col Q to get the unique
values in Col C:

In first row of data (say row 1) key in this formula in Col Q:
=COUNTIF($C$1:$C1,C1)

Highlight all the cells in Col Q from row1 to end of data row (say row
450 in your example). Then do a Fill Down (CTRL-D). The important
thing is to give the absolute and relative references exactly as shown
(ie. keying in the $ symbol or leaving it out).

The number of unique values in Col C can now be found by
=COUNTIF($Q$1:$Q$450,1)
in another results cell in the sheet.

This will work if you do it by hand... For putting in a macro (ie.
programming) you need a little more work, but can be done...

Hope this helps.

Best Regards,
-feltra

Thanks! This is getting REALLY, REALLY CLOSE to what I need. It is not
exactly what I need only b/c because my examples were incomplete. My
fault entirely.

In many cases, the numbers in column C might start with a letter (v).
For example:

C1 v123456 1
C2 v123456 2
C3 v123456 3
C4 v123456 4
C10 789012 1
C45 348975 1
C150 789012 2
C334 789012 3
C53 v569802 1
C450 v569802 2

Any ideas?

Thanks! Great suggestion. That worked like a charm. All items in
column Q are now appropriately numbered.

One follow-up --

On another worksheet -- worksheet 2 -- I now want to tally all the
items that have a value of 1 in column Q and that also have a value of
"foo" in column A and "bar" in column M on worksheet 1. I thought I
could find all the items that I was looking for with this formula:

=SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M
$450="bar") * ('worksheet'!$Q$2:$Q$450="1"))

but it is returning "0" whereas I know from looking at worksheet 1
that I should actually be getting a value of "3".

This:

=SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M
$450="bar") * ('worksheet'!$Q$2:$Q$450="1"))

returns "6", which counts all instances without factoring in a value
of "1" in column Q.

Ideas?

There was a typo in my previous examples. They should be:

=SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M
$450="bar") * ('worksheet 1'!$Q$2:$Q$450="1"))

and

=SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M
$450="bar") * ('worksheet 1'!$Q$2:$Q$450="1"))