Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique Data List (w/ header)
Hello,
I am writing with a question regarding the Unique Data formula method that Hager, Umlas and Longre created- I discovered it at http://www.dailydoseofexcel.com/arch...r-unique-data/ .. As I mention in my comments, I am having a surprising amount of trouble trying to adjust this method to account for a header row. Would you mind explaining how you would go about it? Best, Eugene |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique Data List (w/ header)
On Dec 4, 9:15 am, wrote:
Hello, I am writing with a question regarding the Unique Data formula method that Hager, Umlas and Longre created- I discovered it athttp://www.dailydoseofexcel.com/archives/2006/12/27/power-formulas-fo... . As I mention in my comments, I am having a surprising amount of trouble trying to adjust this method to account for a header row. Would you mind explaining how you would go about it? Best, Eugene To clarify, I have been attempting to adjust the following formulas for a header in the top cell. It sounds like a menial task, but I am having a great deal of trouble figuring it out: TheList = OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),) sArray = SMALL(IF(MATCH(TheList,TheList, 0)=ROW(TheList),ROW(TheList),""),ROW(INDIRECT("1:" &SUM(N(MATCH(TheList,TheList, 0)=ROW(TheList))))))-1 Unique = =F(T(OFFSET(TheList,sArray,,1))="",N(OFFSET(TheLis t,sArray,, 1)),T(OFFSET(TheList,sArray,,1))) This method works when TheList refers to the top cell ($A$1), but not otherwise. I would like to define: TheList = OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1! $A:$A)-1,) , but the rest breaks down. Any help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return unique data from a list | Excel Worksheet Functions | |||
DataValidationList - Unique Entries | New Users to Excel | |||
How can I average data from a repeating list into a unique list? | Excel Worksheet Functions | |||
How can I average data from a repeating list into a unique list? | Excel Worksheet Functions | |||
Data Validation using List (But needs unique list in drop down lis | New Users to Excel |