The code does almost everything but creates the original data. Just set the
first three lines of code to the correct Rows and columns and the code does
the rest. the code assumes the original data is in sheet 1 and the new data
is created iin sheet 2. You can change the names of the sheets as required.
the code has been fully tested.
the code searches row 1 of sheet 2 to find the Make of the automobiles. If
it finds the Make it adds the model to the end of the list. If it doesn't
find the Make it adds it to the header Row on Sheet 2 and addss the Model to
the first Row.
Sub make_table()
Sh1StartRow = 1 'first row of data after header
Sh2FirstRow = 2 'leave at least one row for headers
Sh2NewCol = 1 'enter column where you want data to start
Sh1RowCount = Sh1StartRow
With Sheets("Sheet1")
Do While .Range("A" & Sh1RowCount) <> ""
Make = .Range("A" & Sh1RowCount)
Model = .Range("B" & Sh1RowCount)
With Sheets("Sheet2")
Set c = .Rows(1).Find(what:=Make, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Cells(1, Sh2NewCol) = Make
.Cells(Sh2FirstRow, Sh2NewCol) = Model
Sh2NewCol = Sh2NewCol + 1
Else
LastRow = .Cells(Rows.Count, c.Column).End(xlUp).Row
.Cells(LastRow + 1, c.Column) = Model
End If
End With
Sh1RowCount = Sh1RowCount + 1
Loop
End With
End Sub
"GeraldM" wrote:
> Would appreciate help with the following problem:
>
> I have data in two columns - "Make" & "Model"
> I need to convert the into an array with:
> 1) a column for each "Make"
> 2) each model in the correct column.
>
> Example:
>
> Change the data from that shown below: (Note: The number of "Makes" is not
> limited to three).
>
> Make Model
> ------- ----------
> Ford Falcon
> Ford Focus
> Ford Fiesta
> Ford Mondeo
> Ford Anglia
> Holden Commodore
> Holden Torrano
> Holden Monarro
> Holden Astra
> Kia Rio
> Kia Carnival
>
>
> Need to change the data to look like below: (Note: I need to automate this
> (using VBA) for others to use, so I don't want to manually transpose the
> data).
>
> Ford Holden Kia
> -------- ------------- -----------
> Falcon Commodore Rio
> Focus Torrano Carnival
> Fiesta Monarro
> Mondeo Astra
> Anglia
>
|
Similar Threads
