Creating a macro to count unique items in a list

bearcub · May 31st 2006

I have a situation where I need to count the unique for a customer. The areas to be counted are separated by blank lines.

At present, I am using sumproduct(1/countif(range1,range1), to count the unique items. This formula works perfectly except that it takes me almost an hour to do this for all the measurements.

Is there a macro that can provide me with the same results -- putting the same values where I am presently have the formulas (the cells that before the blank lines)?

Thank you in advance for your help.

PS I had to take out the Bolded values and delete some lines to make have the appropriate file size. The length of the file is normally around 1073 lines.

**Batman** · May 31st 2006

Re: Creating a macro to count unique items in a list

Hi bearcub,

This macro puts the formulas in where they currently are - assuming you don't mind still having the formulas:

Code

Sub CreateCountFormulas()
    Dim rngData As Range
    Dim rngDataArea As Range
    
    Application.Calculation = xlCalculationManual
    Set rngData = Range("B2:B" & Range("B65536").End(xlUp).Row).SpecialCells(xlCellTypeConstants)
    For Each rngDataArea In rngData.Areas
        rngDataArea.Cells(rngDataArea.Cells.Count).Offset(1, 0).FormulaR1C1 = _
            "=SUMPRODUCT(1/COUNTIF(R[-" & rngDataArea.Cells.Count & "]C:R[-1]C,R[-" & rngDataArea.Cells.Count & _
            "]C:R[-1]C))"
    Next
    Application.Calculation = xlCalculationAutomatic
End Sub

Display More

Hope this helps.

carlmack · May 31st 2006

Re: Creating a macro to count unique items in a list

Deleted

**Batman** · May 31st 2006

Re: Creating a macro to count unique items in a list

Carl,

I got the impression that bearcub wanted the number of unique records within each block of data, not for the entire column. If not, that's my macro out the window!

carlmack · May 31st 2006

Re: Creating a macro to count unique items in a list

Yes I think he did too. Just me not reading the post again. When I saw yours I quickly went and deleted my irrelevant waffle hoping nobody would see it . LOL.

bearcub · May 31st 2006

Re: Creating a macro to count unique items in a list

Thank you all for your help.

This is exactly what I need.

Is it looking at the blocks of data to count them (the Areas property listed in the code?)

**Batman** · Jun 1st 2006

Re: Creating a macro to count unique items in a list

Yes. The Areas property relates to each of the separate blocks of consecutive entries. For each area the process finds how many cells there are in the area then goes down 1 row from the last cell and creates the formula you had previously, identifying for each area the first and last cells in the range to use in the formula.

When I tested it on a small sample of your data (I hadn't got the time to move the formula from all rows of your sheet) I got exactly the same answers as you cam up with.

Creating a macro to count unique items in a list

Participate now!

Share