If(And formula yields three values - shouldn't it be two?

pam5cashfan

New Member
Joined
Dec 31, 2018
Messages
5
First time posting...advanced beginner level skills. Currently working on data set of 5000 lines/month and I'll need to aggregate at least 4 months in each file.

Here's what i'm trying to flag with as simple a formula as possible:

If there is no change in column A in consecutive rows, and there is a change in column B in consecutive rows - that's what I want to know.

The data in these columns is in text.

I'm getting three outputs - FALSE, TRUE, and what I thought I had proposed as value-if-true "Switch"."Switch" appears when the first condition has not been met (A2<>A3), but I want it to appear when both conditions have been met (A2=A3, B2<>B3)

How can three outputs exist? Should I be using nested If's instead of If(And ?

Thanks.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Maybe use OR instead of AND?
Excel Workbook
ABC
1Test1M1
2Test1M1 
3Test1M1
4Test2M2Switch
5Test3M2Switch
6Test4M4Switch
7Test4M4
Sheet





<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"][/TD]

[TD="bgcolor: #ffff00"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"][/TD]

[TD="bgcolor: #ffff00"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"][/TD]

[TD="bgcolor: #ffff00"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"][/TD]

[TD="bgcolor: #ffff00"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"][/TD]

[TD="bgcolor: #ffff00"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"][/TD]

[TD="bgcolor: #ffff00"][/TD]
L 4
 
Last edited:
Upvote 0
My take on this


Excel 2013/2016
ABC
1Test1M1
2Test1M1 
3Test1M2switch
4Test2M2
5Test3M2
6Test4M4
7Test4M4
8Test4M5switch
9Test4M4switch
10Test4M4
B
Cell Formulas
RangeFormula
C2=IF(AND(A2=A1,B2<>B1),"switch","")
 
Upvote 0
Thank you!
The change to IF(OR eliminated the TRUE result, which was interesting.
Now that I have the MrExcel HTML Generator, it'll be way easier to elaborate on my question.

I want to flag only those rows (as "Switch") where A1=A2 but B changes. I don't at this moment need to know when A changes.

Like this


Book1
ABCD
1RowUniqueIDColorDesiredOutput
21TEMP001Blue
32TEMP001Blue
43TEMP001GreenSwitch
54TEMP002Yellow
65TEMP002Yellow
76TEMP003Yellow
87TEMP003RedSwitch
98TEMP003GreenSwitch
109TEMP003RedSwitch
Sheet1


Thanks.
 
Upvote 0
In B3 copied down
=IF(AND(A3=A2,B3<>B2),"switch","")
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top