VBA to extract unique values from list and count them on separate page

Question

Ok, this has to be the easiest question ever, but I've been searching for awhile and can't figure this out. I have a very long list of values on sheet "Page1_1" (shtData) which I need to use to generate a list of unique values, along with a count of each unique value, on "Numbers". It's very simple to do manually by just copying to "Numbers" (shtNumbers) and removing duplicates, but the countif formula I use is very memory intensive, and bogs it all down. I have code to provide the unique list, but trying to get the count of each item is stumping me

Put simply: I need code to generate a list of unique values from "Page1_1" (shtData) A2:end of data, and put that list starting on "Numbers" (shtNumbers) A2 and down, then count occurrences and list the count in B2 and down.

current code:

shtData.Range("A2:A65536").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=shtNumbers.Range("A2"), Unique:=True

That code generates the list for me perfectly. I'd post what I've got for the count part, but it doesn't work at all, so I doubt it'd be helpful at all.

Thanks!

I could, but I'd have to set it up every time I run this report, which is often. With a macro, I can just hit a command button and it's done. Of course, I also thought this would be easy to code, so what do I know? — user4907546
– user4907546, Commented Jan 4, 2018 at 21:46

Scott Holtzman · Accepted Answer · 2018-01-04 21:47:40Z

2

Try this:

With shtNumbers

    With .Range(.Range("B2"),.Range("A2").End(xlDown).Offset(,1))

        .FormulaR1C1 = "=COUNTIF(Data!C[-1],RC[-1])" 'change data to actual sheet name

    End With

End With

answered Jan 4, 2018 at 21:47

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user4907546 Over a year ago

This is exactly what I needed! Thanks for the quick reply.

Gary's Student · Accepted Answer · 2018-01-04 22:03:12Z

1

Give this a try:

Sub SuperSimpleFrequencyTable()
    Dim C As Range, A As Range, B As Range

    Set A = Sheets("Page1_1").Range("A:A")
    Set B = Sheets("Numbers").Range("A:A")

    A.Copy B
    B.RemoveDuplicates Columns:=1, Header:=xlNo
    Set C = B.SpecialCells(xlCellTypeConstants).Offset(0, 1)

    With C
        .Formula = "=countif(Page1_1!A:A,A1)"
        .Value = .Value
    End With

End Sub

It will not leave formulas in the Numbers sheet.

answered Jan 4, 2018 at 22:03

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

VBA to extract unique values from list and count them on separate page

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related