Finding unique records (all columns) based on criteria (two columns)

[FarzanaSaleem]

In an excel sheet, I have employee's position titles and departments. I have to find unique positions in each department.

ColumnB: Position Title
ColumnC: Department

There are some more columns in this sheet in addtion to the above two.

Sample Values are:
P1 D1
P2 D2
P3 D3
P1 D4
P5 D5
P6 D6
P1 D1
P7 D7
P5 D8
P5 D5
P9 D9
P10 D10

Output needed is:
P1 D1
P2 D2
P3 D3
P1 D4
P5 D5
P6 D6
P7 D7
P5 D8
P9 D9
P10 D10 (along with other columns)

I used advanced filter with the following options but with no success:
Action: Filter the list in-place
List Range: Complete Sheet
Criteria Range: $B$2:$C$5446
Unique Records only checked

Following message displayed in status bar:
5444 of 5444 records found

 

[xlbo]

In which case it is reading them as non - duplicated values - are you sure you have consistent data as before I read the end of your post, I would've suggested doing exactly as you have !!

You may have spaces at the end of some text and not for others

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[FarzanaSaleem]

Geoff,

Message in the status bar is strange for me as the records were not filtered at all. They remained 5445 and not 5444.

What I did this time is that I selected Copy to another location in the action and entered $A$5448 in the Copy to box. This time I got the following message:

The extract range has a missing or illegal field name

 

[xlbo]

5444 will refer to the number of records - I'm assuming you have a header which means that there would be 5445 rows in TOTAL but 5444 RECORDS

Have alook in your data and see if you have trailing balnks - it's the only thing I can think of that would make this not work (I've even run a test check to make sure that the concept is sound and it is !)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[FarzanaSaleem]

Geoff,

I checked for the sample data as well and it didn't work. I must be making some mistake:

For the following file, I proceeded as follows:

1. Cell Pointer on A1:
2. Data -> Filter -> Advance Filter ...
3. Action: Filter the list in place
4. List Range: $A$1:$D$13
5. Criteria Range: $A$1:$B$13
6. Unique records only checked
7. Message in the status bar, 12 of 12 records found.

It is needed as 10 filtered records

 

[xlbo]

Very bizarre - I just did exactly that and it worked

What version of Excel are you using - I'm on XP so if you are on an earlier version, you may not be able to use this functionality...

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[GlennUK]

Have the list range as $A$1:$B$13 and leave the Criteria as blank, and try it.

Glenn.

 

[FarzanaSaleem]

Geoff,

When I removed Columns C and D and then used advanced filter on 2 columns, then I got the required result:

10 of 12 records found.

??

 

[xlbo]

Farzana - that's how I initially thought it would work - that you can't filter on a subset for unique values but that's just what I did and it worked.....

Glenn - it works for me - exactly as Farzana posted in the list of actions - OfficeXP ExcelXP

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[GlennUK]

Hi Geoff,

didn't work for me, Excel 2000, which is why I posted that suggestion. Every damned version reacts differently to things.

Cheers, Glenn.

 

[xlbo]

Advanced filter seems especially prone to version changes. Seems like every single one of 'em has different Advanced Filter "Features".
I'm seriously considering modding my menubar to call it "Slow Filter" or "Slightly Thick Filter" !!

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question