subtotal of identical data and deleting its duplicate

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good Morning:

I have a column that was use for inventory control. I want to subtotal that
column and delete its duplicates.

example:

abc
abc
def
def
def

I want column b to give me the count for each repetive cell and then delete
the duplitates. The result should be:

abc 2
def 3

Please help.

Thank you.

PaolaAndrea
 
Hi PaolaAndrea. There is a way, not elegant, but it works. Sort the
inventory items in column A to bring all the duplicate items together. Next,
go to data - subtotal and select count. Copy your data and paste special -
values to a new worksheet starting in A2. Put labels in A1 and B1. Column A
will have abc count and def count and column B will have the abc's, def's and
the count of each next to where it says abc and def count. Label column C
total and in C2 type: =if(right(a2,5)="count","T","") and copy down the
length of your data. This will put a T in Column C in each row that has a
count of the inventory items. Sort column C descending to bring all the T's
to the top and delete all the rest. Finally, delete Column C and then go to
Edit - Replace and Replace the word Count with nothing. Please post back if
this is too confusing and I'll try to make it clearer. HTH
 
If you add headers, you could use data|pivottable.

If you sort your data (after adding headers), you could use data|subtotals and
use count.

then use the outlining symbols at the left to hide the details.
select those cells you can see
edit|goto special|visible cells only
edit|copy
start a new sheet
edit|paste

You may want to select that first column and
edit|replace
what: (spacebar)Count
with: (leave blank)
replace all

(and get rid of the bolding.)
 
Back
Top