# Circles with If Statements

#### Martino

##### New Member
I've tried solving this using nested If's but am going round in circles at the moment. I have a table of data thus..

Column A Col B Col C Col D
N21500058-801 65 MCR WIP
N21500058-801 67 MCR WIP
N21500058-801 72 MCR WIP
N21700043-801 6 MCR WIP
N25000007-801 52 CR HELD
N25000007-801 53 MCR HELD
N25000007-801 56 CR HELD
N25000007-801 57 MCR HELD
N25000007-801 65 MCR HELD
N25000009-801 58 MCR WIP
N25000009-801 59 MCR WIP

Column D above is a manual input and I need to automate it due to the size of the file.
Column D is what I want to end up with based on whether Column A Has changed and whether Column C has Changed.
If col A changes and Col C is CR, Col D = Held
Thereafter if col A does not change and Col C is MCR or CR, COl D = Held.
If Col A does change and Col C is MCR then Col D is WIP.

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
starting row 2

if(and(a2<>a1,c2="cr"),"held",if(a2=a1,"held",if(and(a2<>a1,c2="mcr"),"false","error")))

sorry instead of fasle I meant "WIP"

Almost but not quite...if I enter the formula in d2 it sets d2 as held also lower down in d11, both should be WIP

Why should D2 be "WIP"? A2=A1 and C2=MCR. Your requirements would seem to indicate that this should be "HELD". Your requirements seem to reduce to:

If column A changes and column C is MCR, then "WIP", otherwise always use "HELD". Is there another piece to the puzzle?

Seti

"Thereafter if col A does not change and Col C is MCR or CR, COl D = Held"

D2 should be "held" because A has not changed from the previous row

The formula I gave you is still wrong though order and should read :

if(and(a2<>a1,c2="cr"),"held",if(and(a2<>a1,c2="mcr"),"WIP","held"))

I don't think I explained this clearly. The key (I Think)is col c.
If we assume that col A is the same for the first 5 rows. Col C for rows 1&2 is MCR. Col D therefore should be WIP.
If the Col C changes to CR in row 3 Col D changes to Held for rows 3,4,5 regardless of what might be in col C rows 4,5.
If Col A then changes the process starts again, looking at col c. Like so

col A Col B Col C Col D
98 1 MCR WIP
99 1 MCR WIP
100 1 MCR WIP
100 1 CR HELD
100 1 MCR HELD
100 1 MCR HELD
100 1 MCR HELD
101 1 MCR WIP
102 1 MCR WIP
102 1 CR HELD
102 1 MCR HELD

Hope that makes things clearer

Martino,

To preserve the spacing in your table (so everything lines up, put <pre> on the line before you table, and put </pre> on the line after your table... this will preserve your format.

<pre>
A B C D
98 1 MCR WIP
99 1 MCR WIP
100 1 CR Held
100 1 MCR Held
100 1 MCR Held
101 1 MCR WIP
102 1 CR Held
102 1 MCR Held
103 1 MCR WIP
/<Pre>
Hope this works

Edit your last post so instead of the last line being /<pre> it is </pre>.

Thanks

Replies
0
Views
267
Replies
3
Views
94
Replies
5
Views
129
Replies
3
Views
87
Replies
4
Views
599

1,219,574
Messages
6,149,082
Members
450,855
Latest member
onecodevee01

### 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.

### Which adblocker are you using?

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

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