Unique Data List (w/ header)

  • Thread starter Thread starter gokhvat
  • Start date Start date
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
Click to expand...

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(TheList,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.
 
Back
Top