Processing
 

How to transpose rows into columns between two cells which starts with certain value?

06/09/2016 03:38#1

Joined at: 4 months ago

Post: 2

Thank: 0

Thanked: 0

I have an excel sheet which will have rows with certain geographic parameters. I would like to transpose all the rows between File and next File to the adjacent columns. Original sheet is like that of the one in Image - 1. I would like get it transposed like that of the one in Image - 2.

For One Row - One Column, i've used this code http://www.mrexcel.com/forum/excel-questions/79645-copying-alternate-rows-data-into-column.html and it works perfectly. But don't know how to do it for multiple rows - columns transpose

How to transpose rows into columns between two cells which starts with certain value?

06/09/2016 05:15Top#2

Joined at: 7 months ago

Post: 15

Thank: 0

Thanked: 0

Here is a VBA solution

Sub TransposeData()      Const FirstRow As Long = 1     Const WorkSheetName As String = "Sheet4"     Dim arData, v     Dim List As Object     Set List = CreateObject("System.Collections.ArrayList")     Dim NextRow As Long, x As Long      With Worksheets(WorkSheetName)         arData = .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Value         NextRow = WorksheetFunction.CountA(.Range("B:B")) + 1          For Each v In arData             If InStr(v, "File:") And List.Count > 0 Then                  .Cells(NextRow, "B").Resize(1, List.Count) = List.ToArray                 List.Clear                 NextRow = NextRow + 1              End If             List.Add v         Next         If List.Count > 0 Then .Cells(NextRow, "B").Resize(1, List.Count) = List.ToArray     End With  End Sub 
06/09/2016 04:06Top#3

Tin Tran

Member

Joined at: 4 months ago

Post: 1

Thank: 0

Thanked: 0

This works in Google Sheets. As a sample I had 3 records, each record has 4 items so i had data from A1 to A12 representing my 3 records. You want to create a column for example B that has numbers from 0 to whatever.

Then in cell C1 you can have something like =INDEX($A$1:$A$12,$B1*4+1,1)

and in cell D1 you can have something like =INDEX($A$1:$A$12,$B1*4+2,1)

and in cell E1 you can have something like =INDEX($A$1:$A$12,$B1*4+3,1)

and in cell F1 you can have something like =INDEX($A$1:$A$12,$B1*4+4,1)

The so I was finished with first row, then i just click and dragged to fill the rest of the rows, and because $B1 the 1 doesn't have a dollar sign, it automatically read the next row from $B2 and $B3 and grabbed the right data.

Similar articles

Excel More conditions in one formula?

4 months ago - Reply: 2 - Views: 171

Get Workbook reference from Open File dialog

4 months ago - Reply: 2 - Views: 128

Add Calculated Field To Pivot Table

4 months ago - Reply: 0 - Views: 5

Nested IF Functions in Excel not working

4 months ago - Reply: 3 - Views: 163

Formulating Date in Excel 2013

4 months ago - Reply: 2 - Views: 88

VB6 extracts in Excel 2013

5 months ago - Reply: 2 - Views: 153

Excel 2013 : Pull Matching Data from Column

5 months ago - Reply: 1 - Views: 19

excel search and copy in/from another file

5 months ago - Reply: 0 - Views: 1

Buggy PivotTable.ColumnRange

5 months ago - Reply: 0 - Views: 4

Excel static conditional formatting area

5 months ago - Reply: 0 - Views: 8

vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re