PDA

View Full Version : Sum and merge offset cells, if duplicates are found in a certain column (range)



mjgcancio
06-08-2017, 06:27 AM
Hello to all.
I've been struggling with pieces of code I've found over the net, with no success, and I would appreciate your help with this one more. :)
So, the code is:



Option Explicit

Private Sub MergeandSumCells()
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Dim rngMerge1, cell As Range


Set rngMerge1 = Range("B3:B1000") 'defining where the duplicates are


MergeAgain1:
For Each cell In rngMerge1
If cell.Value = cell.Offset(1, 0).Value And IsEmpty(cell) = False Then
'here, i want to merge and sum the offset cell values (in columns C and E), of the duplicates it found in B column
Application.WorksheetFunction.Sum(Range(cell.Offset(0, 1)), Range(cell.Offset(1, 1))) '???
Application.WorksheetFunction.Sum(Range(cell.Offset(0, 3)), Range(cell.Offset(1, 3))) '???
Range(cell.Offset(0, 1), cell.Offset(1, 1)).Merge '???
Range(cell.Offset(0, 3), cell.Offset(1, 3)).Merge '???
GoTo MergeAgain1
End If
Next


Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


What I need is that, whenever there are duplicate cell values in column B, it would sum and then merge the sum value, it would found on the cells for e.g. in columns C and E of the same rows as the duplicates found in column B.

Figure Example:

19430
In red are the duplicate values, and in yellow the values to sum and merge the sum value. By the way can I merge and center horizonttally and vertically in VBA?

This code so far it just blocks Excel (stops responding). :crying:

There must be another way, for sure. A little help? :help

Thank you for your time, best regards

Mário C.

p45cal
06-08-2017, 11:36 AM
I think the functionality you're looking for is to be found in a pivot table.
Supply a file instead of a picture of a file and I/we can show you.

mjgcancio
06-08-2017, 04:30 PM
Hello p45cal and thank you for your reply.

I'm afraid I can't supply a file, because there are sensitive customer data in it that could also jeopardize my job. gulp...sorry

But I can give you more details, so:

there are 544 rows and the last column is AH (and the number of rows and columns can grow up or down)...in the sheet I have several other formulas (mainly IF formulas), another macro I have to run once in a while and also have data entries, so it's a dynamic sheet, if we can say so.

Since my first post I've made an arrangement in the code as follows:


Option Explicit

Private Sub MergeandSumCells()
Application.ScreenUpdating = False
Application.DisplayAlerts = False




Dim rngMerge1, cell As Range
Dim i As Integer
i = Range("B2").End(xlDown).Row
Set rngMerge1 = ActiveSheet.Range("B2:B" & i)


MergeAgain1:
For Each cell In rngMerge1
If cell.Value = cell.Offset(1, 0).Value And IsEmpty(cell) = False Then

'here, i want to sum the offset cell values (in columns C and E), of the duplicates it found in B column

cell.Offset(0, 1).Value = Excel.WorksheetFunction.Sum(cell.Offset(0, 1), cell.Offset(1, 1))
cell.Offset(0, 1).Value = Excel.WorksheetFunction.Sum(cell.Offset(0, 3), cell.Offset(1, 3))

'and another way is:
'cell.Offset(1, 1).Formula = "=SUM(" & Range(cell.Offset(0, 1), cell.Offset(1, 1)).Address(False, False) & ")"
'cell.Offset(1, 3).Formula = "=SUM(" & Range(cell.Offset(0, 3), cell.Offset(1, 3)).Address(False, False) & ")"

'here, i want to merge the offset cell values (in columns C and E), of the duplicates it found in B column


Range(cell.Offset(0, 1), cell.Offset(1, 1)).Merge
Range(cell.Offset(0, 3), cell.Offset(1, 3)).Merge



GoTo MergeAgain1
End If
Next




Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

and partially worked but still the Excel was very, very slow, and the merged cells in the test table at C2:C3 gave me a result of 0, instead of 48 (25+23) and the cells at E2:E3 also gave me 0 right after I stopped the code from executing, because it was taking so long for that test table.:banghead:

Any ideas?

p45cal
06-09-2017, 02:08 AM
I'm afraid I can't supply a file, because there are sensitive customer data in it that could also jeopardize my job. gulp...sorryI wouldn't expect you to expose sensitive data, of course, but you can do a number of things: At worst, make up a file from scratch to demonstrate as closely as possible your problem. Otherwise you can de-sensitize your actual file: remove irrelevant sheets and code, do some search-and-replacing (not wholesale so that there are no sensible data to work with!). It would be even better if that file contained a section on the kind of result you're looking for, made up manually.
There are major advantages to supplying a file: it saves respondents from guessing wrongly on all sorts of aspects of you problem, so any answer you do get is more likely to work in real life
It saves respondents a lot of work, in two senses: (1) they don't have to go about making a file for themselves to experiment with and demonstrate their solution to you and (2) if they answer you without the help of a file they end up spending inordinate amounts of time giving you a wordy answer, which is rarely detailed enough to ensure success at your end (remember, respondents don't know your Excel abilities).
Without a file you risk getting an inaccurate answer, an incomplete answer, or none at all!
Help us to help you.

mjgcancio
06-09-2017, 06:59 AM
Good afternoon.

I'm sorry for the delay on my response and thank you again for your prompt response.

I've arranged a dummy file of the original one, in order to explain a little better my purpose.

So what I do need is that everytime the macro finds duplicate values in column B, it will first sum the values of the cells in the same rows as the duplicates, but in column T and W (or any other one I would need).

The second part is that after the cells in column T and W are summed, the macro will merge only these cells (in column T, W or any other one).

I've researched a little more and found another code that I've adapted for the purpose, but I don't know how to do the second part, which is merging the summed cells in T and W.


Sub mergeCategoryValues() Dim lngRow As Long


With ActiveSheet
Dim columnToMatch As Integer: columnToMatch = 2
Dim columnToSum1 As Integer: columnToSum1 = 3
Dim columnToSum2 As Integer: columnToSum2 = 5


lngRow = .Cells(65536, columnToMatch).End(xlUp).Row
.Cells(columnToMatch).CurrentRegion.Sort key1:=.Cells(columnToMatch), Header:=xlYes


Do
If .Cells(lngRow, columnToMatch) = .Cells(lngRow - 1, columnToMatch) Then

.Cells(lngRow - 1, columnToSum1) = .Cells(lngRow - 1, columnToSum1) + .Cells(lngRow, columnToSum1)
.Cells(lngRow - 1, columnToSum2) = .Cells(lngRow - 1, columnToSum2) + .Cells(lngRow, columnToSum2)

End If


lngRow = lngRow - 1
Loop Until lngRow = 1
End With

'---MISSING THE MERGE PART-----------------------------------------------------------------------------------------------------


End Sub

This is doing the sums in column T and W, in the same first row of each of the first duplicate value in column B. I need to merge the cell with the other rows below so it remains only one cell in column T and W, per duplicate cells in column B. (and with the value of the summed cells, which is the first one)

This macro is working perfectly and quickly for the sums, but still missing the merge part.

Could you please test it and tell me what you think, please.

Thank you.

p45cal
06-09-2017, 09:43 AM
I've had a look at mergeCategoryValues but there's perhaps a problem or 2:
1. When the sort takes place it icludes the headers om row 2 because you have a,b,c,d, etc. on row 1; Is row 1 in your real file?
2. You have columnToSum1 = 3 and columnToSum2 = 5 I take it that the calues should be columnToSum1 = 20 and columnToSum2 = 23?

mjgcancio
06-09-2017, 10:21 AM
I've had a look at mergeCategoryValues but there's perhaps a problem or 2:
1. When the sort takes place it icludes the headers om row 2 because you have a,b,c,d, etc. on row 1; Is row 1 in your real file?
2. You have columnToSum1 = 3 and columnToSum2 = 5 I take it that the calues should be columnToSum1 = 20 and columnToSum2 = 23?

1. Yes I have row 1 in the real file.
2. You're right again...my mistake, I was testing it on a small file.
Even so, I've tested it and I think it works fine ... Isn't it??

p45cal
06-09-2017, 12:08 PM
1. Yes I have row 1 in the real file.This makes your sorting routine in your code think the headers are that row 1 with a,b,c,d,e, etc. in. I've tweaked the code to compensate for that. Why do you need that row?! as it's a duplicate of the Excel's column names anyway?

In the attached, there's a new version of mergeCategoryValues in Module 1. It first copies the MJGCANCIO sheet and works on the copy. I think it does as you want and you can probably see how to tweak it to deal with more/different columns.

Also there's a new sheet21 where I've put an ultra simple pivot table - currently only showing a small subset of the columns, but you can see that the results are the same. This is near instant in terms of updating.

You mention that this sheet is dynamic. Therefore I feel it would be better not to 'adjust' this sheet with merged cells etc. but leave it as a source of data. Adding data to a sheet that has been processed by mergeCategoryValues I would not be confident at all that processing it again would give desired results; sorting and vba coding with merged cells would very likely not work properly.

mjgcancio
06-12-2017, 09:15 AM
Hello p45cal and thank you very, very much for your help.

That's just what I needed. I'll try to understand it better (already found how to tweak different columns, that was easy).
Although the pivot table isn't a solution for me, it was a good gesture, thank you.

With my best regards,

MC
:friends: :jsmile:

mjgcancio
07-31-2017, 02:54 PM
Hello, p45cal.

I'm sorry, I'm not sure if I can reply/continue with an help update using this thread, but since it's based on the same file here goes:

is it possible that instead of the sum of values and consequent merge of summed cells, is there an option or variant that sums the formula of each cell and then reflects it on the merged cells?
for ex. assuming "column a" are the cells that have formulas to sum and "column b" are the cells to merge:



column a
column b


=sum(...)
=(sum(...))+(if(...))+(or(...))


=if(...)


=or(...)



is it possible to do this? join the formulas by summing them on the merged cells, in which not only the value of the sum will appear, but also the formula?

Note: in "column a" there aren't only sum formulas, but also "if" and other formulas. Also, I've put each formula in () because otherwise each individual formula might not work properly when summed to the others.

Thanks in advanced
MC