reorganising data

E

ellison

Active Member
Joined
Aug 1, 2012
Messages
356
Office Version
  1. 365
Platform
  1. Windows
Hi, my job (joy of joys!:eeek:) is to collate the different reports that we get in from different departments around the organisation...
and there is one really repetitive aspect of it that leaves my fingers (& head) numb from all of the copying and pasting. I'm hoping that somebody may be able to suggest something to help automate this...

In short, part of the info in the the reports that we receive is organised into columns. And we'd like it to be sort of re-organised into rows.
As simple as this sounds, it's taking ages with a really convoluted set of lookups, filters & concatenations!

Here is a brief sample of what we are getting at (by the way, the reports can sometimes be 100,000 lines +. (And we have told the the powers that be that Excel isn't really designed for storing data that way, but it seems to fall on deaf ears, grr!)

Here is a sample of the incoming data:
[TABLE="width: 205"]
<tbody>[TR]
[TD="align: left"][/TD]
[TD="align: left"][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Department[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, align: left"]XYZ789[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl65, align: left"]average[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, align: left"]XYZ789[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl65, align: left"]supersedes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, align: left"]XYZ789[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl65, align: left"]not declared[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, align: left"]P1444453.12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl65, align: left"]excellent!!!![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, align: left"]Hammersmith[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl65, align: left"]time lag on sales[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, align: left"]Liverpool[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl65, align: left"]headcount issue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, align: left"]Liverpool[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl65, align: left"]excellent![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Liverpool[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl65, align: left"]good[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Liverpool[/TD]
[TD]good[/TD]
[/TR]
[TR]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, align: left"]Orange[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl65, align: left"]good-ish[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, align: left"]Red[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl65, align: left"]below average[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD="class: xl65, align: left"]Purple[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl65, align: left"]concerning[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Purple[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl65, align: left"]problem[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


..... And here is how we would like the info to appear after being reorganised:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl65"]Department[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Comment 1[/TD]
[TD]Comment 2[/TD]
[TD]Comment 3[/TD]
[TD]Comment 4[/TD]
[/TR]
[TR]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl65, align: left"]XYZ789[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, align: left"]average[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, align: left"]supersedes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, align: left"]not declared[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl65, align: left"]P1444453.12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, align: left"]excellent!!!![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl65, align: left"]Hammersmith[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, align: left"]time lag on sales[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl65, align: left"]Liverpool[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, align: left"]headcount issue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, align: left"]excellent![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, align: left"]good[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, align: left"]good[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl65, align: left"]Red[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, align: left"]below average[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl65, align: left"]Purple[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, align: left"]concerning[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, align: left"]problem[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


If you could point me in the right direction, it would be MASSIVELY appreciated!!! (by me and by me ever-so-numb fingertips:rofl:!!!)

With thanks

Neil
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
How about
Code: 
Sub ConverttoRows()
   Dim Cl As Range
   Dim Ky As Variant
   Dim Dic As Object
   Dim Ary As Variant
   
   Set Dic = CreateObject("scripting.dictionary")
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Not Dic.exists(Cl.Value) Then
         Dic.Add Cl.Value, Cl.Offset(, 1).Value
      Else
         Dic(Cl.Value) = Dic(Cl.Value) & "|" & Cl.Offset(, 1).Value
      End If
   Next Cl
   For Each Ky In Dic.keys
      With Sheets("New").Range("A" & Rows.Count).End(xlUp).Offset(1)
         .Value = Ky
         Ary = Split(Dic(Ky), "|")
         If UBound(Ary) > 0 Then
            .Offset(, 1).Resize(, UBound(Ary) + 1).Value = Ary
         Else
            .Offset(, 1).Value = Ary
         End If
      End With
   Next Ky
End Sub
 
Upvote 0
Many thanks indded...
But I'm getting an error:

"runtime error '9'. Subscript out of range". When I hit debug, the line of code that is highlighted is:

With Sheets("New").Range("A" & Rows.Count).End(xlUp).Offset(1)



Does that help?!
 
Upvote 0
You need to change the sheet name, to the sheet you want the output on.
 
Upvote 0
That is ABSOLUTELY AMAZING.......... My fingertips and I are extremely grateful!!!!!!!!!!!!!!!!!!!!

Thank you very much

Best

Neil
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Macro Requested to pull sections based upon number into blank excel sheet
Replies
0
Views
618
Darryproduct
D

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top