| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching values that are not in order with conditional formatting
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching values that are not in order with conditional formatt
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching values that are not in order with conditional formatt
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching values that are not in order with conditional formatt
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional formatting...cont. from 9/25 | Excel Discussion (Misc queries) | |||
| Conditional Formatting Cell < Today() | Excel Worksheet Functions | |||
| Formula for Searching & matching two values in excel | Excel Discussion (Misc queries) | |||
| Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
| How do I use conditional formatting for multiple rows? | Excel Worksheet Functions | |||
Linear Mode
