Rearranging Data in Excel

A

astout920

New Member
Joined
Apr 21, 2014
Messages
5
Hi, all. I have a table where I need to rearrange the data. The below is a small sample size. The real table is about 100 rows and 700 columns. The first table is what it looks like now, and the second table is what I'd like it to look like. Is there an easy way to do this. I tried pivot tables but it wasn't working right (for me). Would MS Access be a better tool? Although I'm not sure how to build the right query.

Any help would be greatly appreciated!!! Thanks!!!

Current Input
[TABLE="width: 356"]
<tbody>[TR]
[TD="align: center"]Qualifier[/TD]
[TD="align: center"]Model 1[/TD]
[TD="align: center"]Model 2[/TD]
[TD="align: center"]Model 3[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.2[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.1[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]0.4[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"]0.1[/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.6[/TD]
[TD="align: center"]0.1[/TD]
[/TR]
[TR]
[TD="align: center"]E[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0.3[/TD]
[TD="align: center"]0.5[/TD]
[/TR]
</tbody>[/TABLE]


Desired Output (the headers in the top row can be any text as far as I'm concerned)
[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: center"]Model[/TD]
[TD="class: xl65, width: 64, align: center"]Qualifier[/TD]
[TD="class: xl65, width: 64, align: center"]Value[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 1[/TD]
[TD="class: xl65, align: center"]A[/TD]
[TD="class: xl65, align: center"]0.1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 1[/TD]
[TD="class: xl65, align: center"]B[/TD]
[TD="class: xl65, align: center"]0.5[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 1[/TD]
[TD="class: xl65, align: center"]C[/TD]
[TD="class: xl65, align: center"]0.4[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 1[/TD]
[TD="class: xl65, align: center"]D[/TD]
[TD="class: xl65, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 1[/TD]
[TD="class: xl65, align: center"]E[/TD]
[TD="class: xl65, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 2[/TD]
[TD="class: xl65, align: center"]A[/TD]
[TD="class: xl65, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 2[/TD]
[TD="class: xl65, align: center"]B[/TD]
[TD="class: xl65, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 2[/TD]
[TD="class: xl65, align: center"]C[/TD]
[TD="class: xl65, align: center"]0.1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 2[/TD]
[TD="class: xl65, align: center"]D[/TD]
[TD="class: xl65, align: center"]0.6[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 2[/TD]
[TD="class: xl65, align: center"]E[/TD]
[TD="class: xl65, align: center"]0.3[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 3[/TD]
[TD="class: xl65, align: center"]A[/TD]
[TD="class: xl65, align: center"]0.2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 3[/TD]
[TD="class: xl65, align: center"]B[/TD]
[TD="class: xl65, align: center"]0.1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 3[/TD]
[TD="class: xl65, align: center"]C[/TD]
[TD="class: xl65, align: center"]0.1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 3[/TD]
[TD="class: xl65, align: center"]D[/TD]
[TD="class: xl65, align: center"]0.1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]Model 3[/TD]
[TD="class: xl65, align: center"]E[/TD]
[TD="class: xl65, align: center"]0.5[/TD]
[/TR]
</tbody>[/TABLE]

​
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
It looks like what you are trying to do is called unpivot. If you have excel 2016+ you can use the built in power query to do this I think -- I've never done it, but you should be able to google it. If you are using an older version of excel (2007+) you can use the free nutilities add-in from iwishexcel.com. once installed, the nutility you are looking for is also called unpivot. The pic below shows the instructions from the nutilities menu.

3Yyj2Gv

3Yyj2Gv
https://ibb.co/3Yyj2Gv
 
Upvote 0
or with PowerQuery aka Get&Transform

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Qualifier[/td][td=bgcolor:#5B9BD5]Model 1[/td][td=bgcolor:#5B9BD5]Model 2[/td][td=bgcolor:#5B9BD5]Model 3[/td][td][/td][td=bgcolor:#70AD47]Model[/td][td=bgcolor:#70AD47]Qualifier[/td][td=bgcolor:#70AD47]Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]A[/td][td=bgcolor:#DDEBF7]
0.1​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
0.2​
[/td][td][/td][td=bgcolor:#E2EFDA]Model 1[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]
0.1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]
0.5​
[/td][td][/td][td]
0.1​
[/td][td][/td][td]Model 1[/td][td]B[/td][td]
0.5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]C[/td][td=bgcolor:#DDEBF7]
0.4​
[/td][td=bgcolor:#DDEBF7]
0.1​
[/td][td=bgcolor:#DDEBF7]
0.1​
[/td][td][/td][td=bgcolor:#E2EFDA]Model 1[/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]
0.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]D[/td][td][/td][td]
0.6​
[/td][td]
0.1​
[/td][td][/td][td]Model 1[/td][td]D[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]E[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
0.3​
[/td][td=bgcolor:#DDEBF7]
0.5​
[/td][td][/td][td=bgcolor:#E2EFDA]Model 1[/td][td=bgcolor:#E2EFDA]E[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]Model 2[/td][td]A[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Model 2[/td][td=bgcolor:#E2EFDA]B[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]Model 2[/td][td]C[/td][td]
0.1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Model 2[/td][td=bgcolor:#E2EFDA]D[/td][td=bgcolor:#E2EFDA]
0.6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]Model 2[/td][td]E[/td][td]
0.3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Model 3[/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]
0.2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]Model 3[/td][td]B[/td][td]
0.1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Model 3[/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]
0.1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]Model 3[/td][td]D[/td][td]
0.1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Model 3[/td][td=bgcolor:#E2EFDA]E[/td][td=bgcolor:#E2EFDA]
0.5​
[/td][/tr]
[/table]

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ReplaceNull = Table.ReplaceValue(Source,null,999,Replacer.ReplaceValue,{"Model 1", "Model 2", "Model 3"}),
    Unpivot = Table.UnpivotOtherColumns(ReplaceNull, {"Qualifier"}, "Attribute", "Value"),
    Replace = Table.ReplaceValue(Unpivot,999,null,Replacer.ReplaceValue,{"Value"}),
    Sort = Table.Sort(Replace,{{"Attribute", Order.Ascending}, {"Qualifier", Order.Ascending}}),
    Finish = Table.RenameColumns(Table.ReorderColumns(Sort,{"Attribute", "Qualifier", "Value"}),{{"Attribute", "Model"}})
in
    Finish[/SIZE]
 
Upvote 0
Hi Sandy

Maybe a silly question - starting to learn Power Query... :confused:
I got the desired result (see below), but couldn't in Power Query replace null with "" (empty string).

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Model​
[/TD]
[TD]
Qualifier​
[/TD]
[TD]
Valor​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
Model 1​
[/TD]
[TD]
A​
[/TD]
[TD]
0,1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
Model 1​
[/TD]
[TD]
B​
[/TD]
[TD]
0,5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
Model 1​
[/TD]
[TD]
C​
[/TD]
[TD]
0,4​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
Model 1​
[/TD]
[TD]
D​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
Model 1​
[/TD]
[TD]
E​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
Model 2​
[/TD]
[TD]
A​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
Model 2​
[/TD]
[TD]
B​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
Model 2​
[/TD]
[TD]
C​
[/TD]
[TD]
0,1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
Model 2​
[/TD]
[TD]
D​
[/TD]
[TD]
0,6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
Model 2​
[/TD]
[TD]
E​
[/TD]
[TD]
0,3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
Model 3​
[/TD]
[TD]
A​
[/TD]
[TD]
0,2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD]
Model 3​
[/TD]
[TD]
B​
[/TD]
[TD]
0,1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD]
Model 3​
[/TD]
[TD]
C​
[/TD]
[TD]
0,1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD]
Model 3​
[/TD]
[TD]
D​
[/TD]
[TD]
0,1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16​
[/TD]
[TD]
Model 3​
[/TD]
[TD]
E​
[/TD]
[TD]
0,5​
[/TD]
[/TR]
</tbody>[/TABLE]


The alternative way I found was:
Replace null with zero
Unpivot 3 columns
Move Model column left
Replace zero with null (reverting)
Sort
Load

Is there a better way?

M.
 
Last edited:
Upvote 0
Complementing previous post
M-Code (in Portuguese, I hope you can understand it ;))

Code: 
let
    Fonte = Excel.CurrentWorkbook(){[Name="Tabela2"]}[Content],
    #"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Qualifier", type text}, {"Model 1", type number}, {"Model 2", type number}, {"Model 3", type number}}),
    #"Valor Substituído" = Table.ReplaceValue(#"Tipo Alterado",null,0,Replacer.ReplaceValue,{"Model 1", "Model 2", "Model 3"}),
    #"Somente as Colunas Selecionadas Foram Transformadas em Linhas" = Table.Unpivot(#"Valor Substituído", {"Model 1", "Model 2", "Model 3"}, "Atributo", "Valor"),
    #"Colunas Reordenadas" = Table.ReorderColumns(#"Somente as Colunas Selecionadas Foram Transformadas em Linhas",{"Atributo", "Qualifier", "Valor"}),
    #"Linhas Classificadas" = Table.Sort(#"Colunas Reordenadas",{{"Atributo", Order.Ascending}, {"Qualifier", Order.Ascending}}),
    #"Valor Substituído1" = Table.ReplaceValue(#"Linhas Classificadas",0,null,Replacer.ReplaceValue,{"Valor"})
in
    #"Valor Substituído1"

M.
 
Upvote 0
Marcelo Branco said:
The alternative way I found was:
Replace null with zero
Unpivot 3 columns
Move Model column left
Replace zero with null (reverting)
Sort
Load
Is there a better way?
Click to expand...
Hi Marcelo,
but this is the same method except these differences: (null to 999 then 999 to null / null to 0 then 0 to null). IMHO with 999 is harder to make mistake because 0 is very close to the values in the source.
The important thing is that the goal has been achieved :diablo:

edit: (with Google Translate)
Code: 
[SIZE=1]let
     Source = Excel.CurrentWorkbook () {[Name = "Table2"]} [Content],
     # "Changed Type" = Table.TransformColumnTypes (Source, {{"Qualifier", type text}, {"Model 1", type number}, {"Model 2", type number}, {"Model 3", type number }}),
     # "Replaced Value" = Table.ReplaceValue (# "Changed Type", null, 0, Replacer.ReplaceValue, {"Model 1", "Model 2", "Model 3"}),
     # "Only Selected Columns Transformed into Rows" = Table.Unpivot (# "Replaced Value", {"Model 1", "Model 2", "Model 3"}, "Attribute", "Value"),
     # "Reordered Columns" = Table.ReorderColumns (# "Only Selected Columns Transformed into Rows", {"Attribute", "Qualifier", "Value"}),
     # "Sorted Rows" = Table.Sort (# "Reordered Columns", {{"Attribute", Order.Ascending}, {"Qualifier", Order.Ascending}}),
     # "Substituted Value1" = Table.ReplaceValue (# "Sorted Rows", 0, null, Replacer.ReplaceValue, {"Value"})
in
     # "Substituted Value1"[/SIZE]
 
Last edited:
Upvote 0
Odin, Sandy, and Marcelo... all very helpful. Both strategies worked. And I learned something new with Power Queries. Cheers!
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Macro to print a table filling from a sheet then move to the next row on the sheet
Replies
3
Views
171
Peter_SSs
Peter_SSs
C
  • Question Question
Nearest date based on three criteria
Replies
2
Views
300
chris_bosten
C
R
  • Question Question
VBA to add to data model when creating Power Query tables`
Replies
3
Views
577
Raddle
R
E
  • Question Question
Report that multiplies tables ('tree tables')
Replies
1
Views
238
Cubist
Cubist
T
  • Solved
take a column of daily transaction numbers with duplicates and show in different table with its corresponding date and no duplicates
2
Replies
13
Views
526
Dave Patton
D

Forum statistics

Threads
1,223,888
Messages
6,175,215
Members
452,618
Latest member
Tam84

Share this page

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