Simple visualization for project database

[appreciative]

Hi everyone,

I have a request that should hopefully be somewhat simple for the geniuses out there. I've played with variations of arrays, INDEX, and MATCH, but can't figure this one out.



This is how my database is stored:

<table x:str="" style="border-collapse: collapse; width: 276pt;" border="0" cellpadding="0" cellspacing="0" width="458"><col style="width: 30pt;" width="50"> <col style="width: 38pt;" span="3" width="63"> <col style="width: 44pt;" span="3" width="73"> <tbody><tr style="height: 13.5pt;" height="23"> <td class="xl37" style="height: 13.5pt; width: 30pt;" width="50" height="23">Code</td> <td class="xl37" style="border-left: medium none; width: 38pt;" width="63">Status</td> <td class="xl41" style="border-left: medium none; width: 38pt;" width="63">Start</td> <td class="xl41" style="width: 38pt;" width="63">End</td> <td class="xl38" style="width: 44pt;" width="73">Person1</td> <td class="xl39" style="border-left: medium none; width: 44pt;" width="73">Person2</td> <td class="xl40" style="border-left: medium none; width: 44pt;" width="73">Person3</td> </tr> <tr style="height: 12.75pt;" height="21"> <td class="xl33" style="height: 12.75pt;" height="21">AAA</td> <td class="xl33" style="border-left: medium none;">Active</td> <td class="xl42" style="border-left: medium none;" x:num="39619" align="center">20-Jun</td> <td class="xl42" x:num="39634" align="center">5-Jul</td> <td class="xl34">John</td> <td class="xl35" style="border-left: medium none;">Michelle</td> <td class="xl36" style="border-left: medium none;">Peter</td> </tr> <tr style="height: 12.75pt;" height="21"> <td class="xl31" style="border-top: medium none; height: 12.75pt;" height="21">BBB</td> <td class="xl31" style="border-top: medium none; border-left: medium none;">Inactive</td> <td class="xl43" style="border-top: medium none; border-left: medium none;" x:num="39619" align="center">20-Jun</td> <td class="xl43" style="border-top: medium none;" x:num="39634" align="center">5-Jul</td> <td class="xl26" style="border-top: medium none;">Jack</td> <td class="xl25" style="border-top: medium none; border-left: medium none;"></td> <td class="xl27" style="border-top: medium none; border-left: medium none;"></td> </tr> <tr style="height: 12.75pt;" height="21"> <td class="xl31" style="border-top: medium none; height: 12.75pt;" height="21">CCC</td> <td class="xl31" style="border-top: medium none; border-left: medium none;">Active</td> <td class="xl43" style="border-top: medium none; border-left: medium none;" x:num="39619" align="center">20-Jun</td> <td class="xl43" style="border-top: medium none;" x:num="39634" align="center">5-Jul</td> <td class="xl26" style="border-top: medium none;">Jill</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">Jessica</td> <td class="xl27" style="border-top: medium none; border-left: medium none;"></td> </tr> <tr style="height: 12.75pt;" height="21"> <td class="xl31" style="border-top: medium none; height: 12.75pt;" height="21">DDD</td> <td class="xl31" style="border-top: medium none; border-left: medium none;">Inactive</td> <td class="xl43" style="border-top: medium none; border-left: medium none;" x:num="39619" align="center">20-Jun</td> <td class="xl43" style="border-top: medium none;" x:num="39634" align="center">5-Jul</td> <td class="xl26" style="border-top: medium none;">Ashley</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">Fred</td> <td class="xl27" style="border-top: medium none; border-left: medium none;"></td> </tr> <tr style="height: 13.5pt;" height="23"> <td class="xl32" style="border-top: medium none; height: 13.5pt;" height="23">EEE</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">Active</td> <td class="xl44" style="border-top: medium none; border-left: medium none;" x:num="39619" align="center">20-Jun</td> <td class="xl44" style="border-top: medium none;" x:num="39634" align="center">5-Jul</td> <td class="xl28" style="border-top: medium none;">Steven</td> <td class="xl29" style="border-top: medium none; border-left: medium none;"></td> <td class="xl30" style="border-top: medium none; border-left: medium none;"></td> </tr> </tbody></table>


This is the sheet for my formula(s):

<table x:str="" style="border-collapse: collapse; width: 206pt;" border="0" cellpadding="0" cellspacing="0" width="340"><col style="width: 38pt;" width="64"> <col style="width: 28pt;" span="6" width="46"> <tbody><tr style="height: 13.5pt;" height="23"> <td class="xl31" style="height: 13.5pt; width: 38pt;" width="64" height="23">Person</td> <td class="xl38" style="width: 28pt;" x:num="39630" align="right" width="46">1-Jul</td> <td class="xl39" style="border-left: medium none; width: 28pt;" x:num="39631" align="right" width="46">2-Jul</td> <td class="xl39" style="border-left: medium none; width: 28pt;" x:num="39632" align="right" width="46">3-Jul</td> <td class="xl39" style="border-left: medium none; width: 28pt;" x:num="39633" align="right" width="46">4-Jul</td> <td class="xl39" style="border-left: medium none; width: 28pt;" x:num="39634" align="right" width="46">5-Jul</td> <td class="xl40" style="border-left: medium none; width: 28pt;" x:num="39635" align="right" width="46">6-Jul</td> </tr> <tr style="height: 12.75pt;" height="21"> <td class="xl35" style="height: 12.75pt;" height="21">John</td> <td class="xl34" align="center"> ?</td> <td class="xl29" style="border-left: medium none;"></td> <td class="xl29" style="border-left: medium none;"></td> <td class="xl29" style="border-left: medium none;"></td> <td class="xl29" style="border-left: medium none;"></td> <td class="xl30" style="border-left: medium none;"></td> </tr> <tr style="height: 12.75pt;" height="21"> <td class="xl36" style="border-top: medium none; height: 12.75pt;" height="21">Peter</td> <td class="xl32" style="border-top: medium none;"></td> <td class="xl25" style="border-top: medium none; border-left: medium none;"></td> <td class="xl25" style="border-top: medium none; border-left: medium none;"></td> <td class="xl25" style="border-top: medium none; border-left: medium none;"></td> <td class="xl25" style="border-top: medium none; border-left: medium none;"></td> <td class="xl26" style="border-top: medium none; border-left: medium none;"></td> </tr> <tr style="height: 12.75pt;" height="21"> <td class="xl36" style="border-top: medium none; height: 12.75pt;" height="21">Jill</td> <td class="xl32" style="border-top: medium none;"></td> <td class="xl25" style="border-top: medium none; border-left: medium none;"></td> <td class="xl25" style="border-top: medium none; border-left: medium none;"></td> <td class="xl25" style="border-top: medium none; border-left: medium none;"></td> <td class="xl25" style="border-top: medium none; border-left: medium none;"></td> <td class="xl26" style="border-top: medium none; border-left: medium none;"></td> </tr> <tr style="height: 12.75pt;" height="21"> <td class="xl36" style="border-top: medium none; height: 12.75pt;" height="21">Steven</td> <td class="xl32" style="border-top: medium none;"></td> <td class="xl25" style="border-top: medium none; border-left: medium none;"></td> <td class="xl25" style="border-top: medium none; border-left: medium none;"></td> <td class="xl25" style="border-top: medium none; border-left: medium none;"></td> <td class="xl25" style="border-top: medium none; border-left: medium none;"></td> <td class="xl26" style="border-top: medium none; border-left: medium none;"></td> </tr> <tr style="height: 13.5pt;" height="23"> <td class="xl37" style="border-top: medium none; height: 13.5pt;" height="23">Jack</td> <td class="xl33" style="border-top: medium none;"></td> <td class="xl27" style="border-top: medium none; border-left: medium none;"></td> <td class="xl27" style="border-top: medium none; border-left: medium none;"></td> <td class="xl27" style="border-top: medium none; border-left: medium none;"></td> <td class="xl27" style="border-top: medium none; border-left: medium none;"></td> <td class="xl28" style="border-top: medium none; border-left: medium none;"></td> </tr> </tbody></table>


The formula would fill the blank cell in the sheet above with the "Code" each person is working on each day.



For example, where "John" and "1-Jul" meet:

I would like the formula to do is look for the active case that John is currently on in the database. That would require:
1) Checking if the row has a status of "Active"
2) Only on those active rows, searching across all three Person columns for the row with his name
3) On the active row with his name, check if the current date is within the Start to End range
4) Return the case code from that row

If the person is not on an active case that day, the cell can just be blank.



Any help in the right direction is much, much appreciated!

 

[DonkeyOte]

in your real scenario will it always just be 3 people columns or more -- will affect best way to do this ...

 

[DonkeyOte]

I assumed your data was in A1:G6 on Sheet1

in Sheet2 I had names in A2:A6 and dates in B1:G1

the following would return either Sheet1 A1 (if no match) or Actual Code reference (A2:A6) in the matrix .. note in below I used range 2:100 to give some leeway for future records but keep as small as possible.

=OFFSET(Sheet1!$A$1,MAX(SUMPRODUCT(--(Sheet1!$C$2:$C$100<=B$1),--(Sheet1!$D$2:$D$100>=B$1),--(UPPER(TRIM(Sheet1!$B$2:$B$100))="ACTIVE"),
--((Sheet1!$E$2:$E$100=$A2)+(Sheet1!$F$2:$F$100=$A2)+(Sheet1!$G$2:$G$100=$A2)),ROW(B$2:B$100)),1)-1,0)

Now if in your real data nothing in A will contain whatever is written in Sheet1!A1 (currently "Code") you could use a SUBSTITUTE around this to remove the "Code" error and replace with "No Match" or some other string

=SUBSTITUTE(OFFSET(....),Sheet1!$A$1,"No Match")

Or alternatively on Sheet1!A1 type "No Match" and remove need for SUBSTITUTION altogether.

Hope that helps.

Obviously this is all dependent on no person being allocated to 1+ codes within any given timeframe... else it's far more complicated.

 

[appreciative]

lasw10, thank you SO much. very impressed

only question - you used the range 2:100 and said i should i keep it fairly simple, but my database will become fairly large over time. there will only be about ~10 "active" cases at any given time, but all old cases will stay in the database as "inactive."

will this formula suffice for this database size or could excel get pretty slow?

again, thanks so much!

 

[DonkeyOte]

SUMPRODUCT's are *significant* -- compounded by how many you use and how big your ranges are... if you don't need the "Inactive" records I'd suggest you keep a sheet with just the "Active" records and use that as your source.

Another option would be to concatenate the people in a new column on your data sheet (E,F,G to H), you could then revise the SUMPRODUCT to try and FIND the name of the person in the Concatenated string meaning only one range to look in as opposed to 3.

 

[appreciative]

hi lasw10 - i've been using the sumproduct formula you suggested. while it works perfectly, it does take quite some time for the formulas to calculate. i did limit my list to less than 100 entries, but its still quite heavy.

i tried concatenating and using find as you suggested, but i'm not sure how to make it work within the formula. tried a couple times with no luck - think you can help me figure it out?

thanks so much again!

 

[DonkeyOte]

assuming the concatenation of E,F & G was in H you could use something like the below to check just H by looking for the name in the concatenation, as below:

=OFFSET(Sheet1!$A$1,MAX(SUMPRODUCT(--(Sheet1!$C$2:$C$100<=B$1),--(Sheet1!$D$2:$D$100>=B$1),--(UPPER(TRIM(Sheet1!$B$2:$B$100))="ACTIVE"),
--(ISNUMBER(SEARCH($A2,Sheet1!$H$2:$H$100))),ROW(B$2:B$100)),1)-1,0)

I would still advise (strongly) that you create a sheet that holds nothing other than Active records as this would lighten the load significantly.