Formula to find NEXT Unique Value in Row

[tjthom]

Hello,

Here is an example data table that would resemble mine. The columns with bold red text are the columns I need to develop a formula for:

[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]Task1[/TD]
[TD]Task2[/TD]
[TD]Task3[/TD]
[TD]Task4[/TD]
[TD]Task5[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 5[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]


Essentially, I have created a Gantt chart listing persons in the first column, and times of the day as headers so I can track which task a person is assigned to throughout the day.

At the end of the day (Column I in this example) I want to place "Task" headers and have "Task 1" represent the first task-number a person is assigned to, regardless of what column that task occurs in. "Task 2" and the following tasks should represent the second task number (or Nth task number) found in that row, excluding blank cells, previously found task numbers, cells with text, or any non-number.

Things to note for the formula:

• A person may have 0 to 10 tasks. However, "Task 1" should always represent the first non-blank cell in that row
• For "Task 2" through "Task N," the task may be following another task or it could be following a task and a blank cell (a "break" in their day)

I successfully generated the formula for "Task 1" by finding the value of the first non-blank cell. https://exceljet.net/formula/get-first-non-blank-value-in-a-list However, that does not solve the issue for finding the next non-duplicated number after "Task 1" has been found.

Any help you can provide on this would be EXTREMELY APPRECIATED!!!!

As a complete side note: the reason I am looking to find this information is I have another sheet that will be used to mail merge this information for each person. It needs to determine which task their day is starting with, then using some other formulas (yet to be developed) it will generate a start/end time by using INDEX/MATCH and finding the first value in the row and the last value in a row. So if you have any tips on how to find the first value of "Task N" and the last value of "Task N" in the row, bonus points to you!!!

 

[tjthom]

I also should mention that I've been able to do this somewhat with conditional formatting. Using conditional formatting, I am able to highlight the first cell where the task number changes. Here is that formula:

=AND(B1<>A1,ISNUMBER(B1))

In that formula, if the cell it is checking (in this case B1) is the same as the cell before it, it fails. This means only when there is a change in the number (task) does the condition pass. I need this type of logic for the formula above, but I need the formula to return the new value and not just a TRUE/FALSE result.

Thanks for any suggestions at all! I'll take anything and try to work it out myself as well.