Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deifine range ignoring duplicates
In Define Name in Excel 2007 I want to create a formula for A:A that excludes
duplicates in the range. How do you create this formula. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deifine range ignoring duplicates
You could use a column of formulas that would extract and gather together the unique values from
column A, and reference those values using a dynamic named range. HTH, Bernie MS Excel MVP "Gotroots" wrote in message ... In Define Name in Excel 2007 I want to create a formula for A:A that excludes duplicates in the range. How do you create this formula. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deifine range ignoring duplicates
Put this in one column over:
=IF(COUNTIF($A$1:A1,A1)=1,A1,"") Put this one column over from that; enter with ctrl+shift+enter: =IF(ROW()-ROW($C$1:$C$20)+1ROWS(B1:B20)-COUNTBLANK(B1:B20),"",INDIRECT(ADDRESS(SMALL((IF(B 1:B20<"",ROW(B1:B20),ROW()+ROWS(B1:B20))),ROW()-ROW($C$1:$C$20)+1),COLUMN(B1:B20),4))) HTH, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Bernie Deitrick" wrote: You could use a column of formulas that would extract and gather together the unique values from column A, and reference those values using a dynamic named range. HTH, Bernie MS Excel MVP "Gotroots" wrote in message ... In Define Name in Excel 2007 I want to create a formula for A:A that excludes duplicates in the range. How do you create this formula. . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deifine range ignoring duplicates
Hi
Tested out your solution and here is how i got on: =IF(COUNTIF($A$1:A1,A1)=1,A1,"") this formula works by removing the duplicates =IF(ROW()-ROW($C$1:$C$20)+1ROWS(B1:B20)-COUNTBLANK(B1:B20),"",INDIRECT(ADDRESS(SMALL((IF(B 1:B20<"",ROW(B1:B20),ROW()+ROWS(B1:B20))),ROW()-ROW($C$1:$C$20)+1),COLUMN(B1:B20),4))) Some records were not returned with this formula, don't know why. "ryguy7272" wrote: Put this in one column over: =IF(COUNTIF($A$1:A1,A1)=1,A1,"") Put this one column over from that; enter with ctrl+shift+enter: =IF(ROW()-ROW($C$1:$C$20)+1ROWS(B1:B20)-COUNTBLANK(B1:B20),"",INDIRECT(ADDRESS(SMALL((IF(B 1:B20<"",ROW(B1:B20),ROW()+ROWS(B1:B20))),ROW()-ROW($C$1:$C$20)+1),COLUMN(B1:B20),4))) HTH, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Bernie Deitrick" wrote: You could use a column of formulas that would extract and gather together the unique values from column A, and reference those values using a dynamic named range. HTH, Bernie MS Excel MVP "Gotroots" wrote in message ... In Define Name in Excel 2007 I want to create a formula for A:A that excludes duplicates in the range. How do you create this formula. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting values ignoring duplicates | Excel Worksheet Functions | |||
Count, ignoring duplicates | Excel Worksheet Functions | |||
Filtering out duplicates while ignoring 1 row | Excel Discussion (Misc queries) | |||
Ignoring #N/A in an Autosum range | Excel Worksheet Functions | |||
Counting Names in a Column, Ignoring Duplicates | Excel Discussion (Misc queries) |