Solved Unique item counter plus total

Microsoft Excel for mac 2011 - macintosh
January 16, 2016 at 03:35:41
Specs: Mac OSX 10.8.4
If I have hundreds of entries (some entries could be repeated as duplicates or more) under Col. A. How can I create a macro that will sort and show only the unique items from Col. A onto Col. B, and also show the sum of each unique item in Col. C adjacent to the item in Col. B. Also a total of the counter of items somewhere; either below the sum or anywhere else. See below:

Col.A Col. B Col. C
998 Item # Quantity
997 277 1
997 355 1
355 997 2
998 998 3
998 999 1
999 8

I hope I didn't make it too confusing. I have problem lining up the number with the columns. TIA.

message edited by uhjb

See More: Unique item counter plus total

Report •

January 16, 2016 at 07:10:04
You do not need a macro for this, try:

To select only Unique Records:

First, Select your range of cells, IE: A2:A9

Next, on the Data tab, in the Sort & Filter group, click Advanced.

In the Advanced Filter dialog box:

Select the Copy to another location button.

In the Copy to box , enter cell B2.

Check the Unique records only check box,

click OK.

Now to get the number of Items, in cell C2 enter the formula:


The above formula is set to use rows A2 thru A9, as in your example, modify to suit your needs. Then drag down as many rows as necessary.

See how that works for you.


message edited by mmcconaghy

Report •

January 16, 2016 at 07:29:12
The link below shows 3 ways to create a list of unique values from a list with duplicates.

1 - The manual Advanced Filter method as offered by Mike
2 - An Array Formula method which would be a bit more automatic, allowing for the unique values to be updated whenever the original list is updated
3 - A macro which automatically creates the Advanced Filtered list discussed above

While Method 2 is automatic, it does not allow for the sorting of the values. However, that could be solved by using the Array Formula in a "helper column" and then using the SMALL function to sort those values automatically.

Method 3, the macro option, could have the Sort feature added with just a few more lines of code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 16, 2016 at 08:23:32
✔ Best Answer
For another way to do it:

Select and Copy all of Column A to Column B.

Next, select just Column B, and on the Data tab, select Remove Duplicates.

If you get a Warning Box, click the button next to: Continue with current selection

In the Remove Duplicates dialog box:

If you have a Header cell, check the box next to: My Data Has Headers

Make sure column B is checked

Click OK

Column B should now have only Unique records,

In Cell C2 use the formula =COUNTIF(A$2:$A$9,B2)
same as in my previous reply.


Report •

Related Solutions

January 16, 2016 at 15:40:06
Thanks Mike & DerbyDad03 for all your wonderful suggestions. I'm able to get the data I need with your help. Your effort was greatly appreciated. 👍

Report •

April 14, 2016 at 02:02:36
Thank you for this macro that sorts and shows only the unique items from Col. A onto Col. B, and it also shows the sum of each unique item in Col. C adjacent to the item in Col. B.

Report •

Ask Question