Transpose Data Table to another Data Table 2

[zerkerotz]

Just would like to ask for your help on how to do this on vb.net
Need to transpose data table result to another data table.
All logs of a particular ID# should be re-arrange into one row only then export to Excel.

This is the scenario.

Result
Data table 1
ID# | Log date/time
111111 | 09/10/2018 7:30
111111 | 09/10/2018 10:30
111111 | 09/10/2018 13:30
111111 | 09/10/2018 17:30
222222 | 09/10/2018 7:30
222222 | 09/10/2018 17:30
333333 | 09/10/2018 7:30
333333 | 09/10/2018 18:30

Transpose to
Data table 2
ID# | Log date/time 1 | Log date/time 2 | Log date/time 3 | Log date/time 4
111111 | 09/10/2018 7:30 | 09/10/2018 10:30 | 09/10/2018 13:30 | 09/10/2018 17:30
222222 | 09/10/2018 7:30 | 09/10/2018 17:30
333333 | 09/10/2018 7:30 | 09/10/2018 18:30

Thanks in advance.

 

[jebenson]

This code should produce the output you want, in a .csv file which you can open in Excel.

Dim LastID As String
Dim ThisID As String
Dim ThisLine As String
Dim sw As System.Io.StreamWriter

LastID = ""

ThisLine = ""

sw = New System.IO.StreamWriter("C:\Temp\LogData.csv") 'use your own path and filename

'assuming the datatable is called "dt"
For Each dr As DataRow in dt.Rows

ThisID = dr.Item("IDNum")​

If ThisID <> LastID Then​

If ThisLine.Length > 0 Then​

sw.WriteLine(ThisLine)​

ThisLine = ""​

End If​

LastID = ThisID​

ThisLine = dr.Item("IDNum") & "," & dr.Item("Log date/time")​

ElseIf ThisID = LastID Then​

ThisLine &= "," & dr.Item("Log date/time")​

End If​

Next

sw.Close()


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[SkipVought]

Hi,

then export to Excel.

As a nearly 3 decade spreadsheet user, I absolutely detest, hate and abhore getting a summarized report to import and use in Excel. Did I say that I despise and loathe getting a report in Excel? I just want to be clear.

Just send the table you currently have. Excel has all sorts of reporting tools, one of which is the Pivot Table. There are query solutions as well that can be applied to the “raw data”.

The reason I get so exercised by getting stuck with aggregations rather than more granular data is that invariably someone will want me to do some data analysis on this report data. Well that report is 1) a summary and 2) in a format that is non-normalized and thereby nearly impossible to employ Excel’s data analysis tools.

Do your downstream users a huge favor. Just send your table as a .csv, for instance.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here's a sample of your data with a few more rows and a Pivot sheet with a Pivot Table.

The only VBA code refreshes the PT when the Pivot sheet is activated.

Formula:
[tt]
[highlight #FCE94F]=tDATA[[#Headers],[ Log date/time]]&COUNTIF($A$1:[@[ID'# ]],A2)[/highlight]
[/tt]

Data sheet Structured Table named tDATA:
[pre]
ID# Log date/time ID_Idx
111111 9/10/2018 7:30 [highlight #FCE94F]Log date/time1[/highlight]
111111 9/10/2018 10:30 Log date/time2
111111 9/10/2018 13:30 Log date/time3
111111 9/10/2018 17:30 Log date/time4
222222 9/10/2018 7:30 Log date/time1
222222 9/10/2018 17:30 Log date/time2
333333 9/10/2018 7:30 Log date/time1
333333 9/10/2018 18:30 Log date/time2
444444 9/11/2018 7:00 Log date/time1
444444 9/12/2018 0:00 Log date/time2
555555 9/11/2018 0:00 Log date/time1
111111 9/11/2018 0:00 Log date/time5
222222 9/11/2018 0:00 Log date/time3
[/pre]

Pivot sheet
[pre]
Sum of Log date/time ID_Idx
ID# Log date/time1 Log date/time2 Log date/time3 Log date/time4 Log date/time5
111111 9/10/18 7:30 9/10/18 10:30 9/10/18 13:30 9/10/18 17:30 9/11/18 0:00
222222 9/10/18 7:30 9/10/18 17:30 9/11/18 0:00
333333 9/10/18 7:30 9/10/18 18:30
444444 9/11/18 7:00 9/12/18 0:00
555555 9/11/18 0:00
[/pre]
Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[zerkerotz]

@jebenson, you're a genius!
It works as needed. Thank you for the effort.

 

[zerkerotz]

@SkipVought, thanks for the idea.
Will try to look also that Pivot option in Excel.

Have a great day.