X | a b c | a£b£c£ | b£c£ | c£ | | a | b | c | | a | b | c | | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | Y | c d e | c£d£e£ | d£e£ | e£ | | c | d | e | | | d | e | | Z | e f g | e£f£g£ | f£g£ | g£ | | e | f | g | | | f | g | | | | | | | | | | | | | | | | | | By formula | | | | | | | | | | | | a | X | a | | | | | | | | | | | | b | X | b | | | | | | | | | | | | c | X Y | c | | | | | | | | | | | | d | Y | d | | | | | | | | | | | | e | Y Z | e | | | | | | | | | | | | f | Z | f | | | | | | | | | | | | g | Z | g | | | | | | | | | | | |
using formulae in A1:C12
X | a b c | =SUBSTITUTE(B1,CHAR(10),"£")&"£" | :--- | :--- | :--- | Y | c d e | =SUBSTITUTE(B2,CHAR(10),"£")&"£" | Z | e f g | =SUBSTITUTE(B3,CHAR(10),"£")&"£" | | | | | | By formula | a | =StringConcat(CHAR(10),IF(NOT(ISERROR(FIND(A6,$B$1:$B$3))),$A$1:$A$3,"")) | =MID(StringConcat("",$K$1:$N$3),ROW()-5,1) | b | =StringConcat(CHAR(10),IF(NOT(ISERROR(FIND(A7,$B$1:$B$3))),$A$1:$A$3,"")) | =MID(StringConcat("",$K$1:$N$3),ROW()-5,1) | c | =StringConcat(CHAR(10),IF(NOT(ISERROR(FIND(A8,$B$1:$B$3))),$A$1:$A$3,"")) | =MID(StringConcat("",$K$1:$N$3),ROW()-5,1) | d | =StringConcat(CHAR(10),IF(NOT(ISERROR(FIND(A9,$B$1:$B$3))),$A$1:$A$3,"")) | =MID(StringConcat("",$K$1:$N$3),ROW()-5,1) | e | =StringConcat(CHAR(10),IF(NOT(ISERROR(FIND(A10,$B$1:$B$3))),$A$1:$A$3,"")) | =MID(StringConcat("",$K$1:$N$3),ROW()-5,1) | f | =StringConcat(CHAR(10),IF(NOT(ISERROR(FIND(A11,$B$1:$B$3))),$A$1:$A$3,"")) | =MID(StringConcat("",$K$1:$N$3),ROW()-5,1) | g | =StringConcat(CHAR(10),IF(NOT(ISERROR(FIND(A12,$B$1:$B$3))),$A$1:$A$3,"")) | =MID(StringConcat("",$K$1:$N$3),ROW()-5,1) |
in D1:F3
| =IFERROR(RIGHT(C1,LEN(C1)-FIND("£",C1)),"") | =IFERROR(RIGHT(D1,LEN(D1)-FIND("£",D1)),"") | =IFERROR(RIGHT(E1,LEN(E1)-FIND("£",E1)),"") |
|---|---|---|
| =IFERROR(RIGHT(C2,LEN(C2)-FIND("£",C2)),"") | =IFERROR(RIGHT(D2,LEN(D2)-FIND("£",D2)),"") | =IFERROR(RIGHT(E2,LEN(E2)-FIND("£",E2)),"") |
| =IFERROR(RIGHT(C3,LEN(C3)-FIND("£",C3)),"") | =IFERROR(RIGHT(D3,LEN(D3)-FIND("£",D3)),"") | =IFERROR(RIGHT(E3,LEN(E3)-FIND("£",E3)),"") |
In G1:J3
| =IFERROR(LEFT(C1,FIND("£",C1)-1),"") | =IFERROR(LEFT(D1,FIND("£",D1)-1),"") | =IFERROR(LEFT(E1,FIND("£",E1)-1),"") | =IFERROR(LEFT(F1,FIND("£",F1)-1),"") |
|---|---|---|---|
| =IFERROR(LEFT(C2,FIND("£",C2)-1),"") | =IFERROR(LEFT(D2,FIND("£",D2)-1),"") | =IFERROR(LEFT(E2,FIND("£",E2)-1),"") | =IFERROR(LEFT(F2,FIND("£",F2)-1),"") |
| =IFERROR(LEFT(C3,FIND("£",C3)-1),"") | =IFERROR(LEFT(D3,FIND("£",D3)-1),"") | =IFERROR(LEFT(E3,FIND("£",E3)-1),"") | =IFERROR(LEFT(F3,FIND("£",F3)-1),"") |
In K1:N3
| =IF(COUNTIF($G$1:$J1,G1)=1,G1,"") | =IF(COUNTIF($G$1:$J1,H1)=1,H1,"") | =IF(COUNTIF($G$1:$J1,I1)=1,I1,"") | =IF(COUNTIF($G$1:$J1,J1)=1,J1,"") |
|---|---|---|---|
| =IF(COUNTIF($G$1:$J2,G2)=1,G2,"") | =IF(COUNTIF($G$1:$J2,H2)=1,H2,"") | =IF(COUNTIF($G$1:$J2,I2)=1,I2,"") | =IF(COUNTIF($G$1:$J2,J2)=1,J2,"") |
| =IF(COUNTIF($G$1:$J3,G3)=1,G3,"") | =IF(COUNTIF($G$1:$J3,H3)=1,H3,"") | =IF(COUNTIF($G$1:$J3,I3)=1,I3,"") | =IF(COUNTIF($G$1:$J3,J3)=1,J3,"") |
Breaking it down in lots of cells.