• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

For print, distribute data base on number of columns

Dear all experts,

After long, need your little help....request to shorten my manual effort on splitting & printing .
Daily , often ,there are need to update & print long stock list, if data is 2 column like quality no & stock in Mtrs. (rows always dynamic, not fix how many rows) split/distribute total rows /4 & put 2nd part of 2 columns next to 1st, put 3rd part next to 2nd, put 4th part next to 3rd left to right (2 column group) so in 1 A4 size page contain 4 part of 2 columns group (total 8 columns in 2 column group) .... , count of pages is based on how many rows you have. So salesman tick mark on necessary items on printed list for take order based on proper stock item.

If data have 3 column wide, then require 3 parts of 3 columns group, ( total 9 columns ) if data is 4 columns wide then require 2 part of 4 column group...(total 8 columns in 1 page in 2 group) of if data is 5 column wide then not need to split..5 columns, not need to print side by side vertically in 2 part , (total 10 columns on 1 page? No.....please no) because losing readability & uncomfortable for tick mark..so if 5 column data , there are no need to do splitting, no macro require, & not need to generate this thread

Unfortunately, each sales person' s need is different, base on database..that how many columns they need base on different product criteria...(some one want qualy no, units with rate, some one with rate & with unit length...etc..)

In other words if data have heading, , 2 columns, 100 rows, then first part become 25 rows, next 26 to 50 rows data(of course 2 columns) , shift to 3rd column , next 51 to 75 row"s data ,shift on 5th column , next 76 to 100 rows shift on 7th column so rows distribute evenly , & I can print 2 columns group's data in 4 part on 1 paper...( obviously if 1350 lines, then number of pages increase) ..

Heading must be carry on on each grouped data.

Daily, multi time, updated stock need to print & hand over to diffrent salespersons..as per their requirements, manually its take hard effort & time, how can be done with macro?

Hope I can describe well my situation...please help...
Regards,
Chirag Raval
 
Last edited:
Dear & Respected Experts,
I apologize as about long thread, but matter is just cut -paste...if 100 rows & 2 column, , cut 26 t o rest below , paste it on next column. ,now on this column, cut 26 to all below rest rows on next column, on this column, same do 4times,

If 3 columns, &100 rows, do same above steps but 3 times, (100 rows /3) about 33 rows should be shift on next column..I will be upload file at row data & result data..soon..

Regards,
Chirag Raval.
 
Chirag R Raval
Many things are possible
... but without even a sample Excel-file which shows You named 'steps' this could be a challenge.
Dear Respected sir @vletm ,
Many thanks for your response...
sorry to describe my problem in long written story in my main thread... but i can not find way that how can i describe my problem in written way ?
& as per demand for this thread for sample workbook, i attached here with 3 Workbooks, in each 1st sheet have row data & 2nd sheet display result .

you can laugh at my data & require result,like basic level, this is just cut & paste ...but that basic level ,give me pain if repeated many time in the day....Hope you can help me.

Regards,

Chirag Raval
 

Attachments

Last edited:

vletm

Excel Ninja
Chirag R Raval
Where is information that how many rows should be in one set in Result-sheet?
... actually, You do not need Result-sheet!
You wrote something about printing ... how many rows can be in one sheet? Is it eg A4-size paper?
 
Chirag R Raval
Where is information that how many rows should be in one set in Result-sheet?
... actually, You do not need Result-sheet!
You wrote something about printing ... how many rows can be in one sheet? Is it eg A4-size paper?
Dear Sir @vletm ,

How many rows in A4 Page is not fixed, (you can apply print preview & see in ever result sheet that they are look similar & printable on A4 page, also you can not control count of total pages because its based on total rows of whole database..

you can see that after print of whole database on many pages (can be seen in print preview) if you staple that all pages, 1st pages' first column row's series/index, continues on 2nd page's first column, , & 2nd page's 1st column's series/index continues on 3rd page and so. on.... till data end...& hence total pages count based on how many rows in database) .

Actually division of rows count directly based on columns count.

(1) if columns count is 2, then total rows/4 because there are need 4 blocks of group of 2 columns data in that page ,
(2) if columns count is 3, then total rows/3 because there are need 3 blocks of group of 3 columns on that page
(3) if columns count is 4 then total rows/2 because there are need 2 blocks of group of 4 columns on that page

since you can see that every block's end (last line of every block) is in parallel, except last block because last block may have less lines ...

Also note that every block have heading repeated, ... so division number may higher then actual rows count its based on how many block of data should be print on page with heading...

Because this is really practical & real world requirement it can be implement as in 1 page data ,you can print about estimated 3 columns & 30 rows,in firts colum group on 1 single page, 3 columns & next 31 to 60 rows in 2nd columns group in same 1 single page beside 1 columns group, , 3 columns & next 61 to 80 rows on 3rd columns group beside 2nd columns group on same 1 single page may be it called "down then over construct." .....so series/index start for 1 st 80 or 90 can cover in 1 page ... & next 91 & more on 2nd page...but that is may be next level of this thread...but till now, current requirement is just splitting data based on columns count...

Please revert if you have want further clarification about...
Many thanks
Regards,

Chirag Raval
 
Last edited:

vletm

Excel Ninja
Chirag R Raval
Your: Please revert if you have want further clarification about...
I won't ask more, I don't have 'time' to read ... Your ... short replies.
I did [ Do It ]-buttons to two sheets - okay?
Press those one-by-one and ... Will You get something - someway - that You did expect?
(( previous version has one typo ))
 

Attachments

Marc L

Excel Ninja
How many rows in A4 Page is not fixed
Hi, as per Excel basics like any beginner can - must ! - operate your forgot to well configure the print of the RESULT sheet !​
Like according to the printer the margins and the header to repeat on each page.​
Once done just via a print preview you can easily see how many rows can be printed by page, at child level …​
The same to know how many columns you can print …​
With 1 cm margin on all sides with the actual layout it may be a maximum of 53 rows on 4x2 columns​
but depending too on the width of the empty columns (around 7) …​
So it's up to you to give us the right configuration you really need as, after all, we are not on any mind readers forum !​
Edit : it's easy to find out via VBA the rows # by page if the source data sheet is yet well configured for the print …​
 
Dear & respected both experts @Marc L & @vletm ,.

Thanks for your response,. & I am sorry for that , in hurry, i can not makup / format my result sheets (with proper margin, orientation, symetrically well structured lookout hat how nice they look on paper.

I will be check which sir veltm attached on above & revert soon..

If require, or stop some where, I will be surly revert. with my result sheet with require structure..

Till now/then ,. Thank you both respected sirs..

Regards,
Chirag Raval
 
Last edited:

vletm

Excel Ninja
Chirag R Raval
My sample do not need any specific setting from Your printing.
You could even do 'all-in-one-paper' if needed.
You can name number of sets of columns as You needed (with the newest version).
If You would like to have some changes to it
... then max five lines Your writings, I won't read more.
 

Marc L

Excel Ninja
in hurry, i can not makup / format my result sheets (with proper margin, orientation, symetrically well structured lookout hat how nice they look on paper.
You misread : as the maximum columns # & rows # necessary to fit a page can be detected under VBA​
based upon the current source data sheet print page setup (or from a blank/empty result sheet, whatever)
so again you just have to configure the page setup of the source data sheet ! (so not the result sheet or if blank …)
As I wrote :​
Like according to the printer the margins and the header to repeat on each page.
When running my procedure, there is no question as all is automatic, just based on the current page setup !​
According to your 2 columns attachment, you expect a 2x4 columns result but with the current page setup​
the last column does not fit on the same page as previous columns so the result can only be a 2x3 columns …​
But once any smart beginner Excel user well configure the page setup (so at child level)
the expected 2x4 columns result is reached with exactly the same procedure without amending its code !​
As I met an issue - maybe a bug - I have to test on a previous Excel version if it's the same​
- so it could be an Excel normal behavior - then I could check if my workarounds still work …​
 
Chirag R Raval
My sample do not need any specific setting from Your printing.
You could even do 'all-in-one-paper' if needed.
You can name number of sets of columns as You needed (with the newest version).
If You would like to have some changes to it
... then max five lines Your writings, I won't read more.
Dear Sir @vletm ,
please forgot page setup, i manually adjust it , & as per just require splitting as required columns sets/groups ...& Thank you for your working code. its work like a charm ... it is splitting data as per required column groups/sets but it distribute serialized data in horizontally, Original data is vertically sorted , so if sorting can maintain vertically then this thread meet the solution..
Please help..
Regards,
Chirag Raval


Dear Sir @Marc L ,
Thank also for your co-operation for spread light on matter that before ask question if question or answer covering print /page break related task, first requirement from user side that your source data have properly applied page setup on source sheet...because who want to help you construct their effort based on you current page setup... this should be in mind for person who want to require help.

Because , this thread's requirement is only properly splitting data , all page setup related task can be finalize by manually or by vba way hence i do not mention that portion due to try to make this thread simple & single task.
Thank you again sir..

Regards,

Chirag Raval
 

Marc L

Excel Ninja
Chirag,​
according to your 4 columns attachment, my 'scientist' way result is the 4x2 expected one​
but my empirical way reaches a 4x3 result with a well fit page setup, just for info …​
 
Chirag,​
according to your 4 columns attachment, my 'scientist' way result is the 4x2 expected one​
but my empirical way reaches a 4x3 result with a well fit page setup, just for info …​
Dear Sir @Marc L

sorry to mention above about your code effort , can i test your procedure? if you don't mine, can i learn another as your way.?
please .

Regards,
Chirag Raval
 
Chirag R Raval
Did You even try to modify it?
As You know ... someone likes that kind of layout ... someone other kind of layout.
Did You clearly write do it matter?
I added [ Do It 2 ]-button for those who would like other kind of layout.
Dear Sir @vletm ,

Just Amazing... As Require, ..Many Thanks & appreciate your invaluable effort.. Now i have 2 style for distribute data...
Though , i already update in your code about Application.Calculation = xlCalculationAutomatic,Application.EnableEvents =false,Application.ScreenUpdating = false, can we achieve more faster method? just for learning if you want..
Regards,

Chirag Raval
 

vletm

Excel Ninja
Chirag R Raval
Did You asked something more faster?
If You have time to write ... write ... and same time to be hurry then ... hmm?
Maybe manually would be the most fastest way to do those after fit-to-page ... it depends, what do really need?
If You will add something ... then You should know those affects too.
 
Dearvsi
As per post #14 at least choose which way you wanna see !​
Dear Sir @Marc L ,
Chirag,according to your 4 columns attachment, my 'scientist' way result is the 4x2 expected onebut my empirical way reaches a 4x3 result with a well fit page setup, just for info …
Can we see result of your empirical way? If I attached my 4 co!umn source after proper Page setup?

Regards,
Chirag Raval
 
Chirag R Raval
Did You asked something more faster?
If You have time to write ... write ... and same time to be hurry then ... hmm?
Maybe manually would be the most fastest way to do those after fit-to-page ... it depends, what do really need?
If You will add something ... then You should know those affects too.
Dear Sir @vletm ,
May be loop over array ? or dictionary? Or Evaluate? Can i Hope something like another approach.?
Regards,
Chirag Raval
 

Marc L

Excel Ninja
Can we see result of your empirical way?
Ok but as a prototype after the 'Scientist' version I have to ameliorate it according to the specific workarounds​
'cause of the inconsistant Excel / VBA break pages management behavior (different from a version to another one !) …​
 

Marc L

Excel Ninja
So that simplifies a bit the code and it will be a mix of both ways for a better efficiency …​
It works according to the source data columns width, the paper size, margins and top header to repeat, …​
… so to be managed by an user.​
According to your 4 columns attachment, as it is the result is a 4 columns x 2 blocks.​
After operating a well fit page setup by reducing the margins to a reasonable one centimeter (0.4 inch) for example,​
the result should turn to a 4 columns x 3 depending on an empirical parameter, to be continued …​
If it works for your 4 columns workbook, it works too for your other 2 & 3 columns.​
(I'm still testing - when I have time - the workarounds between different computers screen setup, an Excel well known 'issue'.)
Another question :​
where should be located the code : within the source data workbook (so to be saved as .xlsb / .xlsm) or explain the user context ?​
 
Top