Hello everyone
Hopefully someone can give some steer.
I have a column with lots and lots of data.
Under 3 rows of header.
Ideally I'd like to split the data into multiple columns
I need every 15 rows to go to a new column on a different sheet except annoyingly the new columns are spaced every other one.. ie D F H J.
Also the first 3 rows in the new column are headers.
Andy ideas would be much appreciated.
I have assumed that you have your data on Sheet1 in column A, with headers in rows 1 to 3, with data down the column.
On your other sheet, enter this in cell D1
=Sheet1!$A1
and copy to D2:D3. Then in D4, enter the formula
=INDEX(Sheet1!$A:$A,ROW()+(COLUMN()/2-2)*15)
and copy that to D5:D18.
Then copy D1:E18 - note that you need to copy the blank cells in column E as well as the formulas in D - then select from F1:????18 where your selection is an even number of columns that is at least =2*COUNTA(Sheet1!A:A)/15 columns wide. IF the headers already exist in rows 1 to 3 of those columns, then start your copy and paste on row 4.
OR ---------
You can use a macro - same assumptions, plus that the other sheet is named Sheet2:
Please Login or Register to view this content.
Last edited by Bernie Deitrick; 03-28-2017 at 05:16 PM.
Bernie Deitrick
Excel MVP 2000-2010
Thanks for your reply.
I'll try both tomorrow.
With your first solution. Since I would be copying the blank cells of all the even columns that won't really work.
Although I never mentioned it..I hope to use the same fix to copy the data from a second long list into every other
Ie sheet 1 column a split every 15 rows to even columns a/c/e on sheet 2........ and sheet 1 column b split every 15 to odd columns on sheet 2 b/d/f
I'll try to adapt your macro... many thanks
Please Login or Register to view this content.
thanks very much for your help.
I tried to get it to work but get a runtime error.
Ive attached the spreadsheet > trying to get column "AA" on Sheet3 to split into Sheet4 column "D" then every other.
and column "AB" on Sheet3 to split into Sheet4 column "E" then every other.
please check excel sheet name & sheet name in vba macro are different.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
ahh ok - ooops. thank you
ive fixed that - new problems:
is there a way to copy the value only from the source? rather than the formula in that cell?
when pasting onto the "dec-degrees" sheet can it start in column D row 4?
attached latest
You can do simple one way. In "Data" sheet in column insert helper column "AJ" & "AK" with simple formula =AA2 (IN "AJ2") & =AB2 (in "AK2) & copy paste down.
After that select complete AJ & AK coloum "copy" & in same place "paste special" as value.
After that go to VBA code macro "AA" to "AJ" & "AB" to "AK".
cancel this
i reformatted the input and output to be linear in columns.
problem solved./
thansk for everyones help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks