Page 1 of 1

[Solved] Merge macro or formula for same contents

PostPosted: Tue Aug 22, 2017 12:26 pm
by chandrankrishnan
Hai all,Good day.

can anyone help me for the attached sheet with formula or macro program for two cells have the same contents means then result has been to be in merged format.

Attached file.

thanks in advance.

Re: Merge macro or formula for same contents

PostPosted: Thu Aug 24, 2017 9:20 am
by hubert lambert
Hello,

Here's an example:
Code: Select all   Expand viewCollapse view
sub addAndMerge()
    doc = thiscomponent
    sheet = doc.CurrentController.ActiveSheet
    sum = 0
    row = 2   
    first = row
    do
        range = sheet.getCellRangeByPosition(3, row, 7, row+1)
        data = range.DataArray
        sum = sum + data(0)(3)
       
        if data(0)(0) <> data(1)(0) then
            sheet.getCellByPosition(7, first).setValue(sum)
            tobemerged = sheet.getCellRangeByPosition(7, first, 7, row)
            tobemerged.merge(True)
            sum = 0
            first = row + 1
        end if
        row = row +1
    loop until data(1)(0) = ""
end sub

Regards.

Re: Merge macro or formula for same contents

PostPosted: Thu Aug 24, 2017 10:37 am
by chandrankrishnan
Hi hubert lambert ,
Thanks for the program its working fine.you saved my time and i trying this for past one week.

(can you please teach us to make simple macro program like this)

Thanks a ton. Have a Good day.

Re: Merge macro or formula for same contents

PostPosted: Thu Aug 24, 2017 11:56 am
by chandrankrishnan
Hi
Can you please me to fine tune the program.i.e after sum result the serial number has to be generate as as same as total qty(i.e is in merged condition like 1,2,3 etc)

and then column "K" and "L" to be deleted.

Thanks in advance.

Re: Merge macro or formula for same contents

PostPosted: Thu Aug 24, 2017 12:44 pm
by hubert lambert
At your service... ;)

Re: Merge macro or formula for same contents

PostPosted: Sat Aug 26, 2017 7:41 am
by chandrankrishnan
Hi
Good day to you.Program work fine,but one thing

after total qty sum(in merged form) i need continuous serial number to be generate(as mentioned in column I(marked in red color) like 1,2,3...

Thanks in advance.

Re: Merge macro or formula for same contents

PostPosted: Wed Aug 30, 2017 12:31 pm
by chandrankrishnan
Hi,
i tried once again with macro program,where its working for continuous serial for 2cell merge,whereas for 3cell merge, continuous serial number is collapsing that is its missing some numbers.

Can any one help me in this regards.

I had attached my entire work sheet with macro program.

Thanks in advance.

Re: Merge macro or formula for same contents

PostPosted: Wed Aug 30, 2017 2:23 pm
by hubert lambert
Here it is:
chandrankrishnan.ods
(15.07 KiB) Downloaded 57 times

Re: Merge macro or formula for same contents

PostPosted: Thu Aug 31, 2017 8:39 am
by chandrankrishnan
Hi hubert lambert ,
Thanks a ton.Its working fine and perfect.
Thankyou.