Table values to single row for each value

J

jerryr0125

New Member
Joined
May 14, 2017
Messages
6
Hi I am looking to take data that is structured in a table and create a spreadsheet with each data point as a row.

Example - Start Data

[TABLE="width: 260"]
<colgroup><col style="width:65pt" width="65" span="4"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 65"]ID[/TD]
[TD="class: xl64, width: 65, align: right"]1/25/18[/TD]
[TD="class: xl64, width: 65, align: right"]2/14/18[/TD]
[TD="class: xl64, width: 65, align: right"]3/11/18[/TD]
[/TR]
[TR]
[TD]FS01[/TD]
[TD="align: right"]100.25[/TD]
[TD="align: right"]98.7[/TD]
[TD="align: right"]19.07[/TD]
[/TR]
[TR]
[TD]FS02[/TD]
[TD="align: right"]342.23[/TD]
[TD="align: right"]83.65[/TD]
[TD="align: right"]82.14[/TD]
[/TR]
[TR]
[TD]FS03[/TD]
[TD="align: right"]12.4[/TD]
[TD="align: right"]742.34[/TD]
[TD="align: right"]34.48[/TD]
[/TR]
[TR]
[TD]FS04[/TD]
[TD="align: right"]144.68[/TD]
[TD="align: right"]18.23[/TD]
[TD="align: right"]76.17[/TD]
[/TR]
</tbody>[/TABLE]


End Data

[TABLE="width: 195"]
<colgroup><col style="width:65pt" width="65" span="3"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 65"]ID[/TD]
[TD="class: xl64, width: 65"]Date[/TD]
[TD="class: xl64, width: 65"]Amount[/TD]
[/TR]
[TR]
[TD]FS01[/TD]
[TD="class: xl63, align: right"]1/25/18[/TD]
[TD="align: right"]100.25[/TD]
[/TR]
[TR]
[TD]FS01[/TD]
[TD="class: xl63, align: right"]2/14/18[/TD]
[TD="align: right"]98.7[/TD]
[/TR]
[TR]
[TD]FS01[/TD]
[TD="class: xl63, align: right"]3/11/18[/TD]
[TD="align: right"]19.07[/TD]
[/TR]
[TR]
[TD]FS02[/TD]
[TD="class: xl63, align: right"]1/25/18[/TD]
[TD="align: right"]342.23[/TD]
[/TR]
[TR]
[TD]FS02[/TD]
[TD="class: xl63, align: right"]2/14/18[/TD]
[TD="align: right"]83.65[/TD]
[/TR]
[TR]
[TD]FS02[/TD]
[TD="class: xl63, align: right"]3/11/18[/TD]
[TD="align: right"]82.14[/TD]
[/TR]
[TR]
[TD]FS03[/TD]
[TD="class: xl63, align: right"]1/25/18[/TD]
[TD="align: right"]12.4[/TD]
[/TR]
[TR]
[TD]FS03[/TD]
[TD="class: xl63, align: right"]2/14/18[/TD]
[TD="align: right"]742.34[/TD]
[/TR]
[TR]
[TD]FS03[/TD]
[TD="class: xl63, align: right"]3/11/18[/TD]
[TD="align: right"]34.48[/TD]
[/TR]
[TR]
[TD]FS04[/TD]
[TD="class: xl63, align: right"]1/25/18[/TD]
[TD="align: right"]144.68[/TD]
[/TR]
[TR]
[TD]FS04[/TD]
[TD="class: xl63, align: right"]2/14/18[/TD]
[TD="align: right"]18.23[/TD]
[/TR]
[TR]
[TD]FS04[/TD]
[TD="class: xl63, align: right"]3/11/18[/TD]
[TD="align: right"]76.17[/TD]
[/TR]
</tbody>[/TABLE]


thoughts ? Jerry<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { }.xl64 { background: yellow none repeat scroll 0% 0%; }</style><style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { background: yellow none repeat scroll 0% 0%; }.xl64 { background: yellow none repeat scroll 0% 0%; }</style>
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
The code below will transform a "table" starting in A1 to the version (see below) starting in F1.
Excel Workbook
ABCDEFGH
1ID1/25/20182/14/20183/11/2018IDDateAmount
2FS01100.2598.719.07FS011/25/2018100.25
3FS02342.2383.6582.14FS012/14/201898.7
4FS0312.4742.3434.48FS013/11/201819.07
5FS04144.6818.2376.17FS021/25/2018342.23
6FS022/14/201883.65
7FS023/11/201882.14
8FS031/25/201812.4
9FS032/14/2018742.34
10FS033/11/201834.48
11FS041/25/2018144.68
12FS042/14/201818.23
13FS043/11/201876.17
Sheet1



Code: 
Sub jerryr0125()
Dim R As Range, NxRw As Long, i As Long
Set R = Range("A1").CurrentRegion
Application.ScreenUpdating = False
Columns("F:H").ClearContents
Range("F1:H1").Value = Array("ID", "Date", "Amount")
For i = 2 To R.Columns(1).Cells.Count
    NxRw = Cells(Rows.Count, "F").End(xlUp).Row + 1
    Cells(NxRw, "F").Resize(R.Rows(1).Cells.Count - 1).Value = R.Columns(1).Cells(i, 1).Value
    Cells(NxRw, "G").Resize(R.Rows(1).Cells.Count - 1).Value = Application.Transpose(R.Rows(1).Offset(0, 1).Resize(, R.Rows(1).Cells.Count - 1).Value)
    Cells(NxRw, "H").Resize(R.Rows(1).Cells.Count - 1).Value = Application.Transpose(R.Rows(i).Offset(0, 1).Resize(, R.Rows(1).Cells.Count - 1).Value)
Next i
Application.ScreenUpdating = True
End Sub
 
Upvote 0
or you can try PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]ID[/td][td=bgcolor:#5B9BD5]1/25/18[/td][td=bgcolor:#5B9BD5]2/14/18[/td][td=bgcolor:#5B9BD5]03/11/2018[/td][td][/td][td=bgcolor:#70AD47]ID[/td][td=bgcolor:#70AD47]Date[/td][td=bgcolor:#70AD47]Amount[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]FS01[/td][td=bgcolor:#DDEBF7]
100.25​
[/td][td=bgcolor:#DDEBF7]
98.7​
[/td][td=bgcolor:#DDEBF7]
19.07​
[/td][td][/td][td=bgcolor:#E2EFDA]FS01[/td][td=bgcolor:#E2EFDA]
25/01/2018​
[/td][td=bgcolor:#E2EFDA]
100.25​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]FS02[/td][td]
342.23​
[/td][td]
83.65​
[/td][td]
82.14​
[/td][td][/td][td]FS01[/td][td]
14/02/2018​
[/td][td]
98.7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]FS03[/td][td=bgcolor:#DDEBF7]
12.4​
[/td][td=bgcolor:#DDEBF7]
742.34​
[/td][td=bgcolor:#DDEBF7]
34.48​
[/td][td][/td][td=bgcolor:#E2EFDA]FS01[/td][td=bgcolor:#E2EFDA]
11/03/2018​
[/td][td=bgcolor:#E2EFDA]
19.07​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]FS04[/td][td]
144.68​
[/td][td]
18.23​
[/td][td]
76.17​
[/td][td][/td][td]FS02[/td][td]
25/01/2018​
[/td][td]
342.23​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]FS02[/td][td=bgcolor:#E2EFDA]
14/02/2018​
[/td][td=bgcolor:#E2EFDA]
83.65​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]FS02[/td][td]
11/03/2018​
[/td][td]
82.14​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]FS03[/td][td=bgcolor:#E2EFDA]
25/01/2018​
[/td][td=bgcolor:#E2EFDA]
12.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]FS03[/td][td]
14/02/2018​
[/td][td]
742.34​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]FS03[/td][td=bgcolor:#E2EFDA]
11/03/2018​
[/td][td=bgcolor:#E2EFDA]
34.48​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]FS04[/td][td]
25/01/2018​
[/td][td]
144.68​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]FS04[/td][td=bgcolor:#E2EFDA]
14/02/2018​
[/td][td=bgcolor:#E2EFDA]
18.23​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]FS04[/td][td]
11/03/2018​
[/td][td]
76.17​
[/td][/tr]
[/table]

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnPivot = Table.Unpivot(Source, {"1/25/18", "2/14/18", "03/11/2018"}, "Attribute", "Value"),
    Type = Table.TransformColumnTypes(UnPivot, {{"Attribute", type date}}, "en-US"),
    Rename = Table.RenameColumns(Type,{{"Attribute", "Date"}, {"Value", "Amount"}})
in
    Rename[/SIZE]

I'm not using US date format so result is in dd/mm/yyyy

---
what is the heck with BB code? size for table is 1 but as I see table is much larger :(
 
Last edited:
Upvote 0
@jerryr0125
For future reference while we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Complicated VBA with 2 Conditions, 1 = Row target Sum, 2= Single number in the row
Replies
5
Views
727
MickG
M
M
  • Question Question
VBA how much permutation can be made using 3-digit with 0, 1 & 2
Replies
3
Views
724
motilulla
M
K
  • Question Question
Extract only 3 first sign "1", "X" Or "2" appear in the row
Replies
7
Views
841
Kishan
K
K
  • Question Question
Count if 3 character "1 X 2" are find in the 3 rows.
2 3
Replies
22
Views
2K
Kishan
K
R
  • Question Question
"Page" format to table - Power Pivot or Query
Replies
1
Views
801
RooBoyAust
R

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

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