moving data from vertical to horizontal possition

[leo]

I have one worksheet with data listed vertical:

day A - data name 1 - number
day A - data name 2 - number
day A - data name 3 - number
day B - data name 1 - number
day B - data name 2 - number
day B - data name 3 - number

I want to organize it to be:

header - data name 1 - data name 2 - data name 3
day A - number for 1 - number for 2 - number for 3

How can I do this quickly? I tried a pivot table but that just wanted to add
the number together. I just need to shift the data around. I have a lot of
data, like 30 names per day for 2 months, and several worksheets of it. Is
there a quick way to do this?

 

[ryguy7272]

I think this macro will give you what you want.

Sub ConcatData()
Dim X As Double
Dim DataArray(5000, 2) As Variant
Dim NbrFound As Double
Dim Y As Double
Dim Found As Integer
Dim NewWks As Worksheet

Cells(1, 1).Select
Let X = ActiveCell.Row
Do While True
If Len(Cells(X, 1).Value) = Empty Then
Exit Do
End If
If NbrFound = 0 Then
NbrFound = 1
DataArray(1, 1) = Cells(X, 1)
DataArray(1, 2) = Cells(X, 2)
Else
For Y = 1 To NbrFound
Found = 0
If DataArray(Y, 1) = Cells(X, 1).Value Then
DataArray(Y, 2) = DataArray(Y, 2) & ", " & Cells(X, 2)
Found = 1
Exit For
End If
Next
If Found = 0 Then
NbrFound = NbrFound + 1
DataArray(NbrFound, 1) = Cells(X, 1).Value
DataArray(NbrFound, 2) = Cells(X, 2).Value
End If
End If
X = X + 1
Loop

Set NewWks = Worksheets.Add
NewWks.Name = "SummarizedData"
Cells(1, 1).Value = "Names"
Cells(1, 2).Value = "Results"
X = 2
For Y = 1 To NbrFound
Cells(X, 1).Value = DataArray(Y, 1)
Cells(X, 2).Value = DataArray(Y, 2)
X = X + 1
Next
Beep
MsgBox ("Summary is done!")
End Sub

Before running the macro, make sure your data is assembled as such:
day A data name 1 number
day A data name 2 number
day A data name 3 number
day B data name 1 number
day B data name 2 number
day B data name 3 number

(i.e., must be in three columns; no '-' marks in the data set)

Regards,
Ryan---

 

[Reitanos]

It's hard to tell from your sample if the data is in different
columns. If so, then use the Transpose option in Paste Special.
Otherwise, you're going to have to separate the data first (unless you
want to write a macro).

 

[leo]

Thank you, I will try this out.

I think this macro will give you what you want.

Sub ConcatData()
Dim X As Double
Dim DataArray(5000, 2) As Variant
Dim NbrFound As Double
Dim Y As Double
Dim Found As Integer
Dim NewWks As Worksheet

Cells(1, 1).Select
Let X = ActiveCell.Row
Do While True
If Len(Cells(X, 1).Value) = Empty Then
Exit Do
End If
If NbrFound = 0 Then
NbrFound = 1
DataArray(1, 1) = Cells(X, 1)
DataArray(1, 2) = Cells(X, 2)
Else
For Y = 1 To NbrFound
Found = 0
If DataArray(Y, 1) = Cells(X, 1).Value Then
DataArray(Y, 2) = DataArray(Y, 2) & ", " & Cells(X, 2)
Found = 1
Exit For
End If
Next
If Found = 0 Then
NbrFound = NbrFound + 1
DataArray(NbrFound, 1) = Cells(X, 1).Value
DataArray(NbrFound, 2) = Cells(X, 2).Value
End If
End If
X = X + 1
Loop

Set NewWks = Worksheets.Add
NewWks.Name = "SummarizedData"
Cells(1, 1).Value = "Names"
Cells(1, 2).Value = "Results"
X = 2
For Y = 1 To NbrFound
Cells(X, 1).Value = DataArray(Y, 1)
Cells(X, 2).Value = DataArray(Y, 2)
X = X + 1
Next
Beep
MsgBox ("Summary is done!")
End Sub

Before running the macro, make sure your data is assembled as such:
day A data name 1 number
day A data name 2 number
day A data name 3 number
day B data name 1 number
day B data name 2 number
day B data name 3 number

(i.e., must be in three columns; no '-' marks in the data set)

Regards,
Ryan---

 

[leo]

Yes sorry for the over-simple example, I was in a hurry. There are three
columns, one with the date, one with the name, and one with the number. They
take up about 50 rows then repeat again. The whole thing takes up 70,000
rows. I will try the posted macro to hopefully work this data.

Leo