Data Segregation

[shansakhi]

Hello Everybody,
Request your assistance to segregate the data available in one column to multiple column.
There are multiple scenarios which are mentioned in the data.

IS there

ASR_BSP working.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	INPUT	OUTPUT
2	Routing	Bus Name_1	Sector_1	Pax_1	Bus Name_2	Sector_2	Pax_2	Bus Name_3	Sector_3	Pax_3	Bus Name_4	Sector_4	Pax_4
3	AAA FF BBB	FF	AAA-BBB	1
4	AAA FF X BBB	FF	AAA-BBB	1
5	AAA FF O BBB	FF	AAA-BBB	1
6	AAA FF BBB ARNK ARNK	FF	AAA-BBB	1
7	AAA FF BBB BBB FF CCC	FF	AAA-BBB	1	FF	BBB-CCC	1
8	AAA O BBB BBB FF O CCC				FF	BBB-CCC	1
9	AAA X BBB BBB FF X CCC				FF	BBB-CCC	1
10	AAA O BBB BBB FF X CCC				FF	BBB-CCC	1
11	AAA X BBB BBB FF O CCC				FF	BBB-CCC	1
12	AAA FF BBB BBB FF X CCC	FF	AAA-BBB	1	FF	BBB-CCC	1
13	AAA FF BBB BBB FF O CCC	FF	AAA-BBB	1	FF	BBB-CCC	1
14	AAA FF X BBB BBB FF X CCC	FF	AAA-BBB	1	FF	BBB-CCC	1
15	AAA FF O BBB BBB FF X CCC	FF	AAA-BBB	1	FF	BBB-CCC	1
16	AAA FF X BBB BBB FF O CCC	FF	AAA-BBB	1	FF	BBB-CCC	1
17	AAA FF O BBB BBB FF O CCC	FF	AAA-BBB	1	FF	BBB-CCC	1
18	AAA FF BBB BBB CCC CCC FF DDD	FF	AAA-BBB	1				FF	CCC-DDD	1
19	AAA FF BBB ARNK ARNK CCC FF DDD	FF	AAA-BBB	1				FF	CCC-DDD	1
20	AAA FF BBB BBB FF CCC CCC FF DDD	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1
21	AAA FF BBB BBB O CCC CCC FF O DDD	FF	AAA-BBB	1				FF	CCC-DDD	1
22	AAA FF BBB BBB X CCC CCC FF O DDD	FF	AAA-BBB	1				FF	CCC-DDD	1
23	AAA FF BBB BBB O CCC CCC FF X DDD	FF	AAA-BBB	1				FF	CCC-DDD	1
24	AAA FF BBB BBB X CCC CCC FF X DDD	FF	AAA-BBB	1				FF	CCC-DDD	1
25	AAA O BBB BBB FF O CCC CCC FF X DDD				FF	BBB-CCC	1	FF	CCC-DDD	1
26	AAA O BBB BBB FF X CCC CCC FF X DDD				FF	BBB-CCC	1	FF	CCC-DDD	1
27	AAA X BBB BBB FF X CCC CCC FF X DDD				FF	BBB-CCC	1	FF	CCC-DDD	1
28	AAA X BBB BBB FF X CCC CCC FF O DDD				FF	BBB-CCC	1	FF	CCC-DDD	1
29	AAA X BBB BBB FF O CCC CCC FF O DDD				FF	BBB-CCC	1	FF	CCC-DDD	1
30	AAA O BBB BBB FF O CCC CCC FF O DDD				FF	BBB-CCC	1	FF	CCC-DDD	1
31	AAA FF BBB BBB FF X CCC CCC FF X DDD	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1
32	AAA FF BBB BBB FF O CCC CCC FF X DDD	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1
33	AAA FF BBB BBB FF X CCC CCC FF O DDD	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1
34	AAA FF BBB BBB FF O CCC CCC FF O DDD	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1
35	AAA FF O BBB BBB FF X CCC CCC FF X DDD	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1
36	AAA FF BBB BBB FF CCC CCC DDD DDD FF EEE	FF	AAA-BBB	1	FF	BBB-CCC	1				FF	DDD-EEE	1
37	AAA FF BBB BBB FF CCC ARNK ARNK DDD FF EEE	FF	AAA-BBB	1	FF	BBB-CCC	1				FF	DDD-EEE	1
38	AAA FF BBB ARNK ARNK CCC FF DDD DDD FF EEE	FF	AAA-BBB	1				FF	CCC-DDD	1	FF	DDD-EEE	1
39	AAA FF BBB BBB FF CCC CCC FF DDD DDD FF EEE	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1	FF	DDD-EEE	1
40	AAA FF BBB BBB FF X CCC CCC O DDD DDD FF O EEE	FF	AAA-BBB	1	FF	BBB-CCC	1				FF	DDD-EEE	1
41	AAA FF BBB BBB FF O CCC CCC O DDD DDD FF O EEE	FF	AAA-BBB	1	FF	BBB-CCC	1				FF	DDD-EEE	1
42	AAA FF BBB BBB FF X CCC CCC X DDD DDD FF X EEE	FF	AAA-BBB	1	FF	BBB-CCC	1				FF	DDD-EEE	1
43	AAA FF BBB BBB FF X CCC CCC FF X DDD DDD O EEE	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1
44	AAA FF BBB BBB FF X CCC CCC FF O DDD DDD O EEE	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1
45	AAA FF BBB BBB FF O CCC CCC FF X DDD DDD O EEE	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1
46	AAA FF BBB BBB FF X CCC CCC FF X DDD DDD X EEE	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1
47	AAA FF BBB BBB O CCC CCC FF O DDD DDD FF X EEE	FF	AAA-BBB	1				FF	CCC-DDD	1	FF	DDD-EEE	1
48	AAA FF BBB BBB X CCC CCC FF X DDD DDD FF X EEE	FF	AAA-BBB	1				FF	CCC-DDD	1	FF	DDD-EEE	1
49	AAA FF X BBB BBB FF X CCC CCC X DDD DDD FF X EEE	FF	AAA-BBB	1	FF	BBB-CCC	1				FF	DDD-EEE	1
50	AAA FF X BBB BBB O CCC CCC FF O DDD DDD FF X EEE	FF	AAA-BBB	1				FF	CCC-DDD	1	FF	DDD-EEE	1
51	AAA FF BBB BBB FF X CCC CCC FF O DDD DDD FF X EEE	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1	FF	DDD-EEE	1
52	AAA FF BBB BBB FF X CCC CCC FF X DDD DDD FF O EEE	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1	FF	DDD-EEE	1
53	AAA FF BBB BBB FF X CCC CCC FF O DDD DDD FF O EEE	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1	FF	DDD-EEE	1
54	AAA FF BBB BBB FF O CCC CCC FF O DDD DDD FF X EEE	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1	FF	DDD-EEE	1
55	AAA FF BBB BBB FF O CCC CCC FF O DDD DDD FF O EEE	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1	FF	DDD-EEE	1
56	AAA FF BBB BBB FF X CCC CCC FF X DDD DDD FF X EEE	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1	FF	DDD-EEE	1
57	AAA FF BBB BBB FF O CCC CCC FF X DDD DDD FF O EEE	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1	FF	DDD-EEE	1
58	AAA FF BBB BBB FF O CCC CCC FF X DDD DDD FF X EEE	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1	FF	DDD-EEE	1
59	AAA FF O BBB BBB FF O CCC CCC FF X DDD DDD FF X EEE	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1	FF	DDD-EEE	1
60	AAA FF O BBB BBB FF X CCC CCC FF O DDD DDD FF X EEE	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1	FF	DDD-EEE	1
Sheet1

a way that I can do this via loading data and putting some formulas as raw file is very heavy.

 

[sheetspread]

If that's exactly how the data appears (with no skips in the pattern) you can write nested if statements to print the segments to each cell/column.

 

[shansakhi]

The Bus no and Sectors will change. Multiple Bus Nos and Sectors are available in data.

 

[fjns]

Hi, based on the mini-sheet, the strings "O", "X" and "ARNK" are not included in the output. What do these mean?

 

[shansakhi]

Hi, based on the mini-sheet, the strings "O", "X" and "ARNK" are not included in the output. What do these mean?

O / X / ARNK needs to be ignored while creating OUTPUT

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Sanjeev1976]

Check below :

tEMPO.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	INPUT	OUTPUT
2	Routing	Bus Name_1	Sector_1	Pax_1	Bus Name_2	Sector_2	Pax_2	Bus Name_3	Sector_3	Pax_3	Bus Name_4	Sector_4	Pax_4
3	AAA FF BBB	FF	AAA-BBB	1
4	AAA FF X BBB	FF	AAA-BBB	1
5	AAA FF O BBB	FF	AAA-BBB	1
6	AAA FF BBB ARNK ARNK	FF	AAA-BBB	1
7	AAA FF BBB BBB FF CCC	FF	AAA-BBB	1	FF	BBB-CCC	1
8	AAA O BBB BBB FF O CCC				FF	BBB-CCC	1
9	AAA X BBB BBB FF X CCC				FF	BBB-CCC	1
10	AAA O BBB BBB FF X CCC				FF	BBB-CCC	1
11	AAA X BBB BBB FF O CCC				FF	BBB-CCC	1
12	AAA FF BBB BBB FF X CCC	FF	AAA-BBB	1	FF	BBB-CCC	1
13	AAA FF BBB BBB FF O CCC	FF	AAA-BBB	1	FF	BBB-CCC	1
14	AAA FF X BBB BBB FF X CCC	FF	AAA-BBB	1	FF	BBB-CCC	1
15	AAA FF O BBB BBB FF X CCC	FF	AAA-BBB	1	FF	BBB-CCC	1
16	AAA FF X BBB BBB FF O CCC	FF	AAA-BBB	1	FF	BBB-CCC	1
17	AAA FF O BBB BBB FF O CCC	FF	AAA-BBB	1	FF	BBB-CCC	1
18	AAA FF BBB BBB CCC CCC FF DDD	FF	AAA-BBB	1				FF	CCC-DDD	1
19	AAA FF BBB ARNK ARNK CCC FF DDD	FF	AAA-BBB	1				FF	CCC-DDD	1
20	AAA FF BBB BBB FF CCC CCC FF DDD	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1
21	AAA FF BBB BBB O CCC CCC FF O DDD	FF	AAA-BBB	1				FF	CCC-DDD	1
22	AAA FF BBB BBB X CCC CCC FF O DDD	FF	AAA-BBB	1				FF	CCC-DDD	1
23	AAA FF BBB BBB O CCC CCC FF X DDD	FF	AAA-BBB	1				FF	CCC-DDD	1
24	AAA FF BBB BBB X CCC CCC FF X DDD	FF	AAA-BBB	1				FF	CCC-DDD	1
25	AAA O BBB BBB FF O CCC CCC FF X DDD				FF	BBB-CCC	1	FF	CCC-DDD	1
26	AAA O BBB BBB FF X CCC CCC FF X DDD				FF	BBB-CCC	1	FF	CCC-DDD	1
27	AAA X BBB BBB FF X CCC CCC FF X DDD				FF	BBB-CCC	1	FF	CCC-DDD	1
28	AAA X BBB BBB FF X CCC CCC FF O DDD				FF	BBB-CCC	1	FF	CCC-DDD	1
29	AAA X BBB BBB FF O CCC CCC FF O DDD				FF	BBB-CCC	1	FF	CCC-DDD	1
30	AAA O BBB BBB FF O CCC CCC FF O DDD				FF	BBB-CCC	1	FF	CCC-DDD	1
31	AAA FF BBB BBB FF X CCC CCC FF X DDD	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1
32	AAA FF BBB BBB FF O CCC CCC FF X DDD	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1
33	AAA FF BBB BBB FF X CCC CCC FF O DDD	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1
34	AAA FF BBB BBB FF O CCC CCC FF O DDD	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1
35	AAA FF O BBB BBB FF X CCC CCC FF X DDD	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1
36	AAA FF BBB BBB FF CCC CCC DDD DDD FF EEE	FF	AAA-BBB	1	FF	BBB-CCC	1				FF	DDD-EEE	1
37	AAA FF BBB BBB FF CCC ARNK ARNK DDD FF EEE	FF	AAA-BBB	1	FF	BBB-CCC	1				FF	DDD-EEE	1
38	AAA FF BBB ARNK ARNK CCC FF DDD DDD FF EEE	FF	AAA-BBB	1				FF	CCC-DDD	1	FF	DDD-EEE	1
39	AAA FF BBB BBB FF CCC CCC FF DDD DDD FF EEE	FF	AAA-BBB	1	FF	BBB-CCC	1	FF	CCC-DDD	1	FF	DDD-EEE	1
40	AAA FF BBB BBB FF X CCC CCC O DDD DDD FF O EEE	FF	AAA-BBB	1	FF	BBB-CCC	1				FF	DDD-EEE	1
41	AAA FF BBB BBB FF O CCC CCC O DDD DDD FF O EEE	FF	AAA-BBB	1	FF	BBB-CCC	1				FF	DDD-EEE	1
42	AAA FF BBB BBB FF X CCC CCC X DDD DDD FF X EEE	FF	AAA-BBB	1	FF	BBB-CCC	1				FF	DDD-EEE	1
Sheet1

Cell Formulas
Range	Formula
B3:B42	B3	=IFERROR(IF(IFERROR(FIND("A FF B",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"O ",""),"X ",""),"ARNK ","")),""),"FF",""),"")
C3:C42	C3	=IF(B3="FF","AAA-BBB","")
D3:D42,G3:G42,J3:J42,M3:M42	D3	=IF(B3="FF",1,"")
E3:E42	E3	=IFERROR(IF(IFERROR(FIND("B FF C",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"O ",""),"X ",""),"ARNK ","")),""),"FF",""),"")
F3:F42	F3	=IF(E3="FF","BBB-CCC","")
H3:H42	H3	=IFERROR(IF(IFERROR(FIND("C FF D",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"O ",""),"X ",""),"ARNK ","")),""),"FF",""),"")
I3:I42	I3	=IF(H3="FF","CCC-DDD","")
K3:K42	K3	=IFERROR(IF(IFERROR(FIND("D FF E",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"O ",""),"X ",""),"ARNK ","")),""),"FF",""),"")
L3:L42	L3	=IF(K3="FF","DDD-EEE","")

 

[shansakhi]

Thank you Sanjeev.
Here Sectors are dynamic it can be anything instead of AAA-BBB it can be LHR-MAN.
But your formula had given a hint where instead of hardcoded Sector, I can use Left / Right or MID formula to achieve desired result.

 

[shansakhi]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Updated the Account details.. Thank you for suggetion.