CODEWITHSUNDEEP
excelexcel-formulacomparesubstringcell
Annelies
Annelies

Reputation: 47

Change excel formula so that it looks into another row when there is a "1" in the adjacent column

I have the following formula in column Z, that looks into two adjacent rows in column D to find if the number belonging to the word "Dot" is the the same or different (for example both may be Dot7, so are the same)

=IF(MID(D117;FIND("Dot";D117)+3;LEN(D117)-FIND("Dot";D117)-2)=MID(D116;FIND("Dot";D116)+3;LEN(D116)-FIND("Dot";D116)-2);"Same";"Different")

The problem I have is that in some cases, there is an inbetween row containing "115" instead of Dot with a number, in this specific case, D116 contains Dot7, while D117 contains "115", so this row should be skipped and I want to look at the value in D118 instead (which is also Dot7, so they are "same"). It looks like this:

enter image description here

To try to solve this, I have created a helper formula in column Y that looks for "115" in the row below the first row of interest, and returns a "1" if that is the case. This formula looks as follows:

=IF(ISNUMBER(FIND("115"; D117)); 1; 0)

Now I want to update my initial formula so that it takes this information into account. In other words, if column Y (here Y116) contains a "1" it should skip the adjacent row (D117 in this case) and instead look in the next row (D118 in this case). I have no idea how to do that! ChatGPT suggested the following formula, but it is not working as it gives an empty output:

=IF(
AND(Y116=1; MID(D118; FIND("Dot"; D118) + 3; LEN(D118) - FIND("Dot"; D118) - 2) = MID(D116; FIND("Dot"; D116) + 3; LEN(D116) - FIND("Dot"; D116) - 2));
"Same";
IF(
    MID(D117; FIND("Dot"; D117) + 3; LEN(D117) - FIND("Dot"; D117) - 2) = MID(D116; FIND("Dot"; D116) + 3; LEN(D116) - FIND("Dot"; D116) - 2);
    "Same";
    "Different"
))

I have a Mac with excel 2016. I would really love to finally solve this! Thanks for any input.

Upvotes: 1

Views: 37

Answers (1)

Black cat
Black cat

Reputation: 6107

Enough to test row 117 for Number, and based on that compare 118-116, or 117-116

=IF(ISNUMBER(D117);IF(MID(D118;FIND("Dot";D118)+3;LEN(D118)-FIND("Dot";D118)-2)=MID(D116;FIND("Dot";D116)+3;LEN(D116)-FIND("Dot";D116)-2);"Same";"Different");IF(MID(D117;FIND("Dot";D117)+3;LEN(D117)-FIND("Dot";D117)-2)=MID(D116;FIND("Dot";D116)+3;LEN(D116)-FIND("Dot";D116)-2);"Same";"Different"))

Upvotes: 1

Related Questions