Transpose column of data to multiple rows of data, without having to manually 'cut' series

**Viteau** · 09-26-2014, 04:47 AM

Hi all,

I need help in transposing a column of data to multple rows of data. Problem is the column consists of series of 5 numbers and 1 word, and I need to have a row for each series of 6. When I simply transpose the whole column, I am stuck with an enormous row that I have to manually divide into the series. It would save me a lot of time if that can be done automatically. I've solved it by filtering the IBI column and manually copy pasting every kind of IBI, beginning with IBI-2. (see sheet IBI filter) That was better than transposing but I think it can be done even faster.

The 5 numbers and the sign are labelled, so that might help. I do need the data in the same chronological it is in now. I have attached the two excel files, one where I have done all the work manually and another one that's still unaltered. I have 67 other excel files that I need to do this with.

Would love your help!

**Bernie Deitrick** · 09-26-2014, 09:10 AM

You need a better example of before and after - I cannot understand where your numbers are coming from, or going to, since there are no common values between the column BNs of the two files, and the five values that you show grouped are not found together in the first file:

Marker X 603 665 676 671 722

Post a single file with "Before" and "After" sheets, and highlight with color the source of the values in the After table. And we don't need thousands of rows, just a couple of groups that show the source of what you want.

**Viteau** · 09-26-2014, 05:30 PM

I'm sorry, I should have been clearer. They weren't actually a before and after of the same file.

I've made a file the way you said. The source is in column BW of the before sheet, I've highlighted it.

**Viteau** · 09-27-2014, 05:57 PM

Does anyone know what to do?

**Bernie Deitrick** · 09-27-2014, 09:41 PM

I know what to do - enjoy my weekend

I'll take a look Monday....

**Viteau** · 09-28-2014, 10:44 AM

Thanks, I'm waiting patiently.

I have another question you might know the answer to. How do I exclude hidden rows in a formula I use? I have filtered one of my columns on the text that is displayed, and now I want to see if there has been a 'switch' by comparing a cell with the previous cell. IF(AB2=AB5; same; switch) But when I do that, the formula automatically includes hidden rows and transforms the formule into comparing a cell with 3 cells before.

**Bernie Deitrick** · 09-29-2014, 10:51 AM

Try this macro - not sure what you want to do with the other data, but this should get you started.

Option Explicit

Sub TestMacro1()
    Dim R As Range
    Dim S As Worksheet
    Dim T As Worksheet

    Set T = ActiveSheet

    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("After").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    
    T.Copy After:=Worksheets(Worksheets.Count)
    Set S = Worksheets(Worksheets.Count)
    S.Name = "After"
    
    With S
        .Range("BT:BZ").ClearContents
        For Each R In .Range(.Range("BR1"), .Cells(.Rows.Count, "BR").End(xlUp))
            If R.Value = "MARKER" Then
                R.Offset(0, 2).Value = R.Offset(-3, 0).Value
                R.Offset(0, 3).Value = R.Offset(-2, 0).Value
                R.Offset(0, 4).Value = R.Offset(-1, 0).Value
                R.Offset(0, 5).Value = R.Offset(1, 0).Value
                R.Offset(0, 6).Value = R.Offset(2, 0).Value
            End If
        Next R
        .Range("BT:BZ").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .Range("BN:BP").Delete
    End With
    
End Sub

**Viteau** · 10-03-2014, 05:04 AM

Thank you! It did work with the simplified file I sent you, but it does not work on my original files. That is partly due to some columns I have inserted since, but even after deleting those columns the macro doesn't work on my files. I also just realized that the labels 'IBI-2' are not included in the simplified files, which might make it a lot easier to transpose. The labels are in column CK in the attached file.

Could you have a look at the file I have attached and how I might change the macro to make it work?

(Ignore the sheet named 'IBI through filter', it's how I have temporarily solved this issue. By filtering on the label and copy-pasting each column next to the other.)105Frank.xlsx

Transpose column of data to multiple rows of data, without having to manually 'cut' series

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Transpose column of data to multiple rows of data, without having to manually 'cut' series

Re: Transpose column of data to multiple rows of data, without having to manually 'cut' se

Re: Transpose column of data to multiple rows of data, without having to manually 'cut' se

Re: Transpose column of data to multiple rows of data, without having to manually 'cut' se

Re: Transpose column of data to multiple rows of data, without having to manually 'cut' se

Re: Transpose column of data to multiple rows of data, without having to manually 'cut' se

Re: Transpose column of data to multiple rows of data, without having to manually 'cut' se

Re: Transpose column of data to multiple rows of data, without having to manually 'cut' se

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Move Multiple-Rows of Data to One Column (not Transpose)

How to transpose variable data from column A to multiple rows

Transpose Variable Data from Column A to Multiple Rows

how to transpose data from one column into multiple rows?

Macro to transpose one column into multiple rows over MANY lines of data?

Bookmarks

Bookmarks

Posting Permissions