Change the formula in C3 to:

=IF((A3<"")*ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.99 999999999999E+307,$C$1:C2)+1,"")

Ademar Wrote:
I plugged in the formula but it is not working properly. It kind of
works.

--
Regards,

Ademar Nunes

"Aladin Akyurek" wrote
in
message ...

Let A2:B7 house the sample you provided:

{"Name","Age";"Tom",30;"Jerry",25;"Jim",17;"Jerry" ,25;"Jim",17}

C1 must house a 0.

C2: Count

C3, copied down:


=IF((A3<"")*ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.99 999999999999E+307,$C$1:C1)
+1,"")

D1:

=LOOKUP(9.99999999999999E+307,$C$3:$C$7)

which calculates the number of unique records.

D2: D-Name

E3: Age

D3, copied across then down:


=IF(ROW()-ROW(D$3)+1<=$D$1,INDEX(A$3:A$7,MATCH(ROW()-ROW(D$3)+1,$C$3:$C$7)),
"")

Ademar Wrote:
Thanks guys, those solutions work great.
It would be even better if we could do this:

ColumnA ColumnB ColumnC ColumnD
Tom 30 Jerry 25
Jerry 25 Jim 17
Jim 17
Jerry 25
Jim 17

Columns C and D are the columns where I'd plug in the formulas.
The
formula
in ColumnC would lookup columnA and return only distinct values
into
Column
C, thus ignoring duplicates. The formula in CulumnD would return
the
corresponding age, found in Column B for that distinct value.

Can you help again?
--
Thanks,

Ademar Nunes

[...]


--
Aladin Akyurek

------------------------------------------------------------------------
Aladin Akyurek's Profile:
http://www.excelforum.com/member.php...fo&userid=4165
View this thread:
http://www.excelforum.com/showthread...hreadid=274755



--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274755