Circles with If Statements

Martino

New Member
Joined
Sep 11, 2002
Messages
12
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
starting row 2

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

Martino

New Member
Joined
Sep 11, 2002
Messages
12
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
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916

ADVERTISEMENT

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
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
well per your conditions:

"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"))
 

Martino

New Member
Joined
Sep 11, 2002
Messages
12

ADVERTISEMENT

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
 

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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.
 

Martino

New Member
Joined
Sep 11, 2002
Messages
12
<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
 

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
Edit your last post so instead of the last line being /<pre> it is </pre>.

Thanks
 

Forum statistics

Threads
1,144,117
Messages
5,722,581
Members
422,447
Latest member
knopp

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
Top