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

--
Don Guillett
SalesAid Software
[email protected]
"Ingeniero1" <[email protected]> wrote
in message news:[email protected]...
>
> (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
>
>
> --
> Ingeniero1
> ------------------------------------------------------------------------
> Ingeniero1's Profile:
http://www.excelforum.com/member.php...fo&userid=4029
> View this thread: http://www.excelforum.com/showthread...hreadid=389339
>

[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 each number occurs. Of course, the number of repeats is listed each time as well, so they themsleves are repeated.

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

What I have (abbreviated example):
A1:A253 = numbers sorted ascending
B1:B253 = the CountIf formula with the resulting 'repeats' for each job 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.

Alex

[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!
Alex