Search : in
By :

Moving data from rows into columns for every

Last answer on Oct 20, 2009 8:45:50 am BST Rick, on Jun 30, 2009 4:23:23 pm BST 
 Report this message to moderators

Hello,

I have data in a single column and would like to transpose it into a row for every three rows with a space delimiter, for example; The column of data varies in the amount of data each time.

A
B
C
D
E
F
G
H
I
J
etc

into

A B C
D E F
G H I
J etc

Thank you in advance for any advise or solution that will help me solve this issue.
Rick

Configuration: Windows XP Internet Explorer 7.0

Best answers for « moving data from rows into columns for every » in :
Adding data from one column using another ShowAdding data from one column using another Issue Solution Note Issue I am having a real problem with an Excel formula. I have a worksheet with variable rows and a number of columns. I need to add together the values of particular...
EXCEL: The INTERSECTION function ShowEXCEL: The INTERSECTION function You have a table consisting of rows and columns. Want to know the value of the cell at the intersection of a particular row / column in your table Example: Your table: - starts C3 to F3 ... for...
Transforming columns into lines ShowTransforming columns into lines Example Limitations It is endemic for most Linux tools to work with lines, but not with columns (sed, awk, grep, etc..). However, it may happens,that you have a file where the data should be read in...
Download TaskBar Shuffle ShowQuite simple but practical: this program allows to move the tasks into the taskbar by simple drag and drop. It can also group automatically windows (for example, to keep together the various windows of Internet Explorer), or to close a window by...
Tables ShowUsing Tables Often, it is helpful to present information in a more structured manner than a list. Tables can display this information in rows and columns. The tables are defined as series of rows. A table must follow the rules below: The table is...
OOP - Data encapsulation ShowThe concept of encapsulation Encapsulation is a way of organising data and methods into a structure by concealing the the way the object is implemented, i.e. preventing access to data by any means other than those specified. Encapsulation...
Ask your question Reply

1

venkat1926, on Jul 1, 2009 2:37:03 am BST

Suppose the data are in A2 down with A1 having column headings.

try this macro

the results will be in columns C to E

the macro is
[code]
Sub test()
Dim rng As Range, m As Integer, c As Range
Columns("c:E").Delete
m = 3
Set rng = Range(Range("a2"), Range("a2").End(xlDown))
Set c = Range("a2")
Do While c <> ""
'MsgBox c.Address

Range(c, c.Offset(m - 1, 0)).Copy
Cells(Rows.Count, "c").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
Set c = c.Offset(m, 0)
Loop


End Sub
/code

   
Reply to venkat1926

3

Rick, on Jul 1, 2009 2:38:29 pm BST

Venkat1926

Thank you for taking the time to help me on this. I apologize that I was not clear in my support question, however, all of the data needs to be space delimited in a single column. Can you suggest a code change that will take each of the three rows, place a space between each and place them in a separate column. Thank you again as this will save hours of time for me.

Rick

   
Reply to Rick

6

venkat1926, on Jul 2, 2009 1:04:34 am BST

Try this macro test1 which is an amended version of my previous macro

Sub test1()
Dim rng As Range, m As Integer, c As Range, C1 As Range, x As String
Columns("c").Delete
m = 3
Set rng = Range(Range("a2"), Range("a2").End(xlDown))
Set c = Range("a2")
Do While c <> ""
'MsgBox c.Address
x = ""
For Each C1 In Range(c, c.Offset(m - 1, 0))
x = x & " " & C1
Next C1
Cells(Rows.Count, "c").End(xlUp).Offset(1, 0) = Trim(x)

Set c = c.Offset(m, 0)
Loop


End Sub

   
Reply to venkat1926

7

Rick, on Jul 2, 2009 2:27:45 am BST
  • +1

Venkat1926,

Thank you for helping me leverage this and save this soul from working extensive hours in massaging this data. I help I can learn more to help others like you have done for me.

Many thanks again,

Rick

   
Reply to Rick

9

 fishface, on Oct 20, 2009 8:45:50 am BST

This works brilliantly. I had a long column with 4 sets of data repeated and this helped me to transpose into 4 columns. great thanks keep posting!

   
Reply to fishface

2

mubashir aziz, on Jul 1, 2009 6:20:27 am BST
  • +1

Suppose your data is in A1:A50 or A500 ........ then a work sheet function approach is .... 

B2=OFFSET(A1,2*(ROW()-1),0)&" "&OFFSET(A1,2*(ROW()-1)+1,0)&" "&OFFSET(A1,2*(ROW()-1)+2,0)


and drag it down .....





Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz

4

Rick, on Jul 1, 2009 2:55:56 pm BST

Mubashir aziz

Thank you for your response however, I need to automate this on a weekly cycle. The data comes in a text format which I suck into the spreadsheet and then have automated script to massage the data. The last part that i am having difficulty in is to merge every third row and space delimit it into another row or worksheet. From there the data gets exported out again into a text file and imported into the application.

Thanks,
Rick

   
Reply to Rick

5

Rick, on Jul 1, 2009 2:57:26 pm BST

Mubashir aziz

I meant to say merge every three rows not every third row. My appologies.

Rick

   
Reply to Rick

8

mubashir aziz, on Jul 2, 2009 5:56:29 am BST

I think my formula just giving you the result as you are getting from macro !!!! Sorry i'm completely stumped. Can you give me some example if you are still unable to get the solution ...

Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz
Ask your question Reply
They need your help