Table values to single row for each value

[jerryr0125]

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

ID	1/25/18	2/14/18	3/11/18
FS01	100.25	98.7	19.07
FS02	342.23	83.65	82.14
FS03	12.4	742.34	34.48
FS04	144.68	18.23	76.17

<colgroup><col style="width:65pt" width="65" span="4"> </colgroup><tbody>
</tbody>

End Data

ID	Date	Amount
FS01	1/25/18	100.25
FS01	2/14/18	98.7
FS01	3/11/18	19.07
FS02	1/25/18	342.23
FS02	2/14/18	83.65
FS02	3/11/18	82.14
FS03	1/25/18	12.4
FS03	2/14/18	742.34
FS03	3/11/18	34.48
FS04	1/25/18	144.68
FS04	2/14/18	18.23
FS04	3/11/18	76.17

<colgroup><col style="width:65pt" width="65" span="3"> </colgroup><tbody>
</tbody>

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>

 

[Trebor76]

For reference this has been cross-posted here

 

[JoeMo]

The code below will transform a "table" starting in A1 to the version (see below) starting in F1.

Excel Workbook
	A	B	C	D	E	F	G	H
1	ID	1/25/2018	2/14/2018	3/11/2018		ID	Date	Amount
2	FS01	100.25	98.7	19.07		FS01	1/25/2018	100.25
3	FS02	342.23	83.65	82.14		FS01	2/14/2018	98.7
4	FS03	12.4	742.34	34.48		FS01	3/11/2018	19.07
5	FS04	144.68	18.23	76.17		FS02	1/25/2018	342.23
6						FS02	2/14/2018	83.65
7						FS02	3/11/2018	82.14
8						FS03	1/25/2018	12.4
9						FS03	2/14/2018	742.34
10						FS03	3/11/2018	34.48
11						FS04	1/25/2018	144.68
12						FS04	2/14/2018	18.23
13						FS04	3/11/2018	76.17
Sheet1

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

 

[sandy666]

or you can try PowerQuery (Get&Transform)

ID	1/25/18	2/14/18	03/11/2018		ID	Date	Amount
FS01	100.25​	98.7​	19.07​		FS01	25/01/2018​	100.25​
FS02	342.23​	83.65​	82.14​		FS01	14/02/2018​	98.7​
FS03	12.4​	742.34​	34.48​		FS01	11/03/2018​	19.07​
FS04	144.68​	18.23​	76.17​		FS02	25/01/2018​	342.23​
					FS02	14/02/2018​	83.65​
					FS02	11/03/2018​	82.14​
					FS03	25/01/2018​	12.4​
					FS03	14/02/2018​	742.34​
					FS03	11/03/2018​	34.48​
					FS04	25/01/2018​	144.68​
					FS04	14/02/2018​	18.23​
					FS04	11/03/2018​	76.17​

[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

 

[jerryr0125]

Thanks - works (JoeMos code) - appreciate the help and support !! Jerry

 

[Fluff]

@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.