Converting a Grid (or range) of Data to a List
 

Hi -

I need help converting the following Grid of data into a list.

Sample of my data is below: ( I have A LOT of lines of data)

A B C D E F G
1 Name Age Gender 1/1/2006 2/1/2006 3/1/2006 4/1/2006
2 Bobby 23 M $4 $5 $6 $7
3 Allen 21 M $2 $2 $2 $2
4 Nancy 20 F $8 $9 $10 $2
5 Jennifer 19 F $1 $1 $1 $1
6 Susan 18 F $3 $4 $6 $7
7 Emily 17 F $3 $3 $3 $4

I want to put the Dates and the $ dollar amounts in one column and
have the name, age and gender repeat.

Basically I want the data above to look like the data below:

Name Age Gender Date Amount
Bobby 23 M 1/1/2006 $4
Bobby 23 M 2/1/2006 $5
Bobby 23 M 3/1/2006 $6
Bobby 23 M 4/1/2006 $7
Bobby 23 M 5/1/2006 $8
Bobby 23 M 6/1/2006 $5
Allen 21 M 1/1/2006 $2
Allen 21 M 2/1/2006 $2
Allen 21 M 3/1/2006 $2
Allen 21 M 4/1/2006 $2
Allen 21 M 5/1/2006 $3
Allen 21 M 6/1/2006 $4

Does anyone have any tips or suggestions on how I can do this using
formulas, macros, vba or access.

Thank you for your help.

Converting a Grid (or range) of Data to a List
 

One way:

Option Explicit
Sub testme01()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCol As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim oRow As Long
Dim CurWks As Worksheet
Dim NewWks As Worksheet


Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("A1").Resize(1, 5).Value _
= Array("Name", "Age", "Gender", "Date", "Amount")

With CurWks
FirstCol = 4 'keep the first 3 static
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
oRow = 1
For iRow = FirstRow To LastRow
LastCol = .Cells(iRow, .Columns.Count).End(xlToLeft).Column
For iCol = FirstCol To LastCol
oRow = oRow + 1
NewWks.Cells(oRow, "A").Resize(1, 3).Value _
= .Cells(iRow, "A").Resize(1, 3).Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
Next iCol
Next iRow

End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

a wrote:

Hi -

I need help converting the following Grid of data into a list.

Sample of my data is below: ( I have A LOT of lines of data)

A B C D E F G
1 Name Age Gender 1/1/2006 2/1/2006 3/1/2006 4/1/2006
2 Bobby 23 M $4 $5 $6 $7
3 Allen 21 M $2 $2 $2 $2
4 Nancy 20 F $8 $9 $10 $2
5 Jennifer 19 F $1 $1 $1 $1
6 Susan 18 F $3 $4 $6 $7
7 Emily 17 F $3 $3 $3 $4

I want to put the Dates and the $ dollar amounts in one column and
have the name, age and gender repeat.

Basically I want the data above to look like the data below:

Name Age Gender Date Amount
Bobby 23 M 1/1/2006 $4
Bobby 23 M 2/1/2006 $5
Bobby 23 M 3/1/2006 $6
Bobby 23 M 4/1/2006 $7
Bobby 23 M 5/1/2006 $8
Bobby 23 M 6/1/2006 $5
Allen 21 M 1/1/2006 $2
Allen 21 M 2/1/2006 $2
Allen 21 M 3/1/2006 $2
Allen 21 M 4/1/2006 $2
Allen 21 M 5/1/2006 $3
Allen 21 M 6/1/2006 $4

Does anyone have any tips or suggestions on how I can do this using
formulas, macros, vba or access.

Thank you for your help.

--

Dave Peterson