"sumesh56" wrote:
What i am doing is first prepare the seating plan
then copy paste the concerned lines(col C:E) to the
distribution sheet. This is a tedious job.
Is it possible to ask excel to do the job for me?
[....]
Download: http://www.excelbanter.com/attachmen...tachmentid=953

Yes, but only if you ask Excel nicely. ;-)

Seriously, you need to explain the algorithm in some detail, even to us.
That is, what steps you take manually. It might go something like this (I
am guessing):

1. For each "class" in Distribution column B (B8, B16, B25, etc)
2. For each matching (distribution) "classes" in Seating column C
3. copy corresponding Seating "class" (column B) into next
Distribution column C
4. copy corresponding Seating "from" and "to" (columns D:E) into next
Distribution!D:E

For example, for Distribution!B8, we find matching C4, C12, C22 and C48 in
Seating. For matching Seating!C4, for example, we copy Seating!B4 to
Distribution!C8, and we copy Seating!D4:E4 to Distribution!D8:E8.

This presumes there is always sufficient space in each Distribution "class".
It is curious (odd) that some Distribution groups ("class") have 4 lines,
and some have 5 lines.

For one VBA implementation, download "test seating plan 210314.xlsm" from
https://app.box.com/s/f7yddu2xcrki8wx5mr8t. For posterity, I include the
macro text below.

The VBA macro corrects some copy-and-paste/edit errors that you made
originally, to wit:

1. In Distribution!C44, the seating class name is VII-A instead of IX-A.
See Seating!B96.
2. In Distribution!E107:F107, the numbers are 175 and 10 instead of 185 and
20. See Seating!E91:F91.
3. Additional Distribution!C108:E108 from Seating!B42:E42.

Also note that the order is different the Distribution tables starting at
B43 and B75. The order of VBA macro results is the order of appearance in
Seating. Apparently, you sorted the results. Is that necessary?

If yes, sorting can be added to the macro.



Design note: The formula in each group in Distribution column F should be
of the form:

=IF(COUNT(D8,E8)=2,E8-D8+1,"")

This makes it easier to copy and clear the Distribution data initially.

You could do similarly in each group in Seating column F.