2

I have a project spreadsheet, with project managers entering current progress status for each project for each month where there was some activity. So the columns A B and C are filled, I need to be able to figure out the column D using arrayformula. (note that there was no activity/record for "Project 1" in June 2019)

I thought I would first need a helper column to find the date of the previous record for the project and then vlookup the date for the project and return the progress of that date. But I got stuck when trying to figure out the date for the previous record for the project, but I'm sort of stuck. Maybe someone here can help me with the arrayformula I should enter in D2 in order to get the values D2:D6

https://docs.google.com/spreadsheets/d/1DM8ZK6FwBFAmqUg5bv3o-aTIDtVKKMfGRXc2g9SSYgw/edit?usp=sharing

player0
  • 124,011
  • 12
  • 67
  • 124
Mara
  • 371
  • 5
  • 16

1 Answers1

2

try:

=ARRAYFORMULA(IF(A2:A<>"", C2:C-IFNA(VLOOKUP(
 COUNTIFS(B2:B, B2:B, ROW(B2:B), "<="&ROW(B2:B))&B2:B, {
 COUNTIFS(B2:B, B2:B, ROW(B2:B), "<="&ROW(B2:B))+1&B2:B, C2:C}, 2, 0)), ))

0

UPDATE:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A&B2:B, {SORT(A2:A)&INDEX(SORT(A2:B, 1, 1, 2, 1),,2), 
 IF(A2:A<>"", INDEX(SORT(A2:C, 1, 1, 2, 1),,3)-IFNA(VLOOKUP(
 COUNTIFS(INDEX(SORT(A2:B, 1, 1, 2, 1),,2), INDEX(SORT(A2:B, 1, 1, 2, 1),,2), 
 ROW(B2:B), "<="&ROW(B2:B))&INDEX(SORT(A2:B, 1, 1, 2, 1),,2), {
 COUNTIFS(INDEX(SORT(A2:B, 1, 1, 2, 1),,2), INDEX(SORT(A2:B, 1, 1, 2, 1),,2), 
 ROW(B2:B), "<="&ROW(B2:B))+1&INDEX(SORT(A2:B, 1, 1, 2, 1),,2), 
 INDEX(SORT(A2:C, 1, 1, 2, 1),,3)}, 2, 0)), )}, 2, 0)))

0

spreadsheet demo

UPDATE 2:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A&B2:B&C2:C&D2:D&E2:E; {
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;1)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5)\ IF(
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;6)<>""; 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;6)-IFNA(VLOOKUP(IF(
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3)<>""; COUNTIFS(
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5); ROW(K2:K); "<="&ROW(K2:K)); )&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5); {IF(
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3)<>""; COUNTIFS(
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5); 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5); ROW(K2:K); "<="&ROW(K2:K))+1; )&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;2)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;3)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;4)&
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;5)\ 
 INDEX(SORT({A2:A\B2:B\C2:C\D2:D\E2:E\F2:F}; 5; 1; 4; FALSE; 1; 1; 2; 1);;6)}; 2; 0)); )}; 2; 0)))

0

spreadsheet demo

Community
  • 1
  • 1
player0
  • 124,011
  • 12
  • 67
  • 124
  • I don't know how you do the magic, but it works! Thanx! Your formula doesn't work on non-sorted rows? This is a sheet with non-sorted rows: https://docs.google.com/spreadsheets/d/1DM8ZK6FwBFAmqUg5bv3o-aTIDtVKKMfGRXc2g9SSYgw/edit#gid=578321031 – Mara Oct 31 '19 at 17:51
  • Hmm, I've rearranged the rows, and it doesn't show the correct values ;( – Mara Oct 31 '19 at 18:54
  • Yes, my table is unsorted. I could use a helper sheet to sort it, but the thing is already not very responsive and I'm a bit reluctant to add new sheets. Dates do matter, I need to find out the delta between current months progress, and the progress of the last month with a record. If there is no activity then there is no record for the month, so I might have to compare July with April if no record for May and June. – Mara Nov 04 '19 at 07:58
  • I've c&p your formula in G2, but results don't seem to be correct ;( – Mara Nov 04 '19 at 14:46
  • @Mara answer updated. demo sheet included with step by step explanation – player0 Nov 04 '19 at 16:45
  • ,this breakdown was very helpful, I've extended it with few more criterions (client/type/group), but I don't understand how you did the magic to put all the red column formulas into one single cell . This is my current draft, G2 is missing, the rest is fine. https://docs.google.com/spreadsheets/d/1qzAp9CjUFRm6u-w7laZjByaoHoXdFAG-g2g8ulBK9LI/edit?usp=sharing – Mara Nov 14 '19 at 14:49