Forum Discussion
Consolidate unique values from matched cells
Hi All,
The table below probably explains it best, however, I am trying to consolidate the unique values in column C against the common value in column B then populate the consolidated data into column D separated with a /. If the data in column B also exists in Column F then the test "Direct" should also be consolidated into Column D.
Eg CNSPER can be via Direct & BNE & MEL & SYD. Result should be Direct/BNE/MEL/SYD
Where there is only one possible via point then their should be no "/"
EG MELSYD result should be Direct
| A | B | C | D | E | F |
| 2 | Connecting | Direct | |||
| 3 | OD | Via | Result | OD | |
| 4 | CNSPER | BNE | Direct/BNE/MEL/SYD | CNSPER | |
| 5 | CNSPER | MEL | Direct/BNE/MEL/SYD | MELSYD | |
| 6 | CNSPER | SYD | Direct/BNE/MEL/SYD | LAXNYC | |
| 7 | ADLCNS | BNE | BNE/MEL/SYD | ||
| 8 | ADLCNS | MEL | BNE/MEL/SYD | ||
| 9 | ADLCNS | SYD | BNE/MEL/SYD | ||
| 10 | LAXNYC | BOS | |||
| 11 | LAXNYC | CHI | |||
| 12 | LAXNYC | SFO | |||
| 13 | MELSYD |
Thanks in advance,
Peter
2 Replies
Peter,
{=IF(COUNTIFS($E$3:$E$5,A3),"Direct"&IF(B3="","","/"),"")&TEXTJOIN("/",TRUE,IF(($A$3:$A$12=A3)*($B$3:$B$12<>""),$B$3:$B$12,""))}Many thanks Detlef. Works perfectly.
Peter
