Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / December 2007

Tip: Looking for answers? Try searching our database.

Help needed converting list.

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Chris Mitchell - 12 Dec 2007 13:27 GMT
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?
Don Guillett - 12 Dec 2007 14:23 GMT
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?
RagDyeR - 12 Dec 2007 15:52 GMT
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?
Herbert Seidenberg - 12 Dec 2007 19:43 GMT
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
Chris Mitchell - 14 Dec 2007 08:51 GMT
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?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.