Hi All
I'm looking for a formula for column H in the example below. With some help from the lovely people on here, I managed to get this to work in column E, but now I need a similar formula for column H. I want column H to automatically generate an Action ID based on what the user enters in the adjacent cell in column G - Interaction ID. The interaction ID in column G can be repeated and I want the formula in column H to append a number (making it unique) to the end of the ID. Each time the Interaction ID is repeated, it appends the next number in order, as per the example below. I have tried to modify the formula in Column E to see if I can get that to work, but I don't fully understand what each part of the formula is doing, so my efforts so far have been in vain. Any help would be appreciated.
I'm looking for a formula for column H in the example below. With some help from the lovely people on here, I managed to get this to work in column E, but now I need a similar formula for column H. I want column H to automatically generate an Action ID based on what the user enters in the adjacent cell in column G - Interaction ID. The interaction ID in column G can be repeated and I want the formula in column H to append a number (making it unique) to the end of the ID. Each time the Interaction ID is repeated, it appends the next number in order, as per the example below. I have tried to modify the formula in Column E to see if I can get that to work, but I don't fully understand what each part of the formula is doing, so my efforts so far have been in vain. Any help would be appreciated.
| Fruit Example.xlsm | |||||||
|---|---|---|---|---|---|---|---|
| D | E | F | G | H | |||
| 1 | Sheet 1 | Sheet 2 | |||||
| 2 | Activity ID | Interaction ID | Interaction ID | Action ID | |||
| 3 | ACT_1 | ACT_1A | ACT_1A | ACT_1A_1 | |||
| 4 | ACT_1 | ACT_1B | ACT_1A | ACT_1A_2 | |||
| 5 | ACT_3 | ACT_3A | ACT_1A | ACT_1A_3 | |||
| 6 | ACT_1 | ACT_1C | ACT_1B | ACT_1B_1 | |||
| 7 | ACT_49 | ACT_49A | ACT_1B | ACT_1B_2 | |||
| 8 | ACT_21 | ACT_21A | ACT_49A | ACT_49A_1 | |||
| 9 | ACT_21 | ACT_21B | ACT_21A | ACT_21A_1 | |||
| 10 | ACT_21 | ACT_21C | ACT_21B | ACT_21B_1 | |||
| 11 | ACT_21 | ACT_21D | ACT_21B | ACT_21B_2 | |||
| 12 | ACT_21 | ACT_21E | ACT_7A | ACT_7A_1 | |||
| 13 | ACT_21 | ACT_21F | ACT_21F | ACT_21F_1 | |||
| 14 | ACT_7 | ACT_7A | ACT_21F | ACT_21F_2 | |||
| 15 | ACT_21F | ACT_21F_3 | |||||
| 16 | ACT_21D | ACT_21D_1 | |||||
Sheet3 | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E3 | E3 | =D3&SUBSTITUTE(ADDRESS(1,COUNTIFS(D3:D$3,D3),4),1,"") |
| E4:E14 | E4 | =D4&SUBSTITUTE(ADDRESS(1,COUNTIFS(D$3:D4,D4),4),1,"") |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| G16:H16 | Cell | contains an error | text | NO |
| G15:H15 | Cell | contains an error | text | NO |
| G7 | Cell | contains an error | text | NO |
| G6 | Cell | contains an error | text | NO |
| G5 | Cell | contains an error | text | NO |
| G4 | Cell | contains an error | text | NO |
| G3:H14 | Cell | contains an error | text | NO |
| E3:E14 | Cell | contains an error | text | NO |
