Hi, from the example below I would like to take the data from worksheet1 and put into worksheet2 but limit the length of a list (the real spread sheet has over 1000 rows and i would like them in 4 sets of 250 versus the example I provided). Is there an array or macro that would make this work (keeping the formatting)...I am not great with macros, so that approach might need a little explanation.
Last edited by corpfinanalyst; 05-04-2013 at 10:54 PM. Reason: Added example
Hello,
This is a fun sample I made.
On E2, just type in the number 1, 2, 3 or 4 and see the change.
Unfortunately that I cannot get the format over.
(copy pasta from Ford)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
Regards,
Lem
Hey that's cool, but I need the formatting copied over (Critical part), it needs to be on a specified worksheet (in this case sheet2, but it might be called something else and start at a specified location B2 or c3, etc)...also when I typed in 3, I would have expected Rob and GE under J&K (set 2). Shouldn't the previous column be full before it moves to the next?
Thanks so much, a cool approach!
One other problem, I just noticed that when you enter 2, 3, or 4 it is sorting to the right and not down...can that be fixed?
About the format transferring, that can only be done through VBA.
About the sorting, check this sample instead.
Note that I make it so that when you change the number, the table will pull enough data so that the number of rows on each table is about the same as much as possible.
Last edited by Lemice; 05-05-2013 at 01:56 AM. Reason: changing attachment
Thank you so much , but it doesn't seem to work. When you choose two columns it fails to sort in order and only gets worse as there are more columns...with regards to the formatting, I can solve that issue through conditional formatting.
Please help me get the sorting correct!
Duplicate Post...Sorry!
Oh my god, I changed the formula on first line and forgot to change the rest accordingly ...
I have changed the attachment on post #5, please check it again.
As an option- a code
I have copied the data you have in sheet1 up to a couple of hundreds rows to test the code. There is a button on sheet1 and the result is in sheet3.
Lemice, I have a question...it seems to work as described and I can use conditional formatting, however when I go to three columns, I would expect Mike and Google to be under the second set and when I go to 6 columns the third set has two names/companies, but the fourth has three names/companies...is that fixable?
the macro is cool and I see what you did in the code so i can modify it, but for the novice - can you modify it to ask (when you press "Run") how many rows I want in the column and autosize based on that parameter?
Hello,
It is dividing the data into table so that the number of data in each table would be as equal as possible.
Think of it this way:
If you want to divide 16 rows into 4 columns, of course each column will have 4
If you want to divide 16 rows into 5 columns, you want it to be 4 3 3 3 3 as number of rows in each table.
Assuming you want to transfer one in the 3rd into 2nd so that it will be 4, and 2 from the 4rd to the 3rd so it will be 4 and so on, it will be
4 4 4 4 0
And it will be 4 columns, not 5 anymore.
Similarly, when dividing into 3 columns, the best way would be 6 5 5, making it 6 6 4 is a bit ... unbalance ...
Lem,
I get what you are saying and your explanation makes sense, but 6,6,4 would be better if I wanted three columns because I am trying the maximize the data per column. The macro seems to accomplish that, but now I am wondering if it can be done using an array...
The helpful thing with regards to the macro would be able for the user to define the number of rows per column without having to modify the code (as described above).
Amended code
Please Login or Register to view this content.
Okay, for the macro one more thing, I tried applying to three columns of data, and it generally works, but on the 2nd set of data it overwrite the header. Here is the code, less the the modification above, can someone fix it?
Sub columnstocol()
Dim i As Long, ms As Worksheet, LR&
Application.ScreenUpdating = 0
Set ms = Sheets("FRL Summary Sheet")
NR = 2
With Sheets("FRL Input")
LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
For i = 2 To LR Step 75
.Cells(2, 2).Resize(, 2).Copy ms.Cells(3, NR)
.Cells(i, 3).Resize(75, 3).Copy ms.Cells(3, NR).Resize(75, 4)
NR = NR + 5
Next
Application.CutCopyMode = 0
Application.ScreenUpdating = True
End With
ms.Columns.AutoFit
ms.Activate
Set ms = Nothing
End Sub
Corp,
Please use code tags with your code.
It is difficult to comment as I could not see your data, but try change the line
Please Login or Register to view this content.
Sorry, I tried to do that but it didn't seem to work, I will attach the file.
Try this one
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks