Rearranging data in Excel

[zhangm Supervisor]

I have some software that conducts measurements on a series of images and outputs these measurements in the following format.

 

Image	Results
1	7531
1	11129
1	5180
1	15350
1	9567
1	7747
2	8198
2	14134
2	11335
2	5070
2	3682
2	14110
2	5198
2	3723
2	4461
2	10422
3	6939
3	6531
3	14991
3	6148
4	6499
4	8206
4	6084
4	15234
4	6376
…	…

 

This is just an example. In reality, there are several hundred results per image, and up to a couple hundred images.

 

I would like to automatically rearrange this data so that the image number occupies the first column, and the results for that image number are listed in columns to the right, e.g.:

 

Image	Results									
1	7531	11129	5180	15350	9567	7747				
2	8198	14134	11335	5070	3682	14110	5198	3723	4461	10422
3	6939	6531	14991	6148						
4	6499	8206	6084	15234	6376					

What's the best way to do this via Excel? Tried doing it manually for one set of data, but gets real tedious after 20000 or so lines.

[Torolol]

I remember doing the similar, and I would suggest using autohotkey to semi-automates the process.

[Stoffel]

Can't you somehow turn it into a pivot table to get it into that layout?

Or dou you still need to work on the data afterwards?

[zhangm Supervisor]

  On 15/03/2016 at 06:03, Stoffel said:

Can't you somehow turn it into a pivot table to get it into that layout?

Or dou you still need to work on the data afterwards?

Expand  

I'll try playing around with Pivot Tables some more. Currently, I've only gotten the results to be column headers, which produces a very long spreadsheet.

 

AHK might provide a solution, though it might take considerable scripting to get a general solution (I'm not sure we've even settled down on a specific data format yet, so the spreadsheet design and cells could change).

 

Since there is no information here that wouldn't be reproduced in a csv file, I'm also considering dumping it out as tab delimited columns of numbers and praying to the Regex deities for assistance.

 

Edit. Found a macro that does this job if the input data is formatted correctly. The measurements must be in two columns, each with a header like the example in the first post.

 

Option Explicit

Sub Consolidate()
'JBeaucaire  (9/18/2009)
'Columnar data is Sorted/Matched by column A values, merge all other cells into row format
Dim LastRow As Long, NextCol As Long
Dim LastCol As Long, Rw As Long, Cnt As Long
Dim delRNG As Range
Application.ScreenUpdating = False

'Sort data
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
    
'Seed the delete range
    Set delRNG = Range("A" & LastRow + 10)
    
'Group matching names
    For Rw = LastRow To 2 Step -1
        If Cells(Rw, "A").Value = Cells(Rw - 1, "A").Value Then
            Range(Cells(Rw, "B"), Cells(Rw, Columns.Count).End(xlToLeft)).Copy _
                Cells(Rw - 1, Columns.Count).End(xlToLeft).Offset(0, 1)
            Set delRNG = Union(delRNG, Range("A" & Rw))
        End If
    Next Rw

'Delete unneeded rows all at once
    delRNG.EntireRow.Delete (xlShiftUp)
    Set delRNG = Nothing

'Add titles
    NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
    LastCol = Cells(1, 1).CurrentRegion.Columns.Count
    Range("B1", Cells(1, NextCol - 1)).Copy Range(Cells(1, NextCol), Cells(1, LastCol))

Cells.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Taken from http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/text-functions/column-sets-to-rows.

Edited March 16, 2016 by zhangm
one solution