Lookup Function

[Ademar]

I used either the vlookup, hlookup or lookup function a long time ago but I
not sure how I used it.

I need to compare the contents found on column B with the contents found on
column A to see if there are duplicates

If the value on B2 is found anywhere on A1:A5 I need it to tell me that
there is a duplicate.

Can you help?
Thanks


Ademar Nunes

 

[Biff]

Hi!

There are many ways to do this. Here are a few:

Entered as an array, will return TRUE for duplicate, FALSE
for no duplicate:

=OR(A1:A5=B2)

Also entered as an array:

=IF(OR(A1:A5=B2),"duplicate","")

Entered normally:

=IF(ISNUMBER(INDEX(A1:A5,MATCH
(B2,A1:A5,0))),"duplicate","")

Biff

 

[David McRitchie]

you might also take a look at Chip Pearson's page
Duplicates
http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates

 

[Ademar]

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?

 

[Aladin Akyurek]

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.99999999999999E+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)),"")

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. Th
formula
in ColumnC would lookup columnA and return only distinct values int
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

[...

 

[Ademar]

This is a bit advance for me and I don't quite understand. What formula do
I copy in columns C and D?

Thanks Aladin,

Ademar Nunes

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:

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)),
"")

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

[...]

 

[Ademar]

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

--
Regards,

Ademar Nunes

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:

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)),
"")

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]

Change the formula in C3 to:

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

I plugged in the formula but it is not working properly. It kind o
works.

--
Regards,

Ademar Nunes

"Aladin Akyurek" <[email protected]> wrot
in
message news:[email protected]...

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:

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)),
"")

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 value into
Column
C, thus ignoring duplicates. The formula in CulumnD would retur the
corresponding age, found in Column B for that distinct value.

Can you help again?
--
Thanks,

Ademar Nunes

[...]

http://www.excelforum.com/showthread.php?threadid=274755