How do I identify the next personal who needs to do a task in excel?

Copper Contributor

Hey y'all,

 

Please reference the picture below! I have this Excel where people will be filling in a completed task and I need to identify the next person to do the task. I had a few ways that I thought of doing this, but I cannot find any instructions for how I might accomplish them. This may be a tough one and understand if there is just no way to do it. I am happy with any solutions that someone might be able to provide via formulas, formats, or Macros/VBA. I am not experienced with Macros/VBA, but if someone has a solution through Macros/VBA and can lay out instructions in detail, I can definitely try. Below are some options for results that I would potentially want (in order of the most desired result), but if someone has a different idea that gives me what I am looking for, I am all ears! Thank you!

 

My first thought was to figure out a way to have Excel automatically move down any row that has someone who completed the task. For example, (please ignore the highlighted row for now) rows 1-14, 17, and 20-21 are completed and I want those to automatically go elsewhere so that people who haven't completed the task can move up to the top. I wouldn't want those rows to be deleted; just moved so that it is easier to bring the next person to the top. And I want this to be automatic as I can't constantly go in and sort every time someone completes a task and do not want others attempting to sort themselves. Through searching everywhere on Google, I realize now that that might be easier said than done.

 

My second thought was to format it so that the next person and their row that needs to complete the task is highlighted in some way. For example, only row 15 would be highlighted. But the only way that I found highlights all blanks which I do not want to do. I only want the next person's row to be highlighted. I also cannot do any formats or formulas that are triggered by certain cell text. We have people come and go and it cannot be dependent on a name, date, etc. triggering the highlight in the next row.

 

My last thought was some kind of cell somewhere that auto-populates the next name for the task. For example, Eliza in Row 15 is the next person who needs to complete the task and her name would auto-populate in G2.

 

Ok, I know this was a lot and I don't know if any of these are achievable, but if they are please let me know! Open to all suggestions! Thank you!

 

lexcel_1206_0-1698416904038.png

 

3 Replies

@lexcel_1206 

=LET(result,SORTBY(A2:E18,SCAN(0,SEQUENCE(ROWS(A2:A18)),LAMBDA(ini,arr,IF(ISNUMBER(INDEX(D2:D18,arr))+ISTEXT(INDEX(E2:E18,arr)),ini+1,0)))),IF(result=0,"",result))

 

With Office 365 or Excel for the web you can try this formula which numbers the table in columns A - E depending on if there is a date in column D or a text in column E and then sorts the table in ascending order of the numbering. In column F you can see the result of the numbering. The result is the spilled range in columns H to L which dynamically updates according to the changes in the original table.

sort table.png

@OliverScheurich 

 

I just tried it but it came up with #NAME?

 

I noticed that you mentioned doing this on Office 365 or Excel for the web. I am working off of Excel 2016 on the desktop application. I don't know if that has anything to do with it not working? Is there a way to make this work on that version of Excel?

 

Thank you for your help!

@lexcel_1206 

Yes that's because e.g. LAMBDA isn't available in Excel 2016. Alternatively i'd create a helper column with this formula.

=ISNUMBER(D2)+ISTEXT(E2)

sort with helper column.png

Then i'd apply a custom sort by column F (helper column) in ascending order ("aufsteigend" in german in the screenshot).

sort ascending.png