Transpose Data to Create new table

Z

zifu886

New Member
Joined
Jul 10, 2019
Messages
13
Hi Expert,

Im learning excel while I work on parsing large amount data. Need your help me to create a new table from old table as listed below. Your help is highly appreciated. Thank you once again in advance for your great help,

Sincerely
Zi

[TABLE="width: 980"]
<tbody>[TR]
[TD]Source Table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order_Aging[/TD]
[TD]Company[/TD]
[TD]Order Date[/TD]
[TD]Order[/TD]
[TD]Order Due Date[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 - 30[/TD]
[TD]All-Good[/TD]
[TD]12/11/2018[/TD]
[TD]1234[/TD]
[TD]12/11/2018[/TD]
[TD]2400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31 - 60[/TD]
[TD]All-Good[/TD]
[TD]11/26/2018[/TD]
[TD]1200[/TD]
[TD]11/26/2018[/TD]
[TD]7200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Current[/TD]
[TD]UC-Tower[/TD]
[TD]12/21/2018[/TD]
[TD] #40[/TD]
[TD]12/31/2018[/TD]
[TD]1100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 - 30[/TD]
[TD]UC-Tower[/TD]
[TD]12/7/2018[/TD]
[TD]135[/TD]
[TD][/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]- 90[/TD]
[TD]UC-Tower[/TD]
[TD]12/7/2018[/TD]
[TD]#38[/TD]
[TD]12/17/2018[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]> 90[/TD]
[TD]UC-Tower[/TD]
[TD]12/21/2018[/TD]
[TD]136[/TD]
[TD][/TD]
[TD]15000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]current[/TD]
[TD]Hello.com[/TD]
[TD]12/17/2018[/TD]
[TD] #7657[/TD]
[TD]1/16/2019[/TD]
[TD]90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]current[/TD]
[TD]Hello.com[/TD]
[TD]12/31/2018[/TD]
[TD]72[/TD]
[TD]1/10/2019[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New Table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order_Aging[/TD]
[TD]Company[/TD]
[TD]Order Date[/TD]
[TD]Order[/TD]
[TD]Order Due Date[/TD]
[TD]Current[/TD]
[TD]1 - 30[/TD]
[TD]31 - 60[/TD]
[TD]61 - 90[/TD]
[TD]> 90[/TD]
[TD]Amount[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 - 30[/TD]
[TD]All-Good[/TD]
[TD]12/11/2018[/TD]
[TD]1234[/TD]
[TD]12/11/2018[/TD]
[TD][/TD]
[TD]2400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31 - 60[/TD]
[TD]All-Good[/TD]
[TD]11/26/2018[/TD]
[TD]1200[/TD]
[TD]11/26/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]2400[/TD]
[TD][/TD]
[TD][/TD]
[TD]2400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]current[/TD]
[TD]UC-Tower[/TD]
[TD]12/21/2018[/TD]
[TD] #40[/TD]
[TD]12/31/2018[/TD]
[TD]1100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 - 30[/TD]
[TD]UC-Tower[/TD]
[TD]12/7/2018[/TD]
[TD]135[/TD]
[TD][/TD]
[TD][/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]61 - 90[/TD]
[TD]UC-Tower[/TD]
[TD]12/7/2018[/TD]
[TD]#38[/TD]
[TD]12/17/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]> 90[/TD]
[TD]UC-Tower[/TD]
[TD]12/21/2018[/TD]
[TD]136[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15000[/TD]
[TD]15000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]current[/TD]
[TD]Hello.com[/TD]
[TD]12/17/2018[/TD]
[TD] #7657[/TD]
[TD]1/16/2019[/TD]
[TD]90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]90[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]current[/TD]
[TD]Hello.com[/TD]
[TD]12/31/2018[/TD]
[TD]72[/TD]
[TD]1/10/2019[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
I doubt I'll answer this as I'm busy at work but

in the New Table

why does 2400 appear in the 31-60 column for All-Good 11/26/2018 ?
The value is 7200 which doesn't appear ANYWHERE in the New Table
 
Last edited:
Upvote 0
with PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]source[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFFF00]result[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Order_Aging[/td][td=bgcolor:#5B9BD5]Company[/td][td=bgcolor:#5B9BD5]Order Date[/td][td=bgcolor:#5B9BD5]Order[/td][td=bgcolor:#5B9BD5]Order Due Date[/td][td=bgcolor:#5B9BD5]Amount[/td][td][/td][td=bgcolor:#70AD47]Order_Aging[/td][td=bgcolor:#70AD47]Company[/td][td=bgcolor:#70AD47]Order Date[/td][td=bgcolor:#70AD47]Order[/td][td=bgcolor:#70AD47]Order Due Date[/td][td=bgcolor:#70AD47]current[/td][td=bgcolor:#70AD47]1 - 30[/td][td=bgcolor:#70AD47]31 - 60[/td][td=bgcolor:#70AD47]-90[/td][td=bgcolor:#70AD47]> 90[/td][td=bgcolor:#70AD47]Amount[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]1 - 30[/td][td=bgcolor:#DDEBF7]All-Good[/td][td=bgcolor:#DDEBF7]
11/12/2018​
[/td][td=bgcolor:#DDEBF7]
1234​
[/td][td=bgcolor:#DDEBF7]
11/12/2018​
[/td][td=bgcolor:#DDEBF7]
2400​
[/td][td][/td][td=bgcolor:#E2EFDA]1 - 30[/td][td=bgcolor:#E2EFDA]All-Good[/td][td=bgcolor:#E2EFDA]
11/12/2018​
[/td][td=bgcolor:#E2EFDA]1234[/td][td=bgcolor:#E2EFDA]
11/12/2018​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
2400​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
2400​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]31 - 60[/td][td]All-Good[/td][td]
26/11/2018​
[/td][td]
1200​
[/td][td]
26/11/2018​
[/td][td]
7200​
[/td][td][/td][td]31 - 60[/td][td]All-Good[/td][td]
26/11/2018​
[/td][td]1200[/td][td]
26/11/2018​
[/td][td][/td][td][/td][td]
7200​
[/td][td][/td][td][/td][td]
7200​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]current[/td][td=bgcolor:#DDEBF7]UC-Tower[/td][td=bgcolor:#DDEBF7]
21/12/2018​
[/td][td=bgcolor:#DDEBF7]#40[/td][td=bgcolor:#DDEBF7]
31/12/2018​
[/td][td=bgcolor:#DDEBF7]
1100​
[/td][td][/td][td=bgcolor:#E2EFDA]current[/td][td=bgcolor:#E2EFDA]UC-Tower[/td][td=bgcolor:#E2EFDA]
21/12/2018​
[/td][td=bgcolor:#E2EFDA]#40[/td][td=bgcolor:#E2EFDA]
31/12/2018​
[/td][td=bgcolor:#E2EFDA]
1100​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
1100​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1 - 30[/td][td]UC-Tower[/td][td]
07/12/2018​
[/td][td]
135​
[/td][td][/td][td]
200​
[/td][td][/td][td]1 - 30[/td][td]UC-Tower[/td][td]
07/12/2018​
[/td][td]135[/td][td][/td][td][/td][td]
200​
[/td][td][/td][td][/td][td][/td][td]
200​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
-90​
[/td][td=bgcolor:#DDEBF7]UC-Tower[/td][td=bgcolor:#DDEBF7]
07/12/2018​
[/td][td=bgcolor:#DDEBF7]#38[/td][td=bgcolor:#DDEBF7]
17/12/2018​
[/td][td=bgcolor:#DDEBF7]
100​
[/td][td][/td][td=bgcolor:#E2EFDA]-90[/td][td=bgcolor:#E2EFDA]UC-Tower[/td][td=bgcolor:#E2EFDA]
07/12/2018​
[/td][td=bgcolor:#E2EFDA]#38[/td][td=bgcolor:#E2EFDA]
17/12/2018​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
100​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
100​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]> 90[/td][td]UC-Tower[/td][td]
21/12/2018​
[/td][td]
136​
[/td][td][/td][td]
15000​
[/td][td][/td][td]> 90[/td][td]UC-Tower[/td][td]
21/12/2018​
[/td][td]136[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
15000​
[/td][td]
15000​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]current[/td][td=bgcolor:#DDEBF7]Hello.com[/td][td=bgcolor:#DDEBF7]
17/12/2018​
[/td][td=bgcolor:#DDEBF7]#7657[/td][td=bgcolor:#DDEBF7]
16/01/2019​
[/td][td=bgcolor:#DDEBF7]
90​
[/td][td][/td][td=bgcolor:#E2EFDA]current[/td][td=bgcolor:#E2EFDA]Hello.com[/td][td=bgcolor:#E2EFDA]
17/12/2018​
[/td][td=bgcolor:#E2EFDA]#7657[/td][td=bgcolor:#E2EFDA]
16/01/2019​
[/td][td=bgcolor:#E2EFDA]
90​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
90​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]current[/td][td]Hello.com[/td][td]
31/12/2018​
[/td][td]
72​
[/td][td]
10/01/2019​
[/td][td]
100​
[/td][td][/td][td]current[/td][td]Hello.com[/td][td]
31/12/2018​
[/td][td]72[/td][td]
10/01/2019​
[/td][td]
100​
[/td][td][/td][td][/td][td][/td][td][/td][td]
100​
[/td][/tr]
[/table]

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Order_Aging", type text}, {"Company", type text}, {"Order Date", type date}, {"Order", type text}, {"Order Due Date", type date}, {"Amount", Int64.Type}}),
    Index = Table.AddIndexColumn(Type, "Index", 0, 1),
    Duplicta1 = Table.DuplicateColumn(Index, "Order_Aging", "Order_Aging - Copy"),
    Duplicate2 = Table.DuplicateColumn(Duplicta1, "Amount", "Amount - Copy"),
    Pivot = Table.Pivot(Duplicate2, List.Distinct(Duplicate2[#"Order_Aging - Copy"]), "Order_Aging - Copy", "Amount - Copy", List.Sum),
    Sort = Table.Sort(Pivot,{{"Index", Order.Ascending}}),
    RC = Table.RemoveColumns(Sort,{"Index"}),
    REorder = Table.ReorderColumns(RC,{"Order_Aging", "Company", "Order Date", "Order", "Order Due Date", "current", "1 - 30", "31 - 60", "-90", "> 90", "Amount"})
in
    REorder[/SIZE]
 
Upvote 0
Hi Special-K99,
good afternoon, Thanks much for your reply. That is a typo when I transferred the data. the All-Good 11/26/2018 for 31-60 should be "7200" and amount should be updated to 7200 as well. What I tried to achieve is to convert the source table to a new table by inserting the new Column(s) and each column's title = the unique value from Column A, then copy the amount.value to the corresponded new Column (eg, A2="1-30", then copy amount.value (row#2) ) to the new Column (1-30) being created), so on and so forth. Does this make sense? Thank you very much for your help,

Sincerely
Zi
 
Upvote 0
Hi Sandy666,

good morning, Thank you very much for your the M-codes. I used 2013 excel & honestly, I am not familiar with the power query, but tried to download pwoerquery onto excel, and did quick learning to run the power but when I run the M-code, I keep getting below errors, could you please provide me some guidances to run the run. I appreciate your time & help, thank you once again,

Sincerely
Zi

-------------------------------------------------------------------------
Expression.Error: We couldn't find an Excel table named 'Table1'.
Details:
Table1

-------------------------------------------------------------------------
 
Upvote 0
because your source range must be a table, Excel Table ! (see source in my post)
Use Ctrl+T then check in name manager the name of this table and change the name in the code if neccesary
 
Last edited:
Upvote 0
Hi Sandy666,

Great, I was able to run M-Code with Power Query and result is just perfect that I am looking for it, Pwer Query is powerful but I am totally just a beginner & need deeping more about it. Do you know how to use a macro to achieve the same results. Much Appreciate & thank you once again for your great help,

Sincerely
Zi
 
Upvote 0
Zi,
A really good starting point for PQ is Ken Puls book "M is for (Data) Monkey"
 
Upvote 0
Hi Alan

thanks much for your info. I found it from Amazon & ordered one. thanks again for your info,

Have a nice weekend,

Sincerely
Zi
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
506
etaf
etaf
L
  • Question Question
INDEX MATCH
Replies
1
Views
341
Fluff
Fluff
T
  • Question Question
want to extract week number for every month
Replies
3
Views
429
sucett
S
J
  • Question Question
Merging Duplicate values and deleting rows based on certain parameters.
Replies
1
Views
361
JohnBecks
J
V
  • Solved
Creating a new filtered list from a dynamically created list based on previous numbers
Replies
6
Views
136
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

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