matching values that are not in order with conditional formatting

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>> >
>>
>>
>>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >
> >> >
> >>
> >>
> >>
>
>
>