Help with formula

[Richard]

On Sheet1 column A:A there is a list of different part
numbers, (some that are the same part numbers) over and
over again. What I'd like to do is: Each time a part
number is entered in sheet1 column A:A, that part number
be sent to sheet2 in columa A:A (without duplicating that
part number)and in the cell next to that part number count
each time that part number was entered in sheet1 column
A:A. I know this is asking alot! Thanks in advance so much!

 

[Biff]

Hi!

You have 2 different requests. The second one, counting
the number of times a particular part number appears is
easy. The first one, I don't think this can be done with a
single formula. I'm pretty good at extracting data and I
can't figure out how to do it without using helper columns
and sorting.

So, the best way to do this is to use an Advanced Filter
and copy the unique items in the list to your sheet2.
Since you said you will be adding part numbers you should
also use a dynamic named range to define your list.

There are excellent instructions on these subjects here:

http://contextures.com/xladvfilter01.html
http://contextures.com/xlNames01.html#Dynamic

You could even record a macro to automate this procedure.

As far as counting the part numbers, once you have the new
list in sheet2 cell A1:An, in cell B1 enter a formula like
this:

=COUNTIF(Sheet1!A$1:A$n,A1)

If you use a dynamic named range then you can use that in
the formula:

=COUNTIF(MyRange,A1)

Good luck!

Biff