×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Transpose Data Table to another Data Table
2

Transpose Data Table to another Data Table

Transpose Data Table to another Data Table

(OP)
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.

RE: Transpose Data Table to another Data Table

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!

RE: Transpose Data Table to another Data Table

Hi,

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Transpose Data Table to another Data Table

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:

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


Data sheet Structured Table named tDATA:

ID# 	Log date/time	 ID_Idx
111111	9/10/2018 7:30	 Log date/time1
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
 

Pivot sheet

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				
 
Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Transpose Data Table to another Data Table

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

RE: Transpose Data Table to another Data Table

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

Have a great day.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close