Labeling all Cells of a Corresponding Range that has Equal Values Based on a Criterion

[hassanu]

Hello,

Label	Number	Status
incomplete	1	not done
complete	2	not done
complete	2	done
complete	2	not done
incomplete	3	not done
incomplete	3	not done
complete	4	done
complete	5	not done
complete	5	not done
complete	5	done
complete	5	not done

<tbody>
</tbody>

​

What I want to do is look at the Status column (column E), and if any cell says "done" then the corresponding Label cell (column A) and all other Label cells (column A) of the same Number (column B) should say "complete". Please refer to the data that I have provided above. The first "done" in the Status column occurs for the number 2. Therefore, all labels for the number 2 should say "complete" as shown. Similarly, one cell for the number 5 says "done" and so all labels for the number 5 say "complete". And if no corresponding cells for a particular number say "done", then the label for all of those numbers is "incomplete". I want to be able to do this either through excel formulas or VBA code. I would not like to use any advanced filters or conditional formatting. Any help would be greatly appreciated!

 

[njimack]

Excel Workbook
	A	B	C
1	Label	Number	Status
2	complete	1	not done
3	incomplete	2	not done
4	incomplete	2	done
5	incomplete	2	not done
6	complete	3	not done
7	complete	3	not done
8	incomplete	4	done
9	incomplete	5	not done
10	incomplete	5	not done
11	incomplete	5	done
12	incomplete	5	not done
Sheet3

 

[hassanu]

This is not correct though. Numbers 2,4, and 5 should say "complete" since there is at least one "done" entry for those numbers. Also, you provided a specific range of B2:B12 in your formula. I want to be able to run the formula throughout the whole column until the last row of data, which I would need to determine since I am going to be running this macro on multiple spreadsheets. So a macro to take care of all this would probably be a better solution. Also, formulas such as the SUMPRODUCT and COUNTIF are not desirbale since I sometimes have hundreds of thousands of rows of data and these formulas would take a very long time to run on such workbooks.

 

[njimack]

You're right, there was a typo in my formula. The correct function is =IF(SUMPRODUCT(--($B$2:$B$12=B2),--($C$2:$C$12="done")),"","in")&"complete".

The range issue can easily be resolved by either using dynamic ranges or tables. If you would prefer VBA, how will this be triggered - attached to a command button or everytime the sheet is changed/calculated?

 

[Mebs]

I have a similar question, where 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;

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 Status

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.

 

[hassanu]

I appreciate the help. Using VBA, I would want something like: For each unique range of numbers in column B, if at least one of the corresponding statuses says "done" then label all of the statuses as "complete" in a different column (column A in this case). So for the data that I provided, the macro would look at each set of unique numbers and come up with a label for all of them. It would see that the number 1 only has one status that is "not done" and so it puts "incomplete" in column A, and then it would go to the number 2 and see that there are 3 statuses, and at least one status says "done" so all labels for the number 2 should say "complete".

For the range issue, I just want to be able to define the range based on the number of rows of data since it changes when the data is updated, so maybe something with Activesheet.usedrange.rows.count that is embedded in the counter of the for loop.