Changing data based on two conditions

Mebs

Board Regular
 I need to change a cell based on two cells. In cell A1 = Project Number, cell B1 = Revision and cell C1 = Status (row 2 onwards contains the data) Project Number Revision Status 1 1 Unapproved 1 2 Unapproved 1 3 Unapproved 1 4 Unapproved 1 5 Unapproved 2 1 Unapproved 2 2 Unapproved 2 3 Unapproved 2 4 Unapproved 3 1 Approved 3 2 Approved 3 3 Approved 3 4 Approved 4 1 Rejected 4 2 Rejected 4 3 Rejected 4 4 Rejected The issue is that I know that only the last revision for the project has the correct Status unless they are approved. So in this above sample, the table below is correct; (All the previous revision, would have to be approved) Project Number Revision Status 1 5 Unapproved 2 4 Unapproved 3 1 Approved 3 2 Approved 3 3 Approved 3 4 Approved 4 4 Rejected What I need to do is Cell D1 = New status and put a formula that changes them. The logic is that all preceeding one must have been approaved and only the last project number & revision combination must remain as is. So based on the sample data, the new Status would look like; Project Number Revision Status New Ststus 1 1 Unapproved Approved 1 2 Unapproved Approved 1 3 Unapproved Approved 1 4 Unapproved Approved 1 5 Unapproved Unapproved 2 1 Unapproved Approved 2 2 Unapproved Approved 2 3 Unapproved Approved 2 4 Unapproved Unapproved 3 1 Approved Approved 3 2 Approved Approved 3 3 Approved Approved 3 4 Approved Approved 4 1 Rejected Approved 4 2 Rejected Approved 4 3 Rejected Approved 4 4 Rejected Rejected Hope this makes sense and someone can help, thanks.

<TBODY>
</TBODY><COLGROUP><COL span=5><COL span=2><COL span=6></COLGROUP>
.​

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Just realized you can use this in cell D2: =IF(A2<>A3,C2,"Unapproved"). I had been working with SMALL so much lately that it was at the top of my thinking, hence below in my first reply.

Hello Mebs, I hope this is still needed. One way to do it is the following assuming Project Number is sorted low to high:

Excel 2010
ABCD
1Project NumberRevisionStatus
211ApprovedUnapproved
312RejectedUnapproved
413UnapprovedUnapproved
514ApprovedUnapproved
615RejectedRejected
721RejectedUnapproved
822ApprovedUnapproved
923RejectedUnapproved
1024ApprovedApproved
1131ApprovedUnapproved
1232ApprovedUnapproved
1333ApprovedUnapproved
1434ApprovedApproved
1541RejectedUnapproved
1642RejectedUnapproved
1743RejectedUnapproved
1844RejectedRejected

</tbody>
Sheet1

Array Formulas
CellFormula
D2{=IF(A2=A3,"Unapproved",INDEX(\$C\$2:\$C\$18,SMALL(IF(\$A\$2:\$A\$18=A2,ROW(\$C\$2:\$C\$18)),COUNTIF(\$A\$2:\$A\$18,A2))-1))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Luke

Last edited:
Although the logic for saying the older ones must have been approved seems a bit shaky to me:
Ensure the data is sorted by Project # & Revision, use the following formula in D2
=IF(A2=A3,"Approved",C2)
and copy it down replicates the results you showed for column D

Ah, I had it backwards. Pbornemeier is right.

Thanks to both of you. It was a simple solution afterall.

Replies
1
Views
228
Replies
0
Views
583
Replies
0
Views
248
Replies
0
Views
936
Replies
2
Views
222

1,207,166
Messages
6,076,902
Members
446,239
Latest member
Home Nest

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.

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