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

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.