Quote:
Originally Posted by joeu2004[_2_]
I wrote:
"sumesh56" wrote:
is it possible to connect something with the rollnumbers?
Yes. I did not have the benefit of this design information before.
(Re)Download download "test seating plan 210314.xlsm" from
https://app.box.com/s/f7yddu2xcrki8wx5mr8t. I have updated the
marco to take advantage of the meaning of the roll numbers.
Interestingly, with the example data, the original version that does not
sort roll numbers is about 26% faster as written, and 12% faster when we
toggle Application.ScreenUpdating.
I emphasize "with the example data" because I suspect the implementation
that sorts would be faster with some larger data. But perhaps it must be
__much__ larger data. I did not try to determine the break-even point.
Neverthelss, I would stick with the original non-sorting implementation
because it might be simpler to understand.
The file "test seating plan 210314.xlsm" at
https://app.box.com/s/f7yddu2xcrki8wx5mr8t now contains both
implementations.
I wrote:
And it is always prudent to toggle Application.ScreenUpdating, Calculation
mode and EnableEvents.
Application.ScreenUpdating: yes. An improvement of 57% to 64%.
Application.Calculation: no, surprisingly. A degradation of 19% to 26%
when we toggle ScreenUpdating.
But I'm splitting hairs. It is a maximum difference of only 30 milliseconds
(on my computer; YMMV) for the example data when we toggle ScreenUpdating.
(I use the more-accurate performance counter to measure time, not Timer,
which can be off by as much as 15.625 milliseconds, not suitable of
measuring differences this small.) |
thanks for the reply .a suggested i have changed the titles of column B as ROOM sheet seatingplan and CLASS in distribution.for better understanding of the situation i need to further explain.
kindly refer to attached file renewed seating plan220314 edited.there are three sheets on that workbook. sheet named seating plan -sheet named desired output in which i have shown the desired output. the third one sheet named distribution is for displaying the result.
1)only to copy the contents from B:E
2)both the sheets are identical except the column headers.
3)lets try a few lines in the output sheet.
4)i want something like this---
a)IF seatingplan!B4:E7,"=601"and "<=650",then copy
and paste the contents from seatingplan!B4=distribution!C4 and seatingplan!D4:E7=distribution!D4:E4.
b)IF seatingplan!B12:E15,"=601"and "<=650",then copy
and paste the contents from seatingplan!B12=distribution!C5 and seatingplan!D12:E15=distribution!D5:E5.
c)IF seatingplan!B22:E25,"=601"and "<=650",then copy
and paste the contents from seatingplan!B22=distribution!C6 and seatingplan!D22:E25=distribution!D6:E6.
d)IF seatingplan!B33:E36,"=601"and "<=650",then copy
and paste the contents from seatingplan!B33=distribution!C7 and seatingplan!D33:E36=distribution!D7:E7.
5)If we succeed in putting the If formulas correctly or any other suitable syntax, the distribution sheet will be filled in up by excel.