Formula Help - Multiple lines into one line with pipes

[metz=uk]

Good afternoon all,

I have a unique issue and was wondering if there was any way a formula could help.

0612020/WHITE/XL	PM3.B1.H.01
0612020/WHITE/XL	PM3.B1.LOC.12
0612020/WHITE/XL	PM3.B1.PROC.1
0612020/WHITE/XL	PM3.P.N.72.D
0612020/WHITE/XL	PM3.P.Q.38.D
063560030/WHITE/2XL	PM3.P.N.45.D
063560030/WHITE/2XL	PM3.P.N.71.C
063560030/WHITE/L	PM3.B1.LOC.6
063560030/WHITE/L	PM3.B2.Z.01
063560030/WHITE/L	PM3.P.O.21.C
063560030/WHITE/L	PM3.P.O.56.C

I have the above data on a spreadsheet and need to condense it to the below (If column A = duplicates, then take the relevant values from column B and add a pipe between them.)

0612020/WHITE/XL	PM3.B1.H.01	PM3.3B.A|PM3.B1.H.01|PM3.B1.LOC.12|PM3.B1.PROC.1|PM3.P.N.72.D|PM3.P.Q.38.D
0612020/WHITE/XL	PM3.B1.LOC.12
0612020/WHITE/XL	PM3.B1.PROC.1
0612020/WHITE/XL	PM3.P.N.72.D
0612020/WHITE/XL	PM3.P.Q.38.D
063560030/WHITE/2XL	PM3.P.N.45.D	PM3.P.N.45.D|PM3.P.N.71.C
063560030/WHITE/2XL	PM3.P.N.71.C
063560030/WHITE/L	PM3.B1.LOC.6	PM3.B1.LOC.6|PM3.B2.Z.01|PM3.P.O.21.C|PM3.P.O.56.C
063560030/WHITE/L	PM3.B2.Z.01
063560030/WHITE/L	PM3.P.O.21.C
063560030/WHITE/L	PM3.P.O.56.C

Is there a formula that can help with this?

Kind Regards,

[CK76]

Assuming data starts as A2.

Please Login or Register  to view this content.

Confirmed as array (CTRL + SHIFT + ENTER)

If TEXTJOIN isn't available in your Excel version... use PowerQuery to perform self join.

[CK76]

PQ steps:
1. Load range to PQ. Go to editor.
2. Copy and paste the query.
3. In copied query. Group by Column1, All Rows (temp).
4. Add custom column (Custom) with following:

Please Login or Register  to view this content.

5. Add another custom column (Custom.1):

Please Login or Register  to view this content.

6. Back in original query, Merge Queries. Using original's [Column2] and copy's [Custom] as key and left outer join.
7. Expand merged query for [Custom.1].

See attached sample.

[johnmpl]

Hi, to both!

Another option, with helper "D" column (works in any Excel version):

[D2] : =B2&IF(A2=A3,"|"&D3,"")
[C2] : =IF(A1=A2,"",D2)

And drag it down.

Check file with the option applied. Blessings!

[metz=uk]

Brilliant, thank you guys! Both ways are working :D