Select two items from many

[Slim Slender]

There are about 100 records in a table. Column A has names in it, many
different names and each name occurring from 1 to any number of times.
I want to select a group of records consisting of the first two
occurances of each name. In the case where a name occurs only once,
one record. The records can be sorted by name.

 

[Paul Robinson]

Hi
No need for VB
1. Sort your records by name
2. Create a new column called Count
3. In the Count column, first row, put in 1. I will assume that your
names are in column A, headings are in row1 with the Count column in
column B. So the 1 is in B2.
4. Put this formula in B3
=IF(A3=A2,B2+1,1)

5. Copy the formula down.
6. Select the records (including the Counts) with headings and put on
Autofilter. Click on the Count dropdown then Custom.... In the first
box choose the condition "is less than or equal to" and in the box
next to it type 2.

You can now copy and paste the filtered list as required.
regards
Paul

 

[Slim Slender]

Hi
No need for VB
1. Sort your records by name
2. Create a new column called Count
3. In the Count column, first row, put in 1. I will assume that your
names are in column A, headings are in row1 with the Count column in
column B. So the 1 is in B2.
4. Put this formula in B3
   =IF(A3=A2,B2+1,1)

5. Copy the formula down.
6. Select the records (including the Counts) with headings and put on
Autofilter. Click on the Count dropdown then Custom.... In the first
box choose the condition "is less than or equal to" and in the box
next to it type 2.

You can now copy and paste the filtered list as required.
regards
Paul



- Show quoted text -

Thanks Paul, I'll try this.

 

[Slim Slender]

Thanks Paul, I'll try this.- Hide quoted text -

- Show quoted text -

Works for me!