Switch data between columns on same row

[Alt F11]

I hope someone wants to help me...

I need a macro to "reorder" data in column A and B to new columns (on a new sheet) per unique value on the same row! The data below is just simplified to understand my question but in reality the data is more various than only three different single characters. Forget the collors i used in both of the Mini Sheets (Before and After), it's only necessary to reorder the data (column E/F and G/H belongs to eachother). I need this to be able to select data for a line graph where all samples of the same species are placed in the same columns. In reality it will not be two columns before the macro is executed, but perhaps five to ten, so the output is most likely also more than three columns to... ?

Thank you in advance!

	Before					After
	A	B	Etc.			A	B	C	Etc.
	...	...			Columnheaders ->​	x	y	z
1	x	y			1	x	y
2	x	z			2	x		z
3	y	z			3		y	z
4	z	x			4	x		z
5	z	x			5	x		z
6	y	z			6		y	z

Table_View_Data_II.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		Time	Serving Cell DL EARFCN_Serving Cell Identity	Serving Cell RSRP (dBm)	Neighbor Cell DL EARFCN: N1_Neighbor Cell Identity: N1	Neighbor Cell RSRP (dBm): N1	Neighbor Cell DL EARFCN: N2_Neighbor Cell Identity: N2	Neighbor Cell RSRP (dBm): N2
2		11:30:27.000	2850_61	-106.4	2994_32	-105.0	100_44	-110.1
3		11:30:29.000	2850_61	-106.7	2994_32	-109.1	100_44	-111.0
4		11:30:31.000	2850_61	-106.6	2994_32	-110.0	100_44	-112.0
5		11:30:33.000	2850_61	-106.3	2994_32	-105.0	100_44	-111.2
6		11:30:35.000	2850_61	-106.5	2994_32	-110.2	100_44	-111.3
7		11:30:37.000	2850_61	-106.7	1500_151	-101.6	2994_32	-104.2
8		11:30:39.000	2850_61	-107.1	2994_32	-102.7	1500_151	-109.5
9		11:30:41.000	2850_61	-106.4	2994_32	-110.3	100_44	-111.6
10		11:30:43.000	2850_61	-106.1	2850_158	-110.4	2994_32	-111.1
11		11:30:45.000	2850_61	-106.3	1500_151	-107.1	2994_32	-110.1
12		11:30:47.000	2850_61	-106.3	1500_151	-102.9	2994_32	-112.0
13		11:30:49.000	2850_61	-103.2	_		_
14		11:30:51.000	2850_61	-102.2	_		_
15		11:30:53.000	2850_61	-102.5	_		_
16		11:30:55.000	2850_61	-102.5	_		_
17		11:30:57.000	2850_61	-102.2	_		_
18		11:30:59.000	2850_61	-102.5	_		_
19		11:31:01.000	2850_61	-102.1	_		_
20		11:31:03.000	2850_61	-102.1	_		_
21		11:31:05.000	2850_61	-102.0	_		_
22		11:31:07.000	2850_61	-101.4	_		_
23		11:31:09.000	2850_61	-101.2	_		_
24		11:31:11.000	2850_61	-102.5	_		_
25		11:31:13.000	2850_61	-103.1	_		_
26		11:31:15.000	2850_61	-102.9	_		_
27		11:31:17.000	2850_61	-103.1	_		_
28		11:31:19.000	2850_61	-103.0	_		_
29		11:31:21.000	2850_61	-102.3	_		_
Before

Table_View_Data_II.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		Time	Serving Cell DL EARFCN_Serving Cell Identity	Serving Cell RSRP (dBm)	2994_32		100_44		1500_151		2850_158
2		11:30:27.000	2850_61	-106.4	2994_32	-105.0	100_44	-110.1
3		11:30:29.000	2850_61	-106.7	2994_32	-109.1	100_44	-111.0
4		11:30:31.000	2850_61	-106.6	2994_32	-110.0	100_44	-112.0
5		11:30:33.000	2850_61	-106.3	2994_32	-105.0	100_44	-111.2
6		11:30:35.000	2850_61	-106.5	2994_32	-110.2	100_44	-111.3
7		11:30:37.000	2850_61	-106.7	2994_32	-104.2			1500_151	-101.6
8		11:30:39.000	2850_61	-107.1	2994_32	-102.7			1500_151	-109.5
9		11:30:41.000	2850_61	-106.4	2994_32	-110.3	100_44	-111.6
10		11:30:43.000	2850_61	-106.1	2994_32	-111.1					2850_158	-110.4
11		11:30:45.000	2850_61	-106.3	2994_32	-110.1			1500_151	-107.1
12		11:30:47.000	2850_61	-106.3	2994_32	-112.0			1500_151	-102.9
13		11:30:49.000	2850_61	-103.2	_		_
14		11:30:51.000	2850_61	-102.2	_		_
15		11:30:53.000	2850_61	-102.5	_		_
16		11:30:55.000	2850_61	-102.5	_		_
17		11:30:57.000	2850_61	-102.2	_		_
18		11:30:59.000	2850_61	-102.5	_		_
19		11:31:01.000	2850_61	-102.1	_		_
20		11:31:03.000	2850_61	-102.1	_		_
21		11:31:05.000	2850_61	-102.0	_		_
22		11:31:07.000	2850_61	-101.4	_		_
23		11:31:09.000	2850_61	-101.2	_		_
24		11:31:11.000	2850_61	-102.5	_		_
25		11:31:13.000	2850_61	-103.1	_		_
26		11:31:15.000	2850_61	-102.9	_		_
27		11:31:17.000	2850_61	-103.1	_		_
28		11:31:19.000	2850_61	-103.0	_		_
29		11:31:21.000	2850_61	-102.3	_		_
After

 

[coulombevin]

Hi Alt F11,

Why use vba instead of a formula? If you can use both, I would choose only formula like so:

Classeur1
	A	B	C	D	E	F	G	H
1		Time	Serving Cell DL EARFCN_Serving Cell Identity	Serving Cell RSRP (dBm)	Neighbor Cell DL EARFCN: N1_Neighbor Cell Identity: N1	Neighbor Cell RSRP (dBm): N1	Neighbor Cell DL EARFCN: N2_Neighbor Cell Identity: N2	Neighbor Cell RSRP (dBm): N2
2		11:30:27.000	2850_61	-106.400001525879	2994_32	-105	100_44	-110.099998474121
3		11:30:29.000	2850_61	-106.699996948242	2994_32	-109.099998474121	100_44	-111
4		11:30:31.000	2850_61	-106.599998474121	2994_32	-110	100_44	-112
5		11:30:33.000	2850_61	-106.300003051758	2994_32	-105	100_44	-111.199996948242
6		11:30:35.000	2850_61	-106.5	2994_32	-110.199996948242	100_44	-111.300003051758
7		11:30:37.000	2850_61	-106.699996948242	1500_151	-101.599998474121	2994_32	-104.199996948242
8		11:30:39.000	2850_61	-107.099998474121	2994_32	-102.699996948242	1500_151	-109.5
9		11:30:41.000	2850_61	-106.400001525879	2994_32	-110.300003051758	100_44	-111.599998474121
10		11:30:43.000	2850_61	-106.099998474121	2850_158	-110.400001525879	2994_32	-111.099998474121
11		11:30:45.000	2850_61	-106.300003051758	1500_151	-107.099998474121	2994_32	-110.099998474121
12		11:30:47.000	2850_61	-106.300003051758	1500_151	-102.900001525879	2994_32	-112
13		11:30:49.000	2850_61	-103.199996948242	_		_
14		11:30:51.000	2850_61	-102.199996948242	_		_
15		11:30:53.000	2850_61	-102.5	_		_
16		11:30:55.000	2850_61	-102.5	_		_
17		11:30:57.000	2850_61	-102.199996948242	_		_
18		11:30:59.000	2850_61	-102.5	_		_
19		11:31:01.000	2850_61	-102.099998474121	_		_
20		11:31:03.000	2850_61	-102.099998474121	_		_
21		11:31:05.000	2850_61	-102	_		_
22		11:31:07.000	2850_61	-101.400001525879	_		_
23		11:31:09.000	2850_61	-101.199996948242	_		_
24		11:31:11.000	2850_61	-102.5	_		_
25		11:31:13.000	2850_61	-103.099998474121	_		_
26		11:31:15.000	2850_61	-102.900001525879	_		_
27		11:31:17.000	2850_61	-103.099998474121	_		_
28		11:31:19.000	2850_61	-103	_		_
29		11:31:21.000	2850_61	-102.300003051758	_		_
before

Classeur1
	A	B	C	D	E	F	G	H
1		Time	Serving Cell DL EARFCN_Serving Cell Identity	Serving Cell RSRP (dBm)	2994_32	1500_151	2850_158	100_44
2		11:30:27.000	2850_61	-106.400001525879	-105			-110.099998474121
3		11:30:29.000	2850_61	-106.699996948242	-109.099998474121			-111
4		11:30:31.000	2850_61	-106.599998474121	-110			-112
5		11:30:33.000	2850_61	-106.300003051758	-105			-111.199996948242
6		11:30:35.000	2850_61	-106.5	-110.199996948242			-111.300003051758
7		11:30:37.000	2850_61	-106.699996948242	-104.199996948242	-101.599998474121
8		11:30:39.000	2850_61	-107.099998474121	-102.699996948242	-109.5
9		11:30:41.000	2850_61	-106.400001525879	-110.300003051758			-111.599998474121
10		11:30:43.000	2850_61	-106.099998474121	-111.099998474121		-110.400001525879
11		11:30:45.000	2850_61	-106.300003051758	-110.099998474121	-107.099998474121
12		11:30:47.000	2850_61	-106.300003051758	-112	-102.900001525879
13		11:30:49.000	2850_61	-103.199996948242
14		11:30:51.000	2850_61	-102.199996948242
15		11:30:53.000	2850_61	-102.5
16		11:30:55.000	2850_61	-102.5
17		11:30:57.000	2850_61	-102.199996948242
18		11:30:59.000	2850_61	-102.5
19		11:31:01.000	2850_61	-102.099998474121
20		11:31:03.000	2850_61	-102.099998474121
21		11:31:05.000	2850_61	-102
22		11:31:07.000	2850_61	-101.400001525879
23		11:31:09.000	2850_61	-101.199996948242
24		11:31:11.000	2850_61	-102.5
25		11:31:13.000	2850_61	-103.099998474121
26		11:31:15.000	2850_61	-102.900001525879
27		11:31:17.000	2850_61	-103.099998474121
28		11:31:19.000	2850_61	-103
29		11:31:21.000	2850_61	-102.300003051758
after

Cell Formulas
Range	Formula
B1:D29	B1	=before!B1:D29
E1:H1	E1	=UNIQUE(TEXTSPLIT(TEXTJOIN(";",TRUE,FILTER(before!E2:E29,before!E2:E29<>"_"),FILTER(before!G2:G29,before!G2:G29<>"_")),";",,TRUE),TRUE)
E2:H29	E2	=IFERROR(INDEX(before!$E$1:$H$29,ROW(),MATCH(E$1,before!$E2:$H2,0)+1),"")
Dynamic array formulas.

HOW DOES IT WORK??

In befor sheet, you can copy/paste your cells with time, serving cell DL and RSRP or with this:

=before!B1:D29

Next, we have the unique header that we get with this function:

=UNIQUE(TEXTSPLIT(TEXTJOIN(";",TRUE,FILTER(before!E2:E29,before!E2:E29<>"_"),FILTER(before!G2:G29,before!G2:G29<>"_")),";",,TRUE),TRUE)

Finaly, in each cell with a header and a time, you can put this one and drag it accross the table. it will try to find the header value for the specific row the cell is at, and return the column number. Then we offset with the +1 and the index function is going to get the value of that specific cell. If nothing is found, then it will be empty ("").

=IFERROR(INDEX(before!$E$1:$H$29,ROW(),MATCH(E$1,before!$E2:$H2,0)+1),"")

Bests regards,

Vincent