counting unique entries in a list

[Michael]

Hey Guys!

I was wondering if someone could help me. I am trying to write a script that
goes parses through an excel file that contain iventory information. I
already wrote code to cover most of the functionality I am trying to do, but
I am having some difficulty with respect to two areas.

1) The lists vary in length so some 10 records others have 500 records. Is
there an easy way to do a loop to the last element in the list? Like... for
n=1 to sheet.end or something like that? (I do not want to manually change
the bounds for each list.)

2) I am also finding it difficult to automatically generate the list of
unique entries in the script. Currently, I am doing it manually (which is a
really pain). I am trying to get a list of unique items so later on I can
use this list to count their respective quantities.

So if my list is like:

Item Qty
boxes 5
paper 6
paper 1
pens 2
boxes 2

It would return:
boxes
paper
pens

So I can do the counts later.

I greatly appreciate any assistance and/or suggestion you could provide.

Thanks,

-Michael

 

[Bob Phillips]

To find the last row in VBA

iLastrow = Cells(Rows.Count,"A").End(xlUp).Row

You can gernearte a list of uniques with this array formula

=IF(ISERROR(MATCH(0,COUNTIF($B$2:B2,$A$2:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$2:$A$20),"",$A$2:$A$20),MATCH(0,COUNTIF($B$2:B2,$A$2:$A$
20&""),0)))

which assumes the source is in A2:A20, and this formula starts at B3. In B2,
enter the first value from the source, such as =A2

--

HTH

RP
(remove nothere from the email address if mailing direct)