Arrange Data

[rumshar]

Hi All,
Good Evening(per IST)
In order to give some exercise to myself, I imported data from this forum(Notable members). Excel listed everything in column A. How can I arrange this data in database format?
Please see the attached file.
Right Side(col A) = Input
Right Side = Output

 

[Luke M]

As not everyone supplies Gender/location/age, I would suggest ignoring those columns. Here's a quick macro I wrote that can re-arrange the data from Sheet1 into Sheet2.

Sub DataMove()
Dim shIn As Worksheet
Dim shOut As Worksheet
Dim lastRow As Long
Dim c As Range
Dim xCount As Integer
Dim datagroup as Variant

'Set these names as appropriate
Set shIn = Worksheets("Sheet1")
Set shOut = Worksheets("Sheet2")

Application.ScreenUpdating = False
xCount = 1
With shIn
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For i = 1 To lastRow Step 9
    Set c = shIn.Cells(i, 1)

    With shOut
        .Cells(xCount, 1).Value = xCount
        .Cells(xCount, 2).Value = c.Offset(1, 0).Value
        .Cells(xCount, 3).Value = c.Offset(4, 0).Value
        .Cells(xCount, 4).Value = c.Offset(6, 0).Value
        .Cells(xCount, 5).Value = c.Offset(8, 0).Value
        datagroup = Split(c.Offset(2, 0).Value, ",")
        .Cells(xCount, 6).Value = datagroup(0)

        xCount = xCount + 1
    End With
Next i
shOut.Select
Application.ScreenUpdating = True
End Sub

 

[Luke M]

PS. Let us know if you find some interesting stats, or create a cool dashboard.

 

[SirJB7]

@rumshar
Hi!
I'd like to have the phone numbers of all the female Excel Ninjas, so if you achieve that please upload the file.
Regards!

 

[rumshar]

@Luke M
Tonnes of thanks to you for the macro....It gives me immense pleasure if I get something for free..
@SirJB7 definitely I will do that......its been two weeks since Valentine's day, you seem to be still in V mood....

 

[dan_l]

any forum that lists me as a notable member can't be very good.

 

[SirJB7]

@dan_l
Hi!
Checked my profile yet? Information tab.
Regards!

 

[dan_l]

ha! good call