Excel table conversion

Dag2

New Member
Joined
Jul 3, 2023
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hey everyone,

I want to take this sort of table:

Keywords_Search_Klemens.xlsm
ABCDEF
1KeyWords
21bb1
32bb2
43bb3
54bb4
65bb5cc5dd5
76bb6cc6dd6
87bb7cc7dd7
98bb8
109bb9
1110bb10
1211bb11
1312bb12cc12dd12ee12ff12
she


and convert it into this kind of table:
Keywords_Search_Klemens.xlsm
AB
17WordsKey
18bb11
19bb22
20bb33
21bb44
22bb55
23cc55
24dd55
25
she


How would I do that? Any help is appreciated :)

Best regards and thank you!
Klemens
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Solved with Power Query, Unpivot your data.

Power Query:
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
ABCDEFGHI
1KeyWordsColumn1Column2Column3Column4ValueKey
21bb1bb11
32bb2bb22
43bb3bb33
54bb4bb44
65bb5cc5dd5bb55
76bb6cc6dd6cc55
87bb7cc7dd7dd55
98bb8bb66
109bb9cc66
1110bb10dd66
1211bb11bb77
1312bb12cc12dd12ee12ff12cc77
14dd77
15bb88
16bb99
17bb1010
18bb1111
19bb1212
20cc1212
21dd1212
22ee1212
23ff1212
Sheet1
 
Upvote 1
Solution
A formula option
Fluff.xlsm
ABCDEFGHI
1KeyWords
21bb1bb11
32bb2bb22
43bb3bb33
54bb4bb44
65bb5cc5dd5bb55
76bb6cc6dd6cc55
87bb7cc7dd7dd55
98bb8bb66
109bb9cc66
1110bb10dd66
1211bb11bb77
1312bb12cc12dd12ee12ff12cc77
14dd77
15bb88
16bb99
17bb1010
18bb1111
19bb1212
20cc1212
21dd1212
22ee1212
23ff1212
Sheet3
Cell Formulas
RangeFormula
H2:I23H2=HSTACK(TOCOL(B2:F13,1),TOCOL(IF(B2:F13<>"",A2:A13,1/0),2))
Dynamic array formulas.
 
Upvote 0
Solved with Power Query, Unpivot your data.

Power Query:
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
ABCDEFGHI
1KeyWordsColumn1Column2Column3Column4ValueKey
21bb1bb11
32bb2bb22
43bb3bb33
54bb4bb44
65bb5cc5dd5bb55
76bb6cc6dd6cc55
87bb7cc7dd7dd55
98bb8bb66
109bb9cc66
1110bb10dd66
1211bb11bb77
1312bb12cc12dd12ee12ff12cc77
14dd77
15bb88
16bb99
17bb1010
18bb1111
19bb1212
20cc1212
21dd1212
22ee1212
23ff1212
Sheet1
It worked like a charm with power query. Thank you!
 
Upvote 0
A formula option
Fluff.xlsm
ABCDEFGHI
1KeyWords
21bb1bb11
32bb2bb22
43bb3bb33
54bb4bb44
65bb5cc5dd5bb55
76bb6cc6dd6cc55
87bb7cc7dd7dd55
98bb8bb66
109bb9cc66
1110bb10dd66
1211bb11bb77
1312bb12cc12dd12ee12ff12cc77
14dd77
15bb88
16bb99
17bb1010
18bb1111
19bb1212
20cc1212
21dd1212
22ee1212
23ff1212
Sheet3
Cell Formulas
RangeFormula
H2:I23H2=HSTACK(TOCOL(B2:F13,1),TOCOL(IF(B2:F13<>"",A2:A13,1/0),2))
Dynamic array formulas.
thank you for your solution as well!
 
Upvote 0
I have one more problem:
I want to delete duplicates per key in this table:
Keywords_Search_Klemens.xlsm
AB
1KeyActualCategories
21Informationssicherheit
32Informationssicherheit
43Informationssicherheit
54Vorgaben&Maßnahmen
65Informationssicherheit
75Daten&Informationen
85Informationssicherheit
96Informationssicherheit
106Daten&Informationen
116Informationssicherheit
126Informationssicherheit
136Vorgaben&Maßnahmen
146Vorgaben&Maßnahmen
MappedSource


so it looks like this:
Keywords_Search_Klemens.xlsm
FG
1KeyActualCategories
21Informationssicherheit
32Informationssicherheit
43Informationssicherheit
54Vorgaben&Maßnahmen
65Informationssicherheit
75Daten&Informationen
86Daten&Informationen
96Informationssicherheit
106Vorgaben&Maßnahmen
MappedSource


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

Best regards,
Klemens
 
Upvote 0
Do you mean like this
Fluff.xlsm
ABCDEFGHI
1KeyWords
21bb1bb11
32bb2bb22
43bb3bb33
54bb4bb44
65bb5cc5dd5bb55
76bb6cc6dd6cc55
87bb7cc7bb7dd55
98bb8bb66
109bb9cc66
1110bb10dd66
1211bb11bb77
1312bb12cc12dd12ee12ff12cc77
14bb88
15bb99
16bb1010
17bb1111
18bb1212
19cc1212
20dd1212
21ee1212
22ff1212
Sheet3
Cell Formulas
RangeFormula
H2:I22H2=UNIQUE(HSTACK(TOCOL(B2:F13,1),TOCOL(IF(B2:F13<>"",A2:A13,1/0),2)))
Dynamic array formulas.
 
Upvote 0
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
ABCDEFGHI
1KeyWords
21bb1bb11
32bb2bb22
43bb3bb33
54bb4bb44
65bb5cc5dd5bb55
76bb6cc6dd6cc55
87bb7cc7bb7dd55
98bb8bb66
109bb9cc66
1110bb10dd66
1211bb11bb77
1312bb12cc12dd12ee12ff12cc77
14bb88
15bb99
16bb1010
17bb1111
18bb1212
19cc1212
20dd1212
21ee1212
22ff1212
Sheet3
Cell Formulas
RangeFormula
H2:I22H2=UNIQUE(HSTACK(TOCOL(B2:F13,1),TOCOL(IF(B2:F13<>"",A2:A13,1/0),2)))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top