Converting multiple columns to rows

Question

Hi,

Is there any way to convert a long rows to columns like below?

               A                            B                      C               D                          E                 F               G                 H

1         HUSBAND         DOB                  WIFE      DOB                     CHILD1       DOB       CHILD2        DOB

2         xyz                  14may2011           abc         12may2011            pdf                                                  8dec2011

3        CDF

This is A column husband, B his date of birth, C wife, D her date of birth and so on.. Some cells might be empty if there are no children or no wife or dob is missing.

I want to have

         A                          B

1  husband           DOB

2        wife              DOB

3       child1           DOB

4       child2           DOB

5 next husband    DOB       ( next husband meaning CDF "A3" and then his family details )

6

Details are not just from A to H, it goes upto Q. Details like policy no ph no, etc etc.

But, its arranged properly

        A         B        C      D           E         F              G                   H              

husband dob policy  ph no  WIFE  DOB      POLICY           PH NO

I want to have names in one column, DOB in another, policy in next, ph no in next and do on...

TIA

Answer 1

What I would do when faced with a file like that is cut and paste, or use a macro to do it. If you need to do this to multiple files, then a macro is better. Since your number of details can vary from 2 to ?, writing the macro to account for that is best: for your first example, answer 3 when prompted.

Bernie

Sub TestMacro()

Dim i As Integer

Dim col As Integer

col = InputBox("How many columns per group?")

For i = 1 + col To ActiveSheet.UsedRange.Columns.Count Step col

Intersect(ActiveSheet.UsedRange, Columns(i).Resize(, col)).Cut _

Cells(Rows.Count, 1).End(xlUp)(2)

Next i

End Sub

Answer 2

Hi, 

try this code

you have that you want in a New Worksheet.

1) press ALT+F11 to open the Visual Basic Editor

2) select Insert > Module

3) Copy and paste the code below on the right

4) close the VBE

Sub myConvert()

Dim WS, newWS As Worksheet

Dim t1, t2, t3, r  As Long

Application.ScreenUpdating = False

Set WS = ActiveSheet

Set newWS = Worksheets.Add

r = WS.Cells(Rows.Count, 1).End(xlUp).Row

t1 = 0

t2 = 0

t3 = 0

For i = 1 To r

For j = 1 To 7 Step 3

t1 = t1 + 1

newWS.Cells(t1, 1) = WS.Cells(i, j)

Next j

For j = 2 To 8 Step 3

t2 = t2 + 1

newWS.Cells(t2, 2) = WS.Cells(i, j)

Next j

For j = 3 To 9 Step 3

t3 = t3 + 1

newWS.Cells(t3, 3) = WS.Cells(i, j)

Next j

Next i

Application.ScreenUpdating = True

End Sub

To run the macro...

1) press ALT+F8 to show the Macros window

2) select the macro "myConvert"

3) press Run.

As number of columns is a multiple of the number 3, try

this code, which is generalization of the previous one.

Sub GeneralCode ()

Dim WS, newWS As Worksheet

Dim t1, t2, t3, r, c  As Long

Application.ScreenUpdating = False

Set WS = ActiveSheet

Set newWS = Worksheets.Add

r = WS.Cells(Rows.Count, 1).End(xlUp).Row

c = WS.Cells(1, Columns.Count).End(xlToLeft).Column

t1 = 0

t2 = 0

t3 = 0

For i = 1 To r

For j = 1 To c - 2 Step 3

t1 = t1 + 1

newWS.Cells(t1, 1) = WS.Cells(i, j)

Next j

For j = 2 To c - 1 Step 3

t2 = t2 + 1

newWS.Cells(t2, 2) = WS.Cells(i, j)

Next j

For j = 3 To c Step 3

t3 = t3 + 1

newWS.Cells(t3, 3) = WS.Cells(i, j)

Next j

Next i

Application.ScreenUpdating = True

End Sub

Answer 3

Excel 2010 (only) PivotTables, Tables

Consolidate and rearrange Tables.

Variable number of details and

variable number of names in rows.

With multiple sheets.

With macro.

http://c3017412.r12.cf0.rackcdn.com/05_11_11.xlsm

If you get *.zip, don't unzip, just rename *.xlsm

Answer 4

This

name1   dob1    policy1`   name2    dob2  policy2    name3     dob3     policy3

name4   dob4    policy4    name5    dob5  policy5    name6     dob6     policy6

name7   dob7    policy7    name8    dob8  policy8    name9     dob9     policy9

needs to get converted to this

name1     dob1     policy1

name2     dob2     policy2

name3     dob3    policy3

name4   so on..  so on..

name5

name6

name7

In some sheets i have just 2 details, name and dob. In some i have 3 details. Name dob and policy. Some might have 4. I thought if you could guide me for 2 i would be able to work on for the rest.

Details are in perfect order though. Like if i have 5 details.

A               B         C         D           E        F           G           H           I           J          K        L

name1 dob1 policy1 phno1 add1 expiry1  name2  dob2 policy2 phno2 add2 expiry2

name3 dob3  policy3 ........................and do on...

Above i have details through column A to L. But A to L is not fixed. Some sheets can have

"name3" details in M through R..

Answer 5

What columns have the policy number details?

Bernie