• 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.

'Merge & Center' of cells based on value repetition

S. Das

Active Member
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
 

Attachments

  • sample.xlsx
    14.1 KB · Views: 10
Last edited by a moderator:
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.
 
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'?
 
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.
Is there any possible solution without merging??
If possible then the solution is most welcome.
 
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.
 
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'?
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
 
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.
As it's a printable page format (predefined) I have to make like that. Whatever I have said I want this automatically, if possible.
 
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.
 
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.
 
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.
 

Attachments

  • sample.xlsx
    58.5 KB · Views: 6
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?
 
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.
 
S. Das
... it seems impossible to get answers ...
There are two buttons ... use those!
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.
 

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

Easier on a unique table in destination sheets …​
 
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?
 

Attachments

  • sample (1).xlsb
    67 KB · Views: 3
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??
 
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
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)
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?
 
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!
 
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?
 
Back
Top