Help needed converting list.

  • Thread starter Thread starter Chris Mitchell
  • Start date Start date
C

Chris Mitchell

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
 
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)))

--
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?
 
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.
 
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.
 
Back
Top