index match, multiple columns/array with date, strings and blank cells

lukasvjohansson

New Member
Joined
Jun 26, 2018
Messages
27
Hi,

I have a problem regarding a structure for project management and specifically deadlines.

On the rows I have different customers/projects which each have columns with different activities, e.g.:


Project/CustomerDeadline activity 1Deadline activity 2Deadline activity 3Deadline activity 4...
Customer 12019-02-282019-03-052019-03-07
Customer 2YESNot possible2019-03-012019-04-02
Customer 3YES2019-01-02YES
Customer 4YES2019-03-022019-03-13
Customer 5YESYESNot possible
Customer...YESYESNot possible

<tbody>
</tbody>



For example, the data could look like this. Where yes indicates a finished deadline, not possible is an activity that is not possible to perform, and for the blank cells there is not a deadline in place yet, but will be added later. Dates that has already passed may be still kept in the table, like in activity 2 and customer 3 in the example above. I have already applied a conditional formatting on this table, but now...

... I would like to create a separate ranking table of, for e.g., the top 10 most pressing activities, regardless of customer and activity:

RankingCustomerActivityDate
1Customer 1Deadline activity 12019-02-28
2Customer 4Deadline activity 22019-03-02
3.........
4
5
6
...

<tbody>
</tbody>

Ive tried with =SMALL function and using the ranking as the N.th smallest date, but realised that since old dates might be included, the difference from TODAY() function is probably most applicable to get which date is coming up next. Ive made som progress with MMULT function, but at that point things got a bit too complex for me. My greatest challenge seem to be that the table that should be modified includes date, strings and blank cells. And since date is just a format and is still seen as number, I have a hard time separating them.

Any suggestions would be greatly appreciated. Basically my need is to develop a easy, comprehensive to-do list that can handle multiple projects (about 50+) and multiple deadlines on many different activity types (>10)

Best regards

Lukas
 
Last edited:

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

lukasvjohansson

New Member
Joined
Jun 26, 2018
Messages
27
The to-do list table would of course be sorted on the date for the deadline, hence prioritised on the most pressing deadline-date, rather than the type of that activity or the customer.
 

lukasvjohansson

New Member
Joined
Jun 26, 2018
Messages
27
And sorry for all the extra post, but can't edit anymore.

The data is currently not structured as a table, but rather just plain data. Please let me know if an actual table would help my purpose.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
Perhaps something like this:

ABCDEFGHIJKLMN
1Project/CustomerDeadline activity 1Deadline activity 2Deadline activity 3Deadline activity 4...RankingCustomerActivityDate
2Customer 12/28/20193/5/20193/5/20191Customer 2Deadline activity 33/1/2019
3Customer 2YESNot possible3/1/20194/2/20192Customer 4Deadline activity 23/2/2019
4Customer 3YES1/2/2019YES3Customer 1Deadline activity 23/5/2019
5d>Customer 4YES3/2/20193/13/20194Customer 1Deadline activity 33/5/2019
6Customer 5YESYESNot possible5Customer 4Deadline activity 33/13/2019
7Customer...YESYESNot possibl
e
6Customer 2Deadline activity 44/2/2019
87
98
109
1110

<tbody>
</tbody>
Sheet3

Array Formulas
CellFormula
K2{=IF(M2="","",INDEX($A$2:$A$7,INT(SMALL(IF($B$2:$E$7=M2,ROW($B$2:$E$7)*1000+COLUMN($B$2:$E$7)),COUNTIF($M$2:$M2,M2))/1000)-ROW($B$2)+1))}
L2{=IF(M2="","",INDEX($B$1:$E$1,MOD(SMALL(IF($B$2:$E$7=M2,ROW($B$2:$E$7)*1000+COLUMN($B$2:$E$7)-COLUMN($B$2)+1),COUNTIF($M$2:$M2,M2)),1000)))}
M2{=IFERROR(SMALL(IF($B$2:$E$7>=TODAY(),$B$2:$E$7),ROWS($M$2:$M2)),"")}

<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>


I changed one of the dates to show that these formulas handle ties.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,951
Messages
5,525,833
Members
409,666
Latest member
aquabit

This Week's Hot Topics

Top