thanks, i'll give it a go...
the confusion is mutual...

"Biff" wrote:

Ok, I'm a little confused about your layout:

in the example below c1 and c2 come from one list, and c3
and c4 from another. they are not in the same order however.
c1 c2 c3 c4
DK GHB1234 DK GHB1234
GB DAK7654 LM FDE4587
LM SDE4587 GD DAK7654
AS LKS6532 AF LJS6532

If you have 2 columns:

..................C............................... ..D.........................E
1.....DK GHB1234............. DK GHB1234..........formula
2.....GB DAK7654............. LM FDE4587...........
3.....LM SDE4587.............. GD DAK7654.........
3.....AS LKS6532.............. AF LJS6532............

I was hoping to return the position of the match.

Formula in E1 entered as an array using the key combination of
CTRL,SHIFT,ENTER:

=MATCH(RIGHT(D1,4),RIGHT(C$1:C$4,4),0)

Copy down as needed.

Biff

"TBA" wrote in message
...
Hi Biff
I think that using the digit portion is ok, it is really about reducing
the
amount of manual checking i need to do, which at the end of the day i know
some will be necessary. so suggest away on that basis?

cheers Theo

"Biff" wrote:

Hi!

Can you use the 4 digit portion of the ID as a match criteria and, are
they
unique except that there will be pairs?

If not, then I don't think this can be done. For example, which of these
are
matches:

DK GHB1234 AS GHB1234
AS LKS1234 AF LJS1234

2 of them match the first initial set and the 4 digits
2 of them match the second initial set and the 4 digits
all 4 of them match the 4 digits

Biff

"TBA" wrote in message
...
Tricky one- I need to compare two lists to determine where an incorrect
letter or number has been entered.
each casehas a set of two intials and an id number comprising a 3
letter
and
four digit number. in the example below c1 and c2 come from one list,
and
c3
and c4 from another. they are not in the same order however.

c1 c2 c3 c4
DK GHB1234 DK GHB1234
GB DAK7654 LM FDE4587
LM SDE4587 GD DAK7654
AS LKS6532 AF LJS6532

this example aboves shows the four most common outcomes.
row 1= row 1 both values match, all good
row 2= row 3 initials don't match, but id number does
row 3=row 2 initials match, one letter in id doesn't
row 4 = row 4 both initials and id don't match, but they are close.

I would prefer not to order the columns, but could do so I guess.
I was hoping to return the position of the match.
sorry about the confusing instructions, I hope that someone might have
a
pointer or two.
there are around 400 to test

cheers