1

I have data organised in cloumns with multiple rows which I need to convert to rows with multiple columns for data analysis. For example,

    ID  Date of entry   Location    Data1   Data2
    1   20101030        1           a       b
    1   20101030        2           c       d
    1   20101125        1           w       v
    1   20101125        2           e       d
    1   20110314        1           we      r
    1   20110314        2           f       f
    2   20091024        1           ewr     rte
    2   20091024        2           gr      ert
    3   20061128        1           jy      bf
    3   20061128        2           u       df
    3   20110517        1           rd      fd
    3   20110517        2           sg      sd

into this format with each ID row with multiple columns of data (only head row shown below)

ID  entry_1 Dateofentry location_1  data1   data2   location_2  data1   data2   entry_2 Dateofentry location_1  data1   data2   location_2  data1   data2   entry_3 Dateofentry location_1  data1   data2   location_2  data1   data2

Can anyone assist?

Thanks! GT

10gistic
  • 535
  • 3
  • 13
Gavin Tan
  • 11
  • 1
  • 3
  • Are you open to a macro for this? How many rows of data do you have? What version of excel are you using? – hydrox467 Sep 27 '12 at 03:34

4 Answers4

1

You'll have to add the headers yourself, but this code should do what you need:

Sub ConsolidateRows_SpreadAcross()

Dim lastRow As Long, i As Long, j As Long
Dim colMatch As Variant, colConcat As Variant

application.ScreenUpdating = False 'disable ScreenUpdating to avoid screen flashes

lastRow = range("A" & Rows.Count).End(xlUp).Row 'get last row

For i = lastRow To 2 Step -1

    If Cells(i, 2) = Cells(i - 1, 2) Then
        range(Cells(i, 3), Cells(i, Columns.Count).End(xlToLeft)).Copy Cells(i - 1, Columns.Count).End(xlToLeft).Offset(, 1)
        Rows(i).Delete
    Else
        If Cells(i, 1) = Cells(i - 1, 1) Then
            range(Cells(i, 2), Cells(i, Columns.Count).End(xlToLeft)).Copy _
                Cells(i - 1, Columns.Count).End(xlToLeft).Offset(, 1)
            Rows(i).Delete
        End If
    End If

Next

application.ScreenUpdating = True 'reenable ScreenUpdating
End Sub
nutsch
  • 5,922
  • 2
  • 20
  • 35
0

If you copy the whole table, and then right-click wherever you want to paste the new data, you should be given the menu "Paste Special." From that menu, choose transpose. That should transpose the columns into rows.

10gistic
  • 535
  • 3
  • 13
  • I was going to post the same answer, but this doesn't put the data into the format the OP asked for. – hydrox467 Sep 27 '12 at 03:32
  • Yeah, that's the only thing I was unsure of, but I wasn't quite sure what he was looking for, as the example output didn't use the data from the example input. – 10gistic Sep 27 '12 at 03:36
0

I started from a formula I found on the internet that takes one-column list and changes it's layout to multyple columns: that insteat of 1000+ rows you'll get 60 rows with many columns of that list. you can find it here http://help.lockergnome.com/office/list-multiple-columns-page--ftopict935389.html

i wanted to take a list that has 4 columns (the 5th column is empty) and 1000+ rows, and make it a 50 rows table that the 4 fields repeats itself.

for example i have:

family  |  name   |  amount  |   table |
fam1    |  shlomi |  2       |   38    |
fam2    |  hila   |  4       |   23    |    
....    
fam1000 |  avi    |  1       |   15    |

and i want to make it

family  |  name   |  amount  |   table |     |fam50    |  ben   |  2       |   68    |  ...    
fam1    |  shlomi |  2       |   38    |      ...    
fam2    |  hila   |  4       |   23    |      ...    
...                                           ...    
fam49   |  dror   |  1       |   15    |     |fam99    |  dror   |  1      |   15    | ...

On a new worksheet in your existing workbook, insert the following formula into A1:

=IF(OFFSET(Sheet1!$A$1,QUOTIENT(COLUMN(),5)*50+ROW()-1,MOD(COLUMN()-1,5))="","",OFFSET(Sheet1!$A$1,QUOTIENT(COLUMN(),5)*50+ROW()-1,MOD(COLUMN()-1,5)))

.. Copy this formula across as many columns as you need, and as many rows as you need ( I used 50 in my example)

You can change '5' in the furmula to the number of fields you want in each column layout, and you can change '50' to the number of rows you want in each page.

שלומי שטיין
  • 90
  • 9
-2

family | name | amount | table | fam1 | shlomi | 2 | 38 | fam2 | hila | 4 | 23 | .... fam1000 | avi | 1 | 15 | fam50 | ben | 2 | 68 |

dror | 1 | 15 |

and i want to make it

family | name | amount | table | |fam50 | ben | 2 | 68 | ...
fam1 | shlomi | 2 | 38 | ...
fam2 | hila | 4 | 23 | ...
... ...
fam49 | avi | 1 | 15 | |fam99 | dror | 1 | 15 | ...

Ni Tara
  • 1