I have a single column list that is 700+ rows long.
Individual items within the list can appear several times in the list.
I want to convert this to a list where each item only appears once with the
sum of the number of times it occurred in the original list in an adjacent
column.
How can I do this?
Sub getuniquecount()
'get unique names for list
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range(Cells(1, "d"), Cells(lr, "e")).ClearContents
Range("A2:A" & lr).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("D2"), Unique:=True
dlr = Cells(Rows.Count, "d").End(xlUp).Row
'countem
For Each x In Range("d2:d" & dlr)
mc = 0
With Range("a1:a" & lr)
Set c = .Find(x)
If Not c Is Nothing Then
firstAddress = c.Address
Do
mc = mc + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Cells(x.Row, 5) = mc
Next x
End Sub

Signature
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
>I have a single column list that is 700+ rows long.
>
[quoted text clipped - 5 lines]
>
> How can I do this?
Chris Mitchell - 12 Dec 2007 16:10 GMT
Thanks Don.
I'm sure it does exactly as intended, but it's not what I was expecting.
Is this a Macro, VBE or what?
I presume I can copy and paste it in somewhere, but where, and how?
If you could give me a few pointers I'll give it a go, and no doubt learn a
great deal along the way.
> Sub getuniquecount()
> 'get unique names for list
[quoted text clipped - 29 lines]
>>
>> How can I do this?
Don Guillett - 12 Dec 2007 16:54 GMT
It is a macro
If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Signature
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
> Thanks Don.
>
[quoted text clipped - 40 lines]
>>>
>>> How can I do this?
Say your list is in Column A, from A1 to A700.
In B1, enter,
=A1
In B2, enter this *array* formula:
=IF(ISERR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$700&""),0)),"",INDEX(IF(ISBLANK($A$1:$A$700),"",$A$1:$A$700),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$700&""),0)))

Signature
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.
*After* the CSE entry, copy down until you run out of returns.
In C1, enter this formula:
=COUNTIF(A$1:A$700,B1)
And copy down as needed.
--
HTH,
RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================
I have a single column list that is 700+ rows long.
Individual items within the list can appear several times in the list.
I want to convert this to a list where each item only appears once with the
sum of the number of times it occurred in the original list in an adjacent
column.
How can I do this?
Yet another way with Pivot Table.
No code or formulas required.
Assume your list has a header called MyList
Data > Pivot Table
Range: Select your list, including header
Layout: Drag MyList into ROW
Drag MyList into DATA
Double click to make it say: Count of MyList
Options: Uncheck Grand Totals and AutoFormat
Finish
Thanks to all who offered suggestions.
I learned a bit more.
Found the pivot table solution the easiest, but will persevere with the
others.
>I have a single column list that is 700+ rows long.
>
[quoted text clipped - 5 lines]
>
> How can I do this?