Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get nth distict value in a column
Hi,
Is there anyone clever enough to know how to work this out? I've got a column with a list of data. In a summary I'd like to be able to get a distinct list from the column. So from the following source data: Column ----------- IB3\SJ16 IB3\SJ16 IB3\SJ16 SJ17 SJ17 IB3 IB3\SJ16 Verdi SJ17 Tax Certs Verdi Verdi I'd like to be able to get Summary Column ------------- IB3\SJ16 SJ17 IB3 Verdi Tax Certs I've tried playing with the various LOOKUP functions but this has got me stumped. Can anyone point in the direction on how to get this working. Many thanks Si |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get nth distict value in a column
Use Data-Filter-Advanced Filter
Select "Copy to another location" and indicate the range to copy to in the bottom edit box, then be sure to check "Unique records only" "Si" wrote: Hi, Is there anyone clever enough to know how to work this out? I've got a column with a list of data. In a summary I'd like to be able to get a distinct list from the column. So from the following source data: Column ----------- IB3\SJ16 IB3\SJ16 IB3\SJ16 SJ17 SJ17 IB3 IB3\SJ16 Verdi SJ17 Tax Certs Verdi Verdi I'd like to be able to get Summary Column ------------- IB3\SJ16 SJ17 IB3 Verdi Tax Certs I've tried playing with the various LOOKUP functions but this has got me stumped. Can anyone point in the direction on how to get this working. Many thanks Si |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get nth distict value in a column
In B1: =A1
In B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"", INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2 0&""),0))) which is an array formula, so commit with Ctrl-Shift-Enter Copy B2 down. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Si" wrote in message oups.com... Hi, Is there anyone clever enough to know how to work this out? I've got a column with a list of data. In a summary I'd like to be able to get a distinct list from the column. So from the following source data: Column ----------- IB3\SJ16 IB3\SJ16 IB3\SJ16 SJ17 SJ17 IB3 IB3\SJ16 Verdi SJ17 Tax Certs Verdi Verdi I'd like to be able to get Summary Column ------------- IB3\SJ16 SJ17 IB3 Verdi Tax Certs I've tried playing with the various LOOKUP functions but this has got me stumped. Can anyone point in the direction on how to get this working. Many thanks Si |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get nth distict value in a column
Thanks Bob, this is exactly what I was looking for and it works a
treat! Thanks for your time Duke, but I was hoping for a function. Cheers Si |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get nth distict value in a column
Beautiful! I didn't supposed it could be done so easily. Well done
Bob! topola |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get nth distict value in a column
The good thing about this is that if you change a value in the source data,
the target data is automatically updated. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Si" wrote in message oups.com... Thanks Bob, this is exactly what I was looking for and it works a treat! Thanks for your time Duke, but I was hoping for a function. Cheers Si |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get nth distict value in a column
This might appeal to beginners.
Highlight all duplicate cells in your list with conditional formatting, then delete those cells. Use R1C1, then switch back to A1. Insert Name Create or Define (say ListA) Conditional Formatting Formula Is =MATCH(RC,ListA,0)<(ROW()-ROW(ListA)+1) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get nth distict value in a column
Bob Phillips wrote...
In B1: =A1 In B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"", INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20), MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0))) .... Compact form B2: =IF(SUM(COUNTIF(B$1:B1,A$1:A$20))<COUNTA(A$1:A$20) , INDEX(A$1:A$20,MATCH(0,COUNTIF(B$1:B1,A$1:A$20),0) ),"") As for the general question of the n_th distinct value in a list (LST), =INDEX(LST,SMALL(IF(MATCH(LST,LST,0)=ROW(LST)-ROW(INDEX(LST,1,1))+1, MATCH(LST,LST,0)),n)) also an array formula. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check COlumn - Excel VBA | Excel Discussion (Misc queries) | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
How to group similar column titles together???? | Excel Discussion (Misc queries) | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |