Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
moving data from vertical to horizontal possition
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
moving data from vertical to horizontal possition
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--- -- RyGuy "leo" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
moving data from vertical to horizontal possition
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). On Apr 28, 1:25 pm, leo wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
moving data from vertical to horizontal possition
Thank you, I will try this out.
"ryguy7272" wrote: 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--- -- RyGuy "leo" wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
moving data from vertical to horizontal possition
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 "Reitanos" wrote: 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). On Apr 28, 1:25 pm, leo wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cross Reference Vertical and Horizontal for Data | Excel Worksheet Functions | |||
Need lookup of data both vertical and horizontal | Excel Discussion (Misc queries) | |||
How to make Horizontal data go Vertical | Excel Worksheet Functions | |||
Excel moving horizontal columns to vertical | Excel Discussion (Misc queries) | |||
tranfere vertical data to horizontal tabel | Excel Worksheet Functions |