Hello,
I am wondering if I can accomplish what I want to do without adding a helper column. I have the formula in cells I4 to M4 and I9 to M9 showing the Area number from column C if the dates match and there is #2 in Column B. Then in the next row (I5 to M5 and so on), I want to show the event type in column D only if there is #2 in column B. If an extra person is assigned in column F, I want to show that as well. There is where my challenge begins.
I want to shorten the name in Column F showing the last name and whatever letter follows the name in the format of Letter First then the last name. Example is in L11. I added a helper column with a trim, right Substitute formula but wondering if there was a way to do without it.
I am wondering if I can accomplish what I want to do without adding a helper column. I have the formula in cells I4 to M4 and I9 to M9 showing the Area number from column C if the dates match and there is #2 in Column B. Then in the next row (I5 to M5 and so on), I want to show the event type in column D only if there is #2 in column B. If an extra person is assigned in column F, I want to show that as well. There is where my challenge begins.
I want to shorten the name in Column F showing the last name and whatever letter follows the name in the format of Letter First then the last name. Example is in L11. I added a helper column with a trim, right Substitute formula but wondering if there was a way to do without it.
| Audit Ex.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | Date | Type | Area | Event | Primary | Extra | Helper | ||||||||
| 2 | 10/4/2021 | 1 | 111 | Zoom Call | Kim | ||||||||||
| 3 | 10/4/2021 | 1 | 112 | Zoom Call | 10/4/21 | 10/8/21 | 10/12/21 | 10/13/21 | 10/14/21 | ||||||
| 4 | 10/8/2021 | 2 | 115 | (Audit P) | 115 | 117 | 119 | ||||||||
| 5 | 10/12/2021 | 1 | 116 | Zoom Call | P - Kim | CC Audit | P - Kim | ||||||||
| 6 | 10/12/2021 | 2 | 117 | CC Audit | |||||||||||
| 7 | 10/13/2021 | 1 | 118 | Zoom Call | |||||||||||
| 8 | 10/14/2021 | 2 | 119 | (Audit P) | 10/15/21 | 10/18/21 | 10/19/21 | 10/20/21 | 10/21/21 | ||||||
| 9 | 10/14/2021 | 1 | 120 | Zoom Call | 122 | 123 | 127 | ||||||||
| 10 | 10/14/2021 | 1 | 121 | Zoom Call | S - Kim | S - Kim | S - Kim Chris Smith (P) | ||||||||
| 11 | 10/15/2021 | 2 | 122 | (Audit S) | S-Kim, P- Smith | ||||||||||
| 12 | 10/18/2021 | 1 | 122 | 3 Week Call | |||||||||||
| 13 | 10/19/2021 | 2 | 123 | (Audit S) | |||||||||||
| 14 | 10/19/2021 | 1 | 124 | Zoom Call | |||||||||||
| 15 | 10/20/2021 | 2 | 127 | (Audit S) | Chris Smith (P) | Smith | |||||||||
Sheet3 | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I9:M9,I4:M4 | I4 | =IFERROR(INDEX($C$2:$C$15,MATCH(1,INDEX((I3=$A$2:$A$15)*(2=$B$2:$B$15),0),0)),"") |
| I5,M5 | I5 | =IFERROR(IF(INDEX($D$2:$D$15,MATCH(I4,$C$2:$C$15,0),0)="(Audit P)","P - "&$E$2&" "&INDEX($F$2:$F$15,MATCH(I4,$C$2:$C$15,0),0),IF(INDEX($D$2:$D$15,MATCH(I4,$C$2:$C$15,0),0)="Inventory (Secondary)","S - "&I4&" "&INDEX($F$2:$F$15,MATCH(I4,$C$2:$C$15,0),0),"CC Audit")),"") |
| J5:L5 | J5 | =IFERROR(IF(INDEX($D$2:$D$15,MATCH(J4,$C$2:$C$15,0),0)="(Audit P)","P - "&$E$2&" "&INDEX($F$2:$F$15,MATCH(J4,$C$2:$C$15,0),0),IF(INDEX($D$2:$D$15,MATCH(J4,$C$2:$C$15,0),0)="Inventory (Secondary)","S - "&J4&" "&INDEX($F$2:$F$15,MATCH(J4,$C$2:$C$15,0),0),"CC Audit")),"") |
| I10:M10 | I10 | =IFERROR(IF(INDEX($D$2:$D$15,MATCH(I9,$C$2:$C$15,0),0)="(Audit P)","P - "&$E$2&" "&INDEX($F$2:$F$15,MATCH(I9,$C$2:$C$15,0),0),IF(INDEX($D$2:$D$15,MATCH(I9,$C$2:$C$15,0),0)="(Audit S)","S - "&$E$2&" "&INDEX($F$2:$F$15,MATCH(I9,$C$2:$C$15,0),0),"CC Audit")),"") |
| L13 | L13 | =TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(I23)," ",REPT(" ",60)),120),60)) |
| G2:G15 | G2 | =TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(F2)," ",REPT(" ",60)),120),60)) |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
