Excel table conversion

[Dag2]

Hey everyone,

I want to take this sort of table:

Keywords_Search_Klemens.xlsm
	A	B	C	D	E	F
1	Key	Words
2	1	bb1
3	2	bb2
4	3	bb3
5	4	bb4
6	5	bb5	cc5	dd5
7	6	bb6	cc6	dd6
8	7	bb7	cc7	dd7
9	8	bb8
10	9	bb9
11	10	bb10
12	11	bb11
13	12	bb12	cc12	dd12	ee12	ff12
she

and convert it into this kind of table:

Keywords_Search_Klemens.xlsm
	A	B
17	Words	Key
18	bb1	1
19	bb2	2
20	bb3	3
21	bb4	4
22	bb5	5
23	cc5	5
24	dd5	5
25	…	…
she

How would I do that? Any help is appreciated

Best regards and thank you!
Klemens

 

[alansidman]

Solved with Power Query, Unpivot your data.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Key"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Value", "Key"})
in
    #"Reordered Columns"

Book7
	A	B	C	D	E	F	G	H	I
1	Key	Words	Column1	Column2	Column3	Column4		Value	Key
2	1	bb1						bb1	1
3	2	bb2						bb2	2
4	3	bb3						bb3	3
5	4	bb4						bb4	4
6	5	bb5	cc5	dd5				bb5	5
7	6	bb6	cc6	dd6				cc5	5
8	7	bb7	cc7	dd7				dd5	5
9	8	bb8						bb6	6
10	9	bb9						cc6	6
11	10	bb10						dd6	6
12	11	bb11						bb7	7
13	12	bb12	cc12	dd12	ee12	ff12		cc7	7
14								dd7	7
15								bb8	8
16								bb9	9
17								bb10	10
18								bb11	11
19								bb12	12
20								cc12	12
21								dd12	12
22								ee12	12
23								ff12	12
Sheet1

 

[Fluff]

A formula option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Key	Words
2	1	bb1						bb1	1
3	2	bb2						bb2	2
4	3	bb3						bb3	3
5	4	bb4						bb4	4
6	5	bb5	cc5	dd5				bb5	5
7	6	bb6	cc6	dd6				cc5	5
8	7	bb7	cc7	dd7				dd5	5
9	8	bb8						bb6	6
10	9	bb9						cc6	6
11	10	bb10						dd6	6
12	11	bb11						bb7	7
13	12	bb12	cc12	dd12	ee12	ff12		cc7	7
14								dd7	7
15								bb8	8
16								bb9	9
17								bb10	10
18								bb11	11
19								bb12	12
20								cc12	12
21								dd12	12
22								ee12	12
23								ff12	12
Sheet3

Cell Formulas
Range	Formula
H2:I23	H2	=HSTACK(TOCOL(B2:F13,1),TOCOL(IF(B2:F13<>"",A2:A13,1/0),2))
Dynamic array formulas.

 

[Dag2]

Solved with Power Query, Unpivot your data.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Key"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Value", "Key"})
in
    #"Reordered Columns"

Book7
	A	B	C	D	E	F	G	H	I
1	Key	Words	Column1	Column2	Column3	Column4		Value	Key
2	1	bb1						bb1	1
3	2	bb2						bb2	2
4	3	bb3						bb3	3
5	4	bb4						bb4	4
6	5	bb5	cc5	dd5				bb5	5
7	6	bb6	cc6	dd6				cc5	5
8	7	bb7	cc7	dd7				dd5	5
9	8	bb8						bb6	6
10	9	bb9						cc6	6
11	10	bb10						dd6	6
12	11	bb11						bb7	7
13	12	bb12	cc12	dd12	ee12	ff12		cc7	7
14								dd7	7
15								bb8	8
16								bb9	9
17								bb10	10
18								bb11	11
19								bb12	12
20								cc12	12
21								dd12	12
22								ee12	12
23								ff12	12
Sheet1

It worked like a charm with power query. Thank you!

 

[Dag2]

A formula option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Key	Words
2	1	bb1						bb1	1
3	2	bb2						bb2	2
4	3	bb3						bb3	3
5	4	bb4						bb4	4
6	5	bb5	cc5	dd5				bb5	5
7	6	bb6	cc6	dd6				cc5	5
8	7	bb7	cc7	dd7				dd5	5
9	8	bb8						bb6	6
10	9	bb9						cc6	6
11	10	bb10						dd6	6
12	11	bb11						bb7	7
13	12	bb12	cc12	dd12	ee12	ff12		cc7	7
14								dd7	7
15								bb8	8
16								bb9	9
17								bb10	10
18								bb11	11
19								bb12	12
20								cc12	12
21								dd12	12
22								ee12	12
23								ff12	12
Sheet3

Cell Formulas
Range	Formula
H2:I23	H2	=HSTACK(TOCOL(B2:F13,1),TOCOL(IF(B2:F13<>"",A2:A13,1/0),2))
Dynamic array formulas.

thank you for your solution as well!

 

[Dag2]

I have one more problem:
I want to delete duplicates per key in this table:

Keywords_Search_Klemens.xlsm
	A	B
1	Key	ActualCategories
2	1	Informationssicherheit
3	2	Informationssicherheit
4	3	Informationssicherheit
5	4	Vorgaben&Maßnahmen
6	5	Informationssicherheit
7	5	Daten&Informationen
8	5	Informationssicherheit
9	6	Informationssicherheit
10	6	Daten&Informationen
11	6	Informationssicherheit
12	6	Informationssicherheit
13	6	Vorgaben&Maßnahmen
14	6	Vorgaben&Maßnahmen
MappedSource

so it looks like this:

Keywords_Search_Klemens.xlsm
	F	G
1	Key	ActualCategories
2	1	Informationssicherheit
3	2	Informationssicherheit
4	3	Informationssicherheit
5	4	Vorgaben&Maßnahmen
6	5	Informationssicherheit
7	5	Daten&Informationen
8	6	Daten&Informationen
9	6	Informationssicherheit
10	6	Vorgaben&Maßnahmen
MappedSource

I tried doing it with power query, but couldn`t find a solution.

Best regards,
Klemens

 

[Fluff]

Do you mean like this

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Key	Words
2	1	bb1						bb1	1
3	2	bb2						bb2	2
4	3	bb3						bb3	3
5	4	bb4						bb4	4
6	5	bb5	cc5	dd5				bb5	5
7	6	bb6	cc6	dd6				cc5	5
8	7	bb7	cc7	bb7				dd5	5
9	8	bb8						bb6	6
10	9	bb9						cc6	6
11	10	bb10						dd6	6
12	11	bb11						bb7	7
13	12	bb12	cc12	dd12	ee12	ff12		cc7	7
14								bb8	8
15								bb9	9
16								bb10	10
17								bb11	11
18								bb12	12
19								cc12	12
20								dd12	12
21								ee12	12
22								ff12	12
Sheet3

Cell Formulas
Range	Formula
H2:I22	H2	=UNIQUE(HSTACK(TOCOL(B2:F13,1),TOCOL(IF(B2:F13<>"",A2:A13,1/0),2)))
Dynamic array formulas.

 

[Dag2]

No, but maybe I`ll just open a new thread, since this is a different issue, which has nothing to do with the first. The first issue is resolved.

Do you mean like this

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Key	Words
2	1	bb1						bb1	1
3	2	bb2						bb2	2
4	3	bb3						bb3	3
5	4	bb4						bb4	4
6	5	bb5	cc5	dd5				bb5	5
7	6	bb6	cc6	dd6				cc5	5
8	7	bb7	cc7	bb7				dd5	5
9	8	bb8						bb6	6
10	9	bb9						cc6	6
11	10	bb10						dd6	6
12	11	bb11						bb7	7
13	12	bb12	cc12	dd12	ee12	ff12		cc7	7
14								bb8	8
15								bb9	9
16								bb10	10
17								bb11	11
18								bb12	12
19								cc12	12
20								dd12	12
21								ee12	12
22								ff12	12
Sheet3

Cell Formulas
Range	Formula
H2:I22	H2	=UNIQUE(HSTACK(TOCOL(B2:F13,1),TOCOL(IF(B2:F13<>"",A2:A13,1/0),2)))
Dynamic array formulas.

 

[Fluff]

Ok