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

 

[Alt F11]

Hi Vincent,, thank you so much for your reaction! I'll wil try this and for me it seems possible to integrate this to our post-procesflow. I'll inform you later about this... okay?

BR,

Aron

 

[Peter_SSs]

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)

Problem with this part is that TEXTSPLIT is not available in Excel 2021 which is the OP's version.

 

[Alt F11]

That's a shame, it is indeed true that this function is not available in v 2021. Thank you very much for your response Peter, it will save me a lot of time.

 

[kvsrinivasamurthy]

Formula in E1

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

can be replaced by

=TRANSPOSE(UNIQUE(VSTACK(FILTER(E2:E29,E2:E29<>"_"),FILTER(G2:G29,G2:G29<>"_"))))

to avoid Textsplit and Textjoin functions.

 

[Peter_SSs]

to avoid Textsplit and Textjoin functions.

It isn't much use avoiding a function that isn't available in the OP's version if you then use a different one that is also not available in that version.

 

[coulombevin]

Problem with this part is that TEXTSPLIT is not available in Excel 2021 which is the OP's version.

Dear lord forsakeen user manual of microsoft .... I always fall for the Aplies To filled with false information. Unfortunately, the only way I figured out to work around implies to use an inserted row above the table:

In E1, I would use a formula that list every possible id sample with ";" at the end:

=TEXTJOIN(";",TRUE,UNIQUE(FILTER(Before!$E$2:$E$29,Before!$E$2:$E$29<>"_")),UNIQUE(FILTER(Before!$G$2:$G$29,Before!$G$2:$G$29<>"_")))&";"

In E2, I would select the left of that text until I hit the first ";"

IF(E1="","",LEFT(E1,FIND(";",E1)-1))

Then in F1 (next to the long text) I would substitue "id;" of the previous sample from the long text

=SUBSTITUTE(E1,E2&";","")

And in F2, I would do the same as in E2 and so on. Under the row 2, (row 3 and more) I would put this formula to grab the data associated with the id for the right time

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

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

Cell Formulas
Range	Formula
E1	E1	=TEXTJOIN(";",TRUE,UNIQUE(FILTER(Before!$E$2:$E$29,Before!$E$2:$E$29<>"_")),UNIQUE(FILTER(Before!$G$2:$G$29,Before!$G$2:$G$29<>"_")))&";"
F1:J1	F1	=SUBSTITUTE(E1,E2&";","")
E2:J2	E2	=IF(E1="","",LEFT(E1,FIND(";",E1)-1))
B2:D30	B2	=Before!B1:D29
E3:G30	E3	=IFERROR(INDEX(Before!$E$1:$H$29,ROW(),MATCH(E$2,Before!$E2:$H2,0)+1),"")
Dynamic array formulas.

Hope it will help,

Vincent

 

[Peter_SSs]

Some other formula options.
1. Put the formulas in J2:K2 and copy down. Those two columns of formulas can then be copied and pasted to L2, N2, P2 etc

Alt F11.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q
1						2994_32		100_44		1500_151		2850_158
2	2994_32	-105	100_44	-110.1		2994_32	-105	100_44	-110.1
3	2994_32	-109.1	100_44	-111		2994_32	-109.1	100_44	-111
4	2994_32	-110	100_44	-112		2994_32	-110	100_44	-112
5	2994_32	-105	100_44	-111.2		2994_32	-105	100_44	-111.2
6	2994_32	-110.2	100_44	-111.3		2994_32	-110.2	100_44	-111.3
7	1500_151	-101.6	2994_32	-104.2		2994_32	-104.2			1500_151	-101.6
8	2994_32	-102.7	1500_151	-109.5		2994_32	-102.7			1500_151	-109.5
9	2994_32	-110.3	100_44	-111.6		2994_32	-110.3	100_44	-111.6
10	2850_158	-110.4	2994_32	-111.1		2994_32	-111.1					2850_158	-110.4
11	1500_151	-107.1	2994_32	-110.1		2994_32	-110.1			1500_151	-107.1
12	1500_151	-102.9	2994_32	-112		2994_32	-112			1500_151	-102.9
13	_		_
14	_		_
15	_		_
16	_		_
17	_		_
18	_		_
19	_		_
20	_		_
21	_		_
22	_		_
23	_		_
24	_		_
25	_		_
26	_		_
27	_		_
28	_		_
29	_		_
Sheet3

Cell Formulas
Range	Formula
J2:J29,L2:L29,N2:N29,P2:P29	J2	=IFNA(INDEX($E2:$H2,MATCH(J$1,$E2:$H2,0)),"")
K2:K29,M2:M29,O2:O29,Q2:Q29	K2	=IF(J2="","",INDEX($E2:$H2,MATCH(J$1,$E2:$H2,0)+1))

2. Using the same formula (but longer) in all cells.

Alt F11.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q
1						2994_32		100_44		1500_151		2850_158
2	2994_32	-105	100_44	-110.1		2994_32	-105	100_44	-110.1
3	2994_32	-109.1	100_44	-111		2994_32	-109.1	100_44	-111
4	2994_32	-110	100_44	-112		2994_32	-110	100_44	-112
5	2994_32	-105	100_44	-111.2		2994_32	-105	100_44	-111.2
6	2994_32	-110.2	100_44	-111.3		2994_32	-110.2	100_44	-111.3
7	1500_151	-101.6	2994_32	-104.2		2994_32	-104.2			1500_151	-101.6
8	2994_32	-102.7	1500_151	-109.5		2994_32	-102.7			1500_151	-109.5
9	2994_32	-110.3	100_44	-111.6		2994_32	-110.3	100_44	-111.6
10	2850_158	-110.4	2994_32	-111.1		2994_32	-111.1					2850_158	-110.4
11	1500_151	-107.1	2994_32	-110.1		2994_32	-110.1			1500_151	-107.1
12	1500_151	-102.9	2994_32	-112		2994_32	-112			1500_151	-102.9
13	_		_
14	_		_
15	_		_
16	_		_
17	_		_
18	_		_
19	_		_
20	_		_
21	_		_
22	_		_
23	_		_
24	_		_
25	_		_
26	_		_
27	_		_
28	_		_
29	_		_
Sheet4

Cell Formulas
Range	Formula
J2:Q29	J2	=IFNA(INDEX($E2:$H2,MATCH(LOOKUP("zzz",$J$1:J$1),$E2:$H2,0)+MOD(COLUMNS($J:J)-1,2)),"")

Both options above assume the headings in J1 & to the right are given. If they are required by formulas as well, some different (but a bit longer longer) options would be available