Changing data based on two conditions

M

Mebs

Board Regular
Joined
Mar 16, 2009
Messages
51
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)</SPAN>
Project Number</SPAN>Revision</SPAN>Status</SPAN>
1</SPAN>1</SPAN>Unapproved</SPAN>
1</SPAN>2</SPAN>Unapproved</SPAN>
1</SPAN>3</SPAN>Unapproved</SPAN>
1</SPAN>4</SPAN>Unapproved</SPAN>
1</SPAN>5</SPAN>Unapproved</SPAN>
2</SPAN>1</SPAN>Unapproved</SPAN>
2</SPAN>2</SPAN>Unapproved</SPAN>
2</SPAN>3</SPAN>Unapproved</SPAN>
2</SPAN>4</SPAN>Unapproved</SPAN>
3</SPAN>1</SPAN>Approved</SPAN>
3</SPAN>2</SPAN>Approved</SPAN>
3</SPAN>3</SPAN>Approved</SPAN>
3</SPAN>4</SPAN>Approved</SPAN>
4</SPAN>1</SPAN>Rejected</SPAN>
4</SPAN>2</SPAN>Rejected</SPAN>
4</SPAN>3</SPAN>Rejected</SPAN>
4</SPAN>4</SPAN>Rejected</SPAN>
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)</SPAN>
Project Number</SPAN>Revision</SPAN>Status</SPAN>
1</SPAN>5</SPAN>Unapproved</SPAN>
2</SPAN>4</SPAN>Unapproved</SPAN>
3</SPAN>1</SPAN>Approved</SPAN>
3</SPAN>2</SPAN>Approved</SPAN>
3</SPAN>3</SPAN>Approved</SPAN>
3</SPAN>4</SPAN>Approved</SPAN>
4</SPAN>4</SPAN>Rejected</SPAN>
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;</SPAN>
Project Number</SPAN>Revision</SPAN>Status</SPAN>New Ststus</SPAN>
1</SPAN>1</SPAN>Unapproved</SPAN>Approved</SPAN>
1</SPAN>2</SPAN>Unapproved</SPAN>Approved</SPAN>
1</SPAN>3</SPAN>Unapproved</SPAN>Approved</SPAN>
1</SPAN>4</SPAN>Unapproved</SPAN>Approved</SPAN>
1</SPAN>5</SPAN>Unapproved</SPAN>Unapproved</SPAN>
2</SPAN>1</SPAN>Unapproved</SPAN>Approved</SPAN>
2</SPAN>2</SPAN>Unapproved</SPAN>Approved</SPAN>
2</SPAN>3</SPAN>Unapproved</SPAN>Approved</SPAN>
2</SPAN>4</SPAN>Unapproved</SPAN>Unapproved</SPAN>
3</SPAN>1</SPAN>Approved</SPAN>Approved</SPAN>
3</SPAN>2</SPAN>Approved</SPAN>Approved</SPAN>
3</SPAN>3</SPAN>Approved</SPAN>Approved</SPAN>
3</SPAN>4</SPAN>Approved</SPAN>Approved</SPAN>
4</SPAN>1</SPAN>Rejected</SPAN>Approved</SPAN>
4</SPAN>2</SPAN>Rejected</SPAN>Approved</SPAN>
4</SPAN>3</SPAN>Rejected</SPAN>Approved</SPAN>
4</SPAN>4</SPAN>Rejected</SPAN>Rejected</SPAN>
Hope this makes sense and someone can help, thanks.</SPAN>

<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
Sort by date Sort by votes
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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</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))}

<thead>
</thead><tbody>
</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:
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
228
Micron
M
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
583
gvsumich
G
B
  • Question
Count number of rows where a dynamically growing set of columns has the specified value
Replies
0
Views
248
Bries
B
M
  • Question
Help with FilterXML formula
Replies
0
Views
936
montex01
M
2
  • Question
VBA Copy Data from Sheet to Email depending on Assignee
Replies
2
Views
222
2000km12
2

Forum statistics

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

Share this page

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