1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

'Merge & Center' of cells based on value repetition

Discussion in 'VBA Macros' started by S. Das, Jan 24, 2018.

  1. S. Das

    S. Das Member

    Messages:
    88
    Hi, I need a help.

    In the attached file, in sheet1 column A data is given, which is came from sheet2 and there are boxes contain 2 consecutive cell (like A5+A6, A7+A8, A9+A10). If the data is same in consecutive boxes then the boxes will have to 'Merge & Center' and have to show the value.

    Thanks in advance for your time.
    -------------------------------------------------------------------------------------------
    Thread moved to VBA forum

    Attached Files:

    Last edited by a moderator: Jan 24, 2018
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,428
    This isn't possible without vba. Also, I'd strongly advise against using merged cells.

    It is nothing more than visual fluff and will have more detriment than benefit.
  3. vletm

    vletm Excel Ninja

    Messages:
    3,458
    S. Das
    You should move this to 'VBA Macros'.
    If 'Data' is character, do it handle like 1 or 5 or how?
    Is 'required' output always from 'A5'?
  4. S. Das

    S. Das Member

    Messages:
    88
    Is there any possible solution without merging??
    If possible then the solution is most welcome.
  5. Marc L

    Marc L Excel Ninja

    Messages:
    3,673
    Hi !

    What you joined in your sample is merging !
    No matter if it is just only for displaying,
    without any future calculation neither any kind of treatment.

    You can also draw objects like rectangle and write text within …

    As the smart way for data analyse is a number in a single cell
    without any blank between rows / columns.
  6. S. Das

    S. Das Member

    Messages:
    88
    How can I move this to VBA MACROS?? Help me.
    I have only to character type data SS and NH.

    Is it possible make range variable? because I want to print the sheets and there is a format for this
  7. S. Das

    S. Das Member

    Messages:
    88
    As it's a printable page format (predefined) I have to make like that. Whatever I have said I want this automatically, if possible.
  8. vletm

    vletm Excel Ninja

    Messages:
    3,458
    S. Das
    I tried to ask two questions ...
    ... without answers, it would be a challenge.
    ... and You made one new question.
    If 'the real layout' is something that You'll know,
    it would be quicker for You to send sample of that.
  9. S. Das

    S. Das Member

    Messages:
    88
    Here I am attaching the format. You have to do the solution for sheet3A and sheet 3B. For Sheet3A, data will come from sheet2A and for sheet3B data will come from sheet2B.

    Thanks again for your valuable time.
  10. S. Das

    S. Das Member

    Messages:
    88

    Attached Files:

  11. vletm

    vletm Excel Ninja

    Messages:
    3,458
    S. Das ...
    Yes, my valuable time is still waiting Your answers.
    Four new questions for You:
    Do You mean something like below?
    Screen Shot 2018-01-24 at 21.45.44.png
    What to do with '0's?
    Would those cells merge too (max 5 in one 'packet')?
    Are all 'Seat Plan's always same size?
  12. S. Das

    S. Das Member

    Messages:
    88
    I want this thing for column B, I' will hide column A will at the time of print. '0' is not a data for me, you can leave it.
    This is the official page format.
    Maximum 5 boxes that means repetition of the same number is maximum 5.

    And one more thing, If possible, that is one another code to unmerge them all with for column B, so that the same system can be used more than one time.
  13. vletm

    vletm Excel Ninja

    Messages:
    3,458
    S. Das
    ... it seems impossible to get answers ...
    There are two buttons ... use those!

    Attached Files:

    Chirag R Raval and S. Das like this.
  14. S. Das

    S. Das Member

    Messages:
    88
    It's like that what I want, but one thing left, that is the merged cells should show the number which is repeated.
    Example: If the repetition is 1...1...1 the it should show 1 in the merged cell.
    And also tell me the steps to insert the code and button in my main worksheet.

    Thanks again for your support.
  15. Marc L

    Marc L Excel Ninja

    Messages:
    3,673

    Just merge column B, not others from C to K ?

    Easier on a unique table in destination sheets …​
  16. vletm

    vletm Excel Ninja

    Messages:
    3,458
    S. Das
    ... And also tell me the steps to insert the code and button in my main worksheet.
    Ooops ... I gotta make a question again.
    What would be Your
    my main worksheet?

    Attached Files:

    S. Das likes this.
  17. S. Das

    S. Das Member

    Messages:
    88
    Now It's perfect.
    My main workbook is very large in size so that I send you a sample format. Now I want the same thing to implement in my main workbook which is a .xlsm format.

    Can u tell me how can I implement this thing in my main official workbook?
    Or can you please tell me how to assign macro contain private sub to a button??
  18. vletm

    vletm Excel Ninja

    Messages:
    3,458
    S. Das
    hmm ... with previous reply it was worksheet and now it's workbook ... okay!

    1) You open BOTH files
    2) You open BOTH files Thisworkbook (code)-page
    3) You copy all codes from 'my file's Thisworbook (code)-page to Your file's same page.
    4) You copy BOTH buttons from 'my file' to Your file
    5) From Your file, You edit both buttons Macro reference to Your file (=change file name in front of Macro-name)
    S. Das likes this.
  19. S. Das

    S. Das Member

    Messages:
    88
    Thank you so much
  20. S. Das

    S. Das Member

    Messages:
    88
    Sir, I face some problem, after implementing the solution in my maim workbook.
    It can't merge properly. It can only merge the first two cell like( b6 and b7) but nothing inside it.
    Can I upload a google drive link here?
  21. vletm

    vletm Excel Ninja

    Messages:
    3,458
    You can try ...
  22. S. Das

    S. Das Member

    Messages:
    88
    hi @veltm please see the below link, I have shared the main workbook.
    https://drive.google.com/file/d/1TmRGeREs8uoKbNOyR_2phqmud81pNjPY/view?usp=sharing

    Here my data sheet is sheet5A and Sheet5b, instead of 2A and 2B, and printable page range is sheet11A and sheet 11b.

    If possible you can make one thing, leave the data sheets( i.e, sheet5a and 5b) and make the code working only based on the values in column A.
    Last edited: Jan 25, 2018
  23. vletm

    vletm Excel Ninja

    Messages:
    3,458
    S. Das
    You face problem because Yourself ...
    1) ReRead Your #9 Reply!
    2) Below is Tabs from Your Sample-file:
    Screen Shot 2018-01-25 at 22.26.53.png
    3) Your 'my main official workbook' has different structure!
    Ex there are NO those sheets where are keys for merge!
    4) ... and now You ask to change the whole code again
    - think ... think ... few times before write something like that!
  24. S. Das

    S. Das Member

    Messages:
    88
  25. vletm

    vletm Excel Ninja

    Messages:
    3,458
    The problem ... there is no problems!
    All matters ... all matters!
    Sheets have to have SAME LAYOUT:
    ex Your Sheet2A's 'data start from 3rd row and original ... from 2nd row!
    Screen Shot 2018-01-26 at 10.27.55.png
    This is like ... if You would like to phone to someone ...
    Could You press ANY NUMBERS or how?
    S. Das and Marc L like this.

Share This Page