Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter for frist record in a sorted list
I am trying to use a computed/calculated filter for a list of records sorted
by the first column. The first column has SSNumbers with multiple duplicates. I want to filter for just the first SSNs of those duplicates - so a unique list of SSN and what comes with that record. This is what I came up with: 1. =$A2<$A1 or =$A2<$A3 (but this leaves either the first or the last unique entry out). 2. =COUNTIF(SA$2:A2,A2)=1 (but then I get all the first duplicates together, and no other records). What should I have done instead? Thanks so much for your solution. Gerry |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter for frist record in a sorted list
for your second equation, you should have two results 1s and 0s
the 1s shoul dbe bothe the first duplicate and the unique records are yoiu using autofilter to then select what you want or what are you doing after you have applied the euation. you could also use advanced filter to select a unique lis of serial numbers and then use Vlookup t select the first instancwe of match. "Gerry Verschuuren" wrote: I am trying to use a computed/calculated filter for a list of records sorted by the first column. The first column has SSNumbers with multiple duplicates. I want to filter for just the first SSNs of those duplicates - so a unique list of SSN and what comes with that record. This is what I came up with: 1. =$A2<$A1 or =$A2<$A3 (but this leaves either the first or the last unique entry out). 2. =COUNTIF(SA$2:A2,A2)=1 (but then I get all the first duplicates together, and no other records). What should I have done instead? Thanks so much for your solution. Gerry |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter for frist record in a sorted list
I use an advanced filter with the formula in the filter. So the formula does
not give me 0's and 1's by only TRUE or FALSE for the 1st record. Then I apply the filter and do not get what I expect. "bj" wrote: for your second equation, you should have two results 1s and 0s the 1s shoul dbe bothe the first duplicate and the unique records are yoiu using autofilter to then select what you want or what are you doing after you have applied the euation. you could also use advanced filter to select a unique lis of serial numbers and then use Vlookup t select the first instancwe of match. "Gerry Verschuuren" wrote: I am trying to use a computed/calculated filter for a list of records sorted by the first column. The first column has SSNumbers with multiple duplicates. I want to filter for just the first SSNs of those duplicates - so a unique list of SSN and what comes with that record. This is what I came up with: 1. =$A2<$A1 or =$A2<$A3 (but this leaves either the first or the last unique entry out). 2. =COUNTIF(SA$2:A2,A2)=1 (but then I get all the first duplicates together, and no other records). What should I have done instead? Thanks so much for your solution. Gerry |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter for frist record in a sorted list
rather than using these equations with advanced filter,
I assume your data is sorted I would use a helper column and change your equaition to in row 1 1 in row 2 =if(A2=A1,"",1) copy row two down use auto filter on the helper column and select the 1s if not sorted in the helper column row 1 =countif(A$1:A1,A1) autofilter select 1s "Gerry Verschuuren" wrote: I use an advanced filter with the formula in the filter. So the formula does not give me 0's and 1's by only TRUE or FALSE for the 1st record. Then I apply the filter and do not get what I expect. "bj" wrote: for your second equation, you should have two results 1s and 0s the 1s shoul dbe bothe the first duplicate and the unique records are yoiu using autofilter to then select what you want or what are you doing after you have applied the euation. you could also use advanced filter to select a unique lis of serial numbers and then use Vlookup t select the first instancwe of match. "Gerry Verschuuren" wrote: I am trying to use a computed/calculated filter for a list of records sorted by the first column. The first column has SSNumbers with multiple duplicates. I want to filter for just the first SSNs of those duplicates - so a unique list of SSN and what comes with that record. This is what I came up with: 1. =$A2<$A1 or =$A2<$A3 (but this leaves either the first or the last unique entry out). 2. =COUNTIF(SA$2:A2,A2)=1 (but then I get all the first duplicates together, and no other records). What should I have done instead? Thanks so much for your solution. Gerry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter record on color scheme | Excel Worksheet Functions | |||
Sorted list | Excel Discussion (Misc queries) | |||
Record count in filter mode | Excel Discussion (Misc queries) | |||
advance filter excluding record | Excel Discussion (Misc queries) | |||
Why does my advance filter only show one record? | Excel Discussion (Misc queries) |