View Full Version : How to find the last duplicate value

LOSS1574

06-19-2008, 08:44 AM

What formula should I use based on the example below?

I want to find the last duplicate value/or single value in the part column and bring over the adjacent column value as well.

9148

Thank you,

Simon Lloyd

06-19-2008, 08:52 AM

In your example why do you have FIND next to the cells? and find the value where?, you mention duplicates but in your example there are none that have the same part name e.g Value F is 5 there is another 5 but it belongs to Value G. You need to explain further what you are trying to achieve and what the expected outcome is and where!

LOSS1574

06-19-2008, 10:19 AM

I'm not sure of the proper way to describe what I need. However, I'll give it another shot.

Name(Column A) Accounts(Column B)

MUM 5

MUM 25

MUM 15 ---------- this is a line I need to find

Shand 7

Shand 2 ---------- Another line I need to find

Morhand 6---------- Another line I need to find

(Keep in mind the Numbers next to each Name(Column A) are in a separate column B)

I'm trying to create a formula(or any other method) in column C to find the last duplicate value or single value in colum A and move that value along with the adjacent cell (Column B) to Column C.

So using the above method Column C would read:

MUM 15

Shand 2

Morhand 6

The other values would be elminated.

I'm probably still not making any sense, however any help would be greatly appreciated :)

Simon Lloyd

06-19-2008, 10:23 AM

What criteria are you using to find Mum 15 instead of Mum 5? is the name in one column and the number in another? are they always in order i.e all the Mum's all the Shand's etc?

Bob Phillips

06-19-2008, 10:38 AM

G1: =A1

G2: =IF(ISERROR(MATCH(0,COUNTIF(G$1:GB1,$A$1:$A$200&""),0)),"",

INDEX(IF(ISBLANK($A:$A),"",$A$1:$A$200),MATCH(0,COUNTIF(G$1:G1,$A$1:$A$200&""),0)))

G2 is an array formula, copy down as afra as you think you might need

H1: =IF(G1="","",INDEX(B:B,MAX(IF(A1:A20=G1,ROW(A1:A20)))))

also an array formula, and copy down as far as the G column

Simon Lloyd

06-19-2008, 10:44 AM

As always Bob :jawdown:

LOSS1574

06-19-2008, 11:21 AM

Thank you both so much!!!!!

Amazing!:beerchug: :bow:

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.