1

Can anybody help me to correct this data? There is a long list of this type of data:

enter image description here

  • If there are columns that are exactly matched, like in row 5 (Harley- harley) it should return 1 or 'true' in column C.
  • The columns which are not exactly matched, like in Row 3 (Kerry Sean-Kelvin), it should return 0 or 'false' in column C,
  • Where there is little mismatching between two columns as in rows 1, 2, 4, and 6, it should return -1 or 'Mismatched'.
4
  • You'll need to specify what "little mismatching" means. For example, you could say that a two letter match in the beginning or the end qualifies as a "little mismatch", but you have to be specific in order to write an excel formula to test if the condition is met.
    – Atzmon
    May 29, 2016 at 6:41
  • Little mismatch means if there are one or more than one characters mismatched like in row 1 Jessica-Jassica,at row no 6 Sonica is written as Onica.
    – Japinder
    May 29, 2016 at 7:11
  • Or leave this part,i'l be highly obliged if u could solve first two points.
    – Japinder
    May 29, 2016 at 7:27
  • Check my answer below, see if it helps.
    – Atzmon
    May 29, 2016 at 8:32

3 Answers 3

1

You can use this formula:

=IF(A1=B1, "1",IF(B1=LEFT(A1,LEN(B1)), "-1", IF(B2=RIGHT(A2,LEN(B2)), "-1", "0")))

As for "little mismatches", it will only detect cases where B1 is either the prefix or suffix of A1 (so "Sonica" and "onica" will produce "-1").

enter image description here

Detecting a mismatch by one character anywhere within A1 (like "Jessica" vs "Jassica") is more complex and would require some programming.

4
  • First of all Thanku Atzmon, See how this formula works: John Panesar John 0 Kerry Sean Kelvin -1 Michael Messy Mi 0 Harley harley -1 Sonica onica 1 It returns -1 for both harley-Harley and Kerry-Kelvin..??? and 1 for Sonica-onica....???
    – Japinder
    May 29, 2016 at 17:42
  • Added a screenshot to make sure we're referring to the same results.
    – Atzmon
    May 29, 2016 at 17:43
  • O yess ..Got it...Thanx a lot Atmzon..It's very helpful.....please do favour me to solve the last point..Thanku again...
    – Japinder
    May 29, 2016 at 17:51
  • That's beyond the scope of this SupeUser answer (being more of a help site that a free code writing site). What you need is some fuzzy logic matching programmed in VBA. Take a look at some implementations of the Levenshtein Distance algorithm in VBA over at StackOverflow.
    – Atzmon
    May 29, 2016 at 20:42
0

For the first one, you can use the IF function to compare strings and get a results as True or False.

Syntax: IF(A1=B1,"True","False")

If you want to match cases, you can use EXACT function. It checks for case and other characters.

You have to combines these 2 with other string based functions to obtain the results. As your cases are not exactly static.

1
  • thanx , but exact function does not work for huge data. i need macro for correcting it.
    – Japinder
    May 29, 2016 at 7:14
0

You have to decide for the number of characters that match, I used 3 as limit, if 3 characters matches the result will be -1 if less 0 if all match 1, the formula in C1 and press Shift + Ctrl + Enter array Formula and you can drag it:

=IF(ISNA(IF(A1=B1,1,IF(SUM(MATCH(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0))>3,-1,0))),0,IF(A1=B1,1,IF(SUM(MATCH(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0))>3,-1,0)))

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .