List similar to Auto-Filter's?

[Ingeniero1]

(I understand that perhaps this thread belongs under “Programming” or
under “Functions”?)

The Auto-Filter drop-down list provides, in ascending order, all
occurrences in the selected column cells, but only once; in other
words, it detects repeated entries and does not duplicate them in the
list.

My subject spreadsheet consists of several columns, with one column
containing job numbers. Several rows may have the same job numbers, and
I sort the list so the numbers are grouped. From the sorted list, I
manually create a separate list that includes the job numbers only
once, without duplicates, just as the Auto-Filter drop down list.

Is there a better way (other than manually) to achieve the same
results; i.e., create a list that contains every unique job number but
no repeats? As an added feature, I would like to count the occurrences
as well!

Example:
Source
123456
234567
123456
564535
234567
123456
123456

Results
123456 – 4
234567 – 2
564535 – 1

Thanks!

Alex

 

[swatsp0p]

Advanced Filter will do what you need. Two steps required.

First, in a helper column you need to count the occurances of each job
number using the following formula (copied down the entire range):
=COUNTIF($A$2:$A$100,A2)
This will return the number of times each number occurs within your
list. [of course, adjust the ranges to fit your needs].

Next, select Data>Filter>Advanced Filter... and set the range to only
include the column of your job numbers (e.g. $A$2:$A$100) and check the
box for "Unique Records Only". Click OK

You will now have a list of unique records with the count each one
has.

Does this work for you?

 

[Don Guillett]

Have a look at
data>filter>advanced filter>unique items and then use the SUMIF function

 

[goldcup]

A quick way is to use a pivot table
you need a column header (the first row) so for the explanation let's
just call it Sample#s.

Sample#s
123456
234567
123456
564535

Select Column with the data
menu item: Data>PivotTable and PivotChart Reports
next, next
Layout
drag Sample#s to the Data area
drag Sample#s to the Row area
Ok, Finish

 

[goldcup]

Layout

drag Sample#s to the Data area
drag Sample#s to the Row area

I forgot to mention double click the Sample#s label in the Data area
and choose Count (I think the default for numbers is Sum)

 

[Ingeniero1]

Bruce,
The first part worked - to count the number of instances that eac
number occurs. Of course, the number of repeats is listed each time a
well, so they themsleves are repeated.

I can't get the other part to work, though. Maybe I am doing somethin
wrong.

What I have (abbreviated example):
A1:A253 = numbers sorted ascending
B1:B253 = the CountIf formula with the resulting 'repeats' for each jo
number.

Advanced filter setup:
Selected A1:B253
Then, when Adv. Filter box opens,
(•) Filter List in Place
List Range = A1:A253
Criteria = Tried none and also A1:A253
[•] Unique records only

But nothing happens - nothing changes.
I was expecting the rows with repeated job numbers to not be displayed
but this is not the case.

Ale

 

[swatsp0p]

Oops, I forgot to mention that your filter range must have a heading, so
in A1 (insert a blank row if needed) give your column a name, such as
"Job #", then with A1 selected, follow the steps for setting the
Advanced Filter
(•) Filter List in Place
List Range = A2:A253
Criteria = blank
[•] Unique records only

Does it work now?

 

[Ingeniero1]

Yes!
Thanks!
Ale