Data form several columns to one

[ayyaaa]

Hi,

I have data in excel sheet in several columns (lists are flexible):

s1

s2

s3

s4

s5

s6

s7

s8

s9

s10

s11

s12

s13

s14

s15

s16

s17

s18

s19

s20

s21

s22

s23

tr1

iuhu1

e8zu9

uzsiu4

erd1

wef5

ew6

wefw7

wefe3

erg8

3wef3

efwe8

rge9

ef1

erf6

wdq5

ui9

fgr6

r4

reg4

erg3

ef6

ed4

tr2

iuhu2

e8zu10

uzsiu5

erd2

wef6

ew7

wefw8

wefe4

erg9

3wef4

efwe9

rge10

ef2

erf7

wdq6

ui10

fgr7

r5

reg5

erg4

ef7

ed5

tr3

iuhu3

e8zu11

uzsiu6

erd3

wef7

ew8

wefw9

wefe5

erg10

3wef5

efwe10

rge11

ef3

erf8

wdq7

ui11

fgr8

r6

reg6

erg5

ef8

ed6

tr4

iuhu4

e8zu12

uzsiu7

erd4

wef8

ew9

wefw10

wefe6

erg11

3wef6

efwe11

rge12

ef4

erf9

wdq8

ui12

fgr9

r7

reg7

erg6

ef9

ed7

tr5

iuhu5

e8zu13

uzsiu8

erd5

wef9

ew10

wefw11

wefe7

erg12

3wef7

efwe12

rge13

ef5

erf10

wdq9

ui13

fgr10

r8

reg8

erg7

ef10

ed8

tr6

iuhu6

e8zu14

uzsiu9

erd6

wef10

ew11

wefw12

wefe8

erg13

3wef8

efwe13

rge14

ef6

erf11

wdq10

ui14

fgr11

r9

reg9

erg8

ef11

ed9

tr7

iuhu7

e8zu15

uzsiu10

erd7

wef11

ew12

wefw13

wefe9

erg14

3wef9

efwe14

rge15

ef7

erf12

wdq11

ui15

fgr12

r10

reg10

erg9

ef12

ed10

tr8

iuhu8

e8zu16

uzsiu11

erd8

wef12

ew13

wefw14

wefe10

erg15

3wef10

efwe15

rge16

ef8

erf13

wdq12

ui16

fgr13

r11

reg11

erg10

ef13

ed11

tr9

iuhu9

e8zu17

uzsiu12

erd9

wef13

ew14

wefw15

wefe11

erg16

3wef11

efwe16

rge17

ef9

erf14

wdq13

ui17

fgr14

r12

reg12

erg11

ef14

ed12

tr10

iuhu10

e8zu18

uzsiu13

erd10

ew15

wefw16

erg17

3wef12

rge18

ef10

erf15

wdq14

ui18

fgr15

r13

reg13

ef15

ed13

tr11

iuhu11

e8zu19

uzsiu14

erd11

ew16

wefw17

erg18

3wef13

rge19

ef11

erf16

wdq15

ui19

fgr16

r14

reg14

ef16

ed14

tr12

iuhu12

e8zu20

uzsiu15

ew17

wefw18

erg19

3wef14

rge20

ef12

erf17

wdq16

ui20

fgr17

reg15

ef17

ed15

tr13

iuhu13

e8zu21

uzsiu16

wefw19

erg20

3wef15

ef13

erf18

wdq17

fgr18

reg16

ef18

ed16

tr14

iuhu14

e8zu22

uzsiu17

wefw20

erg21

3wef16

ef14

erf19

wdq18

fgr19

reg17

ef19

ed17

tr15

e8zu23

uzsiu18

wefw21

erg22

3wef17

ef15

erf20

wdq19

fgr20

reg18

ef20

ed18

tr16

e8zu24

uzsiu19

wefw22

erg23

ef16

erf21

wdq20

fgr21

reg19

ef21

ed19

tr17

e8zu25

uzsiu20

wefw23

erg24

ef17

erf22

wdq21

fgr22

reg20

ef22

ed20

tr18

e8zu26

uzsiu21

erg25

ef18

erf23

wdq22

fgr23

ef23

ed21

tr19

e8zu27

uzsiu22

erg26

ef19

erf24

wdq23

ef24

ed22

tr20

e8zu28

uzsiu23

erg27

ef25

ed23

tr21

e8zu29

uzsiu24

erg28

ef26

ed24

tr22

e8zu30

uzsiu25

erg29

ef27

ed25

tr23

e8zu31

erg30

tr24

e8zu32

erg31

tr25

e8zu33

e8zu34

e8zu35

e8zu36

<tbody>
</tbody>

So I need pivot table to join these data from columns

to one column, sorted by each column - not all a in alphabetical order. How can I do this?

Thanks in advance for help.
BW Aya

 

[Rickmaurinus]

Hi!

The perfect tool to do this is Power Query in Excel.
Have a look at attached file: https://expirebox.com/download/7c758477f75cfde4a5bb71810bf186a9.html

It makes your data into a table. Then it pulls into Power Query (go to tab Data, the group is called "Get & Transform" in excel 2016, then you click from table).
In Power Query I have added an Index column. Then I unpivoted all columns except the Index column. After I sorted the column on the Header, and after that on the Index Number. Lastly I merged the two columns, so you still have all the information you need.

In this way your data will look as you described. It will be in one column.

Regards,
Rick