• 0

Rearranging data in Excel


Question

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.

Link to comment
https://www.neowin.net/forum/topic/1291940-rearranging-data-in-excel/
Share on other sites

3 answers to this question

Recommended Posts

  • 0
  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 by zhangm
one solution
This topic is now closed to further replies.
  • Posts

    • Ew... Not going to download that crap, even if it's free!
    • They can say whatever they want about the delay, it was paid for. No way it just happens to release exactly 1 year later, gtfo.
    • There's a similar 2025 model that's even cheaper (£474)
    • Black Myth: Wukong hits Xbox this August, exactly a year after PlayStation launch by Pulasthi Ariyasinghe Developer Game Science delivered one of the biggest action games of all time last year, with Black Myth: Wukong going on to sell tens of millions in its launch month and winning a multitude of awards. However, the title skipped out on a platform it was originally announced to launch in: Xbox. Following a long string of reasons and delays, it seems the port is finally coming to the platform. Today, Game Science announced that the Xbox Series X|S version of Black Myth: Wukong now has a release date, with it landing on August 20, 2025. Pre-orders for the title will become available starting June 18. Interestingly, this Xbox release date is set exactly one year after the original release on PlayStation 5 and PC. Game Science originally stated that the delay was due to technical issues with Xbox consoles. However, Microsoft refuted that claim quickly and alluded that the delay was due to some sort of console exclusivity deal between Game Science and Sony. In today's announcement, Game Science once again says that the delay was due to its work on porting the game to Xbox and meeting its quality standards. "Bringing Black Myth: Wukong to Xbox Series X|S—and ensuring the experience met our internal quality standards—was no easy feat," said the studio today regarding the long delay on Xbox platforms. "Fortunately, we were able to complete this challenging task smoothly within the first year of the game's official release." "If you're an Xbox player who hasn't yet experienced the game on another platform, we're confident it will stand among the finest ARPGs available on Xbox," added Game Science. For those looking to jump into the game a little cheaper than usual, Game Science will be hosting Black Myth: Wukong's first-ever sale on June 18. The 20% off discount will be available across PC, PlayStation, and even the Microsoft Store for the Xbox Series X|S pre-order.
  • Recent Achievements

    • Week One Done
      theevergreentree earned a badge
      Week One Done
    • Dedicated
      Fryer Tuck earned a badge
      Dedicated
    • Week One Done
      luxoxfurniture earned a badge
      Week One Done
    • First Post
      Uranus_enjoyer earned a badge
      First Post
    • Week One Done
      Uranus_enjoyer earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      441
    2. 2
      +FloatingFatMan
      248
    3. 3
      snowy owl
      228
    4. 4
      ATLien_0
      213
    5. 5
      Xenon
      152
  • Tell a friend

    Love Neowin? Tell a friend!