Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Extract from list and count (Excel 2003)

    Hi

    I would like to be able to extract one of each name from a list and then show how many times it appears in the original list.

    Please see attached example of what I am trying to achieve.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract from list and count (Excel 2003)

    The following macro is a variation on the one in <post#=325850>post 325850</post#>:

    Sub UniqueList()
    Dim rListPaste As Range
    Dim rDatabase As Range

    Set rListPaste = Range("E4")

    Set rDatabase = Range("C4", Range("C65536").End(xlUp))
    'May need to specify <!t>[NameofSheet]<!/t>.Range, e.g, Sheet1.Range
    rDatabase.AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=rListPaste, Unique:=True

    ' Extend rListPaste to range of unique values
    Set rListPaste = Range(rListPaste.Cells(1, 1), _
    rListPaste.Cells(1, 1).End(xlDown))
    ' Set frequency formulas
    rListPaste.Offset(0, 1).FormulaR1C1 = _
    "=COUNTIF(" & rDatabase.Address(ReferenceStyle:=xlR1C1) & ",RC<!t>[-1]<!/t>)"
    ' Create column header
    rListPaste.Cells(1, 2).Value = "Frequency"
    ' Sort range
    rListPaste.Resize(ColumnSize:=2).Sort _
    Key1:="Product", Order1:=xlAscending, Header:=xlYes
    End Sub

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract from list and count (Excel 2003)

    You can use Advanced filter to get the list of unique names.

    1- Select the list (C4:C19 in your sheet).

    2- Select Filter from the Data Menu and then Select Advanced Filter from the popup menu.

    3- Click on OK in the message Box to use C4 as the column label.

    4- Click on "Copy to another location" and "Unique records" in the dialog box.

    5- Click in the "Copy to" box, and then Click on the cell where you want the unique list (E4 in your example).

    6- Click OK.

    You should now have a list of unique items. Put the formula below in the cell next to the first item you want to count (F5 in your example).

    <pre>=COUNTIF($C$5:$C$19,E5)
    </pre>


    Adjust the formula for the range of the original list, and the location of the first item in the unique list. Double click on the fill handle on the cell containing the formula to fill it down to the end of the unique list.
    Legare Coleman

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract from list and count (Excel 2003)

    Another way is detailed by Chip Pearson in Duplicate And Unique Items In Lists

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extract from list and count (Excel 2003)

    In addition to the other solutions, a pivot table can do it directly:

    Select the date
    Data - pivot table report..
    <Next><next>
    Drag "Product" to a row field
    Drag product to the data (it should automatically go to "Count of Product"
    Click "Existing WOrksheet" and select "E4"
    <finish>

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract from list and count (Excel 2003)

    Using a fast formula system...

    C4:C19 houses the data of interest, including the label/header.

    D3: 0

    which is mandatory.

    D4: Idx

    which is just a label.

    D5, copied down:

    =IF((C5<>"")*ISNA(MATCH(C5,$C$4:C4,0)),LOOKUP(9.99 999999999999E+307,$D$34)+1,"")

    E3:

    =LOOKUP(9.99999999999999E+307,$D$5:$D$19)

    E4: Distinct Products

    which is just a label.

    E5, copied down:

    =IF(ROW()-ROW($E$5)+1<=$E$3,LOOKUP(ROW()-ROW($E$5)+1,$D$5:$D$19,$C$5:$C$19),"")

    F4: Count

    which is just a label.

    F5, copied down:

    =IF(E5<>"",COUNTIF($C$5:$C$19,E5),"")
    Microsoft MVP - Excel

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract from list and count (Excel 2003)

    Hi Aladin
    I'm sorry it took so long to reply, but I would lilke to take this opportunity to thank everyone who replied. I decided to go with Aladins option.
    I would like to ask a furthe request I would like to add ISNA to the formulas below but I have terrible trouble with my bracket placings, only the list will be blank when sent out.

    Many Thanks
    Braddy

    =IF(ROW()-ROW($E$5)+1<=$E$3,LOOKUP(ROW()-ROW($E$5)+1,$D$5:$D$19,$C$5:$C$19),"")


    =IF(E5<>"",COUNTIF($C$5:$C$19,E5),"")
    If you are a fool at forty, you will always be a fool

  8. #8
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract from list and count (Excel 2003)

    Braddy,

    There is no need for ISNA for these formulas

    =IF(ROW()-ROW($E$5)+1<=$E$3,LOOKUP(ROW()-ROW($E$5)+1,$D$5:$D$19,$C$5:$C$19),"")


    =IF(E5<>"",COUNTIF($C$5:$C$19,E5),"")

    will never return #N/A. unless the range to process is empty.

    To cover the last possibility, I'd suggest to change the formula in E3:

    =LOOKUP(9.99999999999999E+307,$D$5:$D$19)

    to:

    =LOOKUP(9.99999999999999E+307,$D$3:$D$19)
    Microsoft MVP - Excel

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract from list and count (Excel 2003)

    HI Aladin

    The range list will be empty originaly so I have changed the range a suggested to

    =LOOKUP(9.99999999999999E+307,$D$3:$D$19)

    Thank you very much for your help.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •