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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
726
MickG
M
M
  • Question Question
VBA how much permutation can be made using 3-digit with 0, 1 & 2
Replies
3
Views
722
motilulla
M
K
  • Question Question
Extract only 3 first sign "1", "X" Or "2" appear in the row
Replies
7
Views
839
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
799
RooBoyAust
R

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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