Count Unique Values

G

Guest

I work for a transportation company. On a spreadsheet, I have a list of
Shippers, beside that, I have a list of unique SKUs that the coresponding
shipper carried. Beside that, I have the coresponding Bill of Lading (BOL)
that the Carrier took that SKU out on. I need to count how many times a
particular shipper had a unique BOL.

In the example below, I need the formula to return that there were 2 BOLs
associated with AVRT. I don't need to know what numbers they are or what SKUs
were on them, but I need to know how many shipments there were.

Shipper SKU BOL
AVRT asd 123
AVRT sfg 123
GST dgh 345
JBH ert 876
GST sdf 645
AVRT ghj 875
AVRT skr 875

Thank You!
 
T

T. Valko

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

E2 = AVRT

=SUM(--(FREQUENCY(IF(A2:A8=E2,MATCH(C2:C8,C2:C8,0)),MATCH(C2:C8,C2:C8,0))>0))

Biff
 
H

Harlan Grove

T. Valko said:
Entered as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER):

E2 = AVRT

=SUM(--(FREQUENCY(IF(A2:A8=E2,MATCH(C2:C8,C2:C8,0)),
MATCH(C2:C8,C2:C8,0))>0))
Click to expand...
....

Maybe, but a simpler, nonarray formula would also work:

=SUMPRODUCT((A2:A8=E2)/COUNTIF(C2:C8,C2:C8))

This would only break down if the col C values could start with
comparison operators (<, =, >) or include wildcard characters.
Actually, wildcard characters could also screw up the MATCH calls.
 
H

Harlan Grove

Alan Beban said:
If the functions in the freely downloadable file at ....

=ArrayCount(ArrayUniques(VLookups("AVRT",A1:C8,3)))
Click to expand...
....

Better idea for the OP would be using Laurent Longre's MOREFUNC.XLL
add-in, available from

http://xcell05.free.fr/english/

then using the formula

=COUNTDIFF(IF(A2:A8=E2,C2:C8),,FALSE)
 
G

Guest

With
your posted table of data in A1:C10 (I added some blank cells)
AND
the assumption that BOL cells will be either numbers or numeric text (123 or
023)

Then if
E1: AVRT

this regular formula returns the count of UNIQUE BOLs for AVRT
F1: =SUMPRODUCT(--(FREQUENCY((A2:A10=E1)*C2:C10,(A2:A10=E1)*C2:C10)>0))-1

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
G

Guest

Regarding:
SUMPRODUCT((A2:A8=E2)/COUNTIF(C2:C8,C2:C8))

Possible issue if the same Bill of Lading ref could exist for more than one
shipper. Not enough information to determine if there's any exposure on that,
though.

***********
Regards,
Ron

XL2002, WinXP
 
D

Domenic

Assuming that A2:C8 contains the data, and E2 contains AVRT, try the
following formula which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF($A$2:$A$8=E2,IF($C$2:$C$8<>"",MATCH("~"&$C$2:$C$8,$C
$2:$C$8&"",0))),ROW($C$2:$C$8)-ROW($C$2)+1),1))

Note that Column C can contain numerical values, text values,
empty/blank cells, comparison characters, and wildcard characters.

Hope this helps!
 
T

T. Valko

Note that Column C can contain numerical values, text values,
empty/blank cells, comparison characters, and wildcard characters.
Click to expand...

That's just about everything and the kitchen sink! But........what about
error values and booleans?

<VBG>

Biff
 
H

Harlan Grove

Ron Coderre said:
Regarding:
SUMPRODUCT((A2:A8=E2)/COUNTIF(C2:C8,C2:C8))

Possible issue if the same Bill of Lading ref could exist for more
than one shipper. Not enough information to determine if there's
any exposure on that, though.
Click to expand...
....

It gets more complicated and requires a longer formula.

=SUM(IF(A2:A8=E2,1/MMULT(TRANSPOSE(A2:A8=E2)
*(C2:C8=TRANSPOSE(C2:C8)),ROW(C2:C8)^0)))

The advantage of this formula is that it works when col C values could
contain wildcards. But maybe overkill. My main objection to Biff's
formula was multiple MATCH calls, but they could be reduced.

=COUNT(1/FREQUENCY(IF(A2:A8=E2,MATCH(C2:C8,C2:C8,0)),
ROW(C2:C8)-MIN(ROW(C2:C8))+1))
 
D

Domenic

T. Valko said:
That's just about everything and the kitchen sink! But........what about
error values and booleans?

<VBG>

Biff
Click to expand...

I guess not quite everything... <VBG>
 
T

T. Valko

Ok...

I've abandoned my use of SUM(..MATCH...MATCH) in favor
of COUNT(..MATCH...ROW)

Biff
 
G

Guest

I am trying to get your formula to work, but I keep getting 'FALSE.' This is
my formula

=COUNTDIFF(IF(log!R4:R65536=BC3,log!T4:T65536),,FALSE)

And this is a sample of my columns


(col R) (col T)
Carrier BOL #

GIST (BAH) 104502
GIST (BAH) 104502
GIST (BAH) 104502
AVRT 104501
AVRT 104501
AVRT 104501

I would like to know that there is one AVRT BOL in that list. Why is this
returning 'FALSE'?

Thanks so much for the help.
 
H

Harlan Grove

Averitt Engineer said:
I am trying to get your formula to work, but I keep getting 'FALSE.'
This is my formula

=COUNTDIFF(IF(log!R4:R65536=BC3,log!T4:T65536),,FALSE)
Click to expand...

I can't get COUNTDIFF to return FALSE. Zero (0), yes. FALSE, no.
And this is a sample of my columns

(col R) (col T)
Carrier BOL #

GIST (BAH) 104502
GIST (BAH) 104502
GIST (BAH) 104502
AVRT 104501
AVRT 104501
AVRT 104501
Click to expand...
....

Given the above (including the column headings and blank row) in R1:T8
(with col S blank), and with AVRT in cell W1 rather than BC3, the
array formula

=COUNTDIFF(IF(R3:R8=W1,T3:T8),,FALSE)

returns 1 on my system. What version of MOREFUNC.XLL are you using?
Also, if you don't enter the formula as an array formula, it'll return
#VALUE!. Is that what you mean by FALSE? Also, it's possible there are
stray trailing spaces in either your col R values or your BC3 value.
You could try the array formula

=COUNTDIFF(IF(TRIM(log!R4:R65536)=TRIM(BC3),log!T4:T65536),,FALSE)
 
B

Bernd

Hello,

I suggest a careful step by step approach:

If your data is in Sheet1, A1:C8:
1. Create a helper column in sheet1. Enter
=A1&";"&C1
into D1 and copy down to D8

2. Get my UDF List_Freq from http://www.sulprobil.com/html/listfreq.html
and insert it into a macro module:
Press ALT + F11, insert a module, copy macro text into module, go back
to worksheet, but now Sheet2 (TWO!)

3. Select cells A1:B8 in sheet2 and array-enter
=List_Freq(Sheet1!D1:D8,1)
[Press CTRL + ALT + ENTER to enter formula, curly brackets should
enclose your formula after that]

4. Create a helper column in sheet2. Enter
=LEFT(FIND(";",A1&";")-1)
into C1 and copy down to C8

5. Select cells A1:B8 in sheet3 and array-enter
=List_Freq(Sheet1!C1:C8,1)
[Press CTRL + ALT + ENTER to enter formula, curly brackets should
enclose your formula after that]

Sheet3 shows your results. Ignore blank and zero entries in first
rows.

I hope it is an advantage to see intermediate results here. You can
spot user entry errors easily, I think.

Regards,
Bernd
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count of unique values 8

Top