Dynamic Array to make 2 Columns

[Stephen_IV]

I need a Dynamic Array to make 2 columns with the data set that I have below.

Book3 A B C D E F G H 1 Names Anchorage ANC 2 Anchorage - ANC Boston BOS 3 Boston - BOS San Juan SJU 4 San Juan - SJU Chicago MDW 5 Chicago - MDW - ORD Chicago ORD 6 Honolulu - HNL Honolulu HNL 7 Los Angeles - LAX - ONT - SNA Los Angeles LAX 8 New York - EWR - JFK - LGA Los Angeles ONT 9 Orlando - MCO Los Angeles SNA 10 Hartford - HFD New York EWR 11 Phoenix - PHX New York JFK 12 Portland - PDX New York LGA 13 Seattle - SEA Orlando MCO 14 Toronto - YYZ - YTZ Hartford HFD 15 Washington DC - IAD - DCA Phoenix PHX 16 Portland PDX 17 Seattle SEA 18 Toronto YYZ 19 Toronto YTZ 20 Washington DC IAD 21 Washington DC DCA Sheet4

 

[Fluff]

How about

=DROP(REDUCE("",A2:A15,LAMBDA(x,y,LET(r,TRIM(TEXTSPLIT(y,,"-")),a,INDEX(r,1,1),VSTACK(x,HSTACK(EXPAND(a,ROWS(r)-1,,a),DROP(r,1)))))),1)

 

[Stephen_IV]

Perfect! Thank you again Fluff!! You have been a lifesaver and mentor. Thanks again!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Peter_SSs]

If you are interested, another option using quite a few less functions:

=TEXTSPLIT(TEXTJOIN("|",,TEXTAFTER(SUBSTITUTE(A2:A15,"-","|"&LEFT(A2:A15,FIND("-",A2:A15))),"|"))," - ","|")

 

[Stephen_IV]

Sure thanks!! I appreciate it.

 

[Peter_SSs]

You're welcome.