PDA

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: