Rota creation

[Paul at GTS]

Hi,

I have the data as below which shows personel availability for 5/1/2020, I would like to create a column with concurrent data for personnel that do not have status as "Away"

Book1
	W	X
22	Sunday Away	05/01/2020
23	A	Away
24	B
25	C
26	D	Away
27	E
28	F
29	G
30	H	Away
31	I
32	J
33	K	Away
34	L
35	M
36	N
Table Build

Concurrent data as below :

Book1
	X
39	05/01/2020
40	B
41	C
42	E
43	F
44	G
45	I
46	J
Table Build

Any ideas ?

 

[Joe4]

Have you considered using Filters to filter in/out the values you want?

 

[Paul at GTS]

Thanks Joe4

I tried filtering but due to the way the raw data is supplied if I apply a filter all subsequent columns are affected which is not what I need. Below is how I would like the data to behave :

Book1
	W	X	Y	Z
21	Away Dates
22		05/01/2020	12/01/2020	19/01/2020
23	A	Away	Away
24	B
25	C
26	D	Away
27	E
28	F			Away
29	G
30	H	Away	Away
31	I
32	J
33	K	Away
34	L
35	M
36	N
37
38	Whos Available
39		05/01/2020	12/01/2020	19/01/2020
40		B	B	A
41		C	C	B
42		E	D	C
43		F	E	D
44		G	F	E
45		I	G	G
46		J	I	H
47		L	J	I
48		M	K	J
49		N	L	K
50			M	L
51			N	M
52				N
Table Build

<table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" >

 

[Joe4]

Maybe something like this will work: https://exceljet.net/formula/extract-all-matches-with-helper-column

 

[Paul at GTS]

Thanks Joe4,

I'll give it a go, looks interesting.