Extract from list and count (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

The following macro is a variation on the one in post 325850:

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 [NameofSheet].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[-1])”
‘ Create column header
rListPaste.Cells(1, 2).Value = “Frequency”
‘ Sort range
rListPaste.Resize(ColumnSize:=2).Sort _
Key1:=”Product”, Order1:=xlAscending, Header:=xlYes
End Sub

Reply | Quote

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

=COUNTIF($C$5:$C$19,E5)

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.

Reply | Quote

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

Reply | Quote

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

Select the date
Data – pivot table report..

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”

Steve

Reply | Quote

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.99999999999999E+307,$D$3:D4)+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),””)

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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

Reply | Quote