# Summing values based on matching column across sheets

Solved/Closed
leapout Posts 20 Registration date Monday March 1, 2021 Status Member Last seen April 26, 2022 - Apr 12, 2022 at 12:08 PM
leapout Posts 20 Registration date Monday March 1, 2021 Status Member Last seen April 26, 2022 - Apr 26, 2022 at 04:36 PM
Hello,

Hello,

I would match data across sheets based on matching columns B then should add & summing the values to sheet summary under headers BUYING and SELLING . should add the values to last columns (BUYING ,SELLING) ,because evrey month insert three columns BUYING,SELLING,NET. the most important BUYING,SELLING should match data across sheets based on columns B and put the values under headers (BUYING ,SELLING).

as in picture 1 , the values which showing in column H brings from the other file . it's openning balances , so it 's not relating of the others sheets .

sheet summary

sheet 1

sheet2

the result in sheet summary

## 2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 19, 2022 at 11:32 AM
Hi Leapout,

Could you check if the code below yields the desired result:
```Sub RunMe()
Dim mWS1, mWS2, mWS3 As Worksheet
Dim mRow, x, lRow As Long
Dim mFind As Range

Set mWS1 = Sheets("Sheet1")
Set mWS2 = Sheets("Sheet2")
Set mWS3 = Sheets("Summary")
x = 2
mWS1.Select
lRow = Range("B1").End(xlDown).Row

nSheet:
For mRow = x To lRow
Set mFind = mWS3.Columns("B").Find(Cells(mRow, "B").Value)
mWS3.Cells(mFind.Row, Columns.Count).End(xlToLeft).Offset(0, -2).Value = Cells(mRow, "F").Value
mWS3.Cells(mFind.Row, Columns.Count).End(xlToLeft).Offset(0, -1).Value = Cells(mRow, "G").Value
If mRow = lRow And ActiveSheet.Name = mWS1.Name Then
mWS2.Select
lRow = Range("B1").End(xlDown).Row
GoTo nSheet
End If
Next mRow
End Sub```

Best regards,
Trowa
leapout Posts 20 Registration date Monday March 1, 2021 Status Member Last seen April 26, 2022 1
Updated on Apr 19, 2022 at 05:02 PM
Hi Trowa,
thanks for this code , but I have one thing ,
sometimes there are duplicates items for sheet1,2 then should sum the values when show in sheet summary
I'm talking about column BUYING between sheet1,2

best regards,
Leapout
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552 > leapout Posts 20 Registration date Monday March 1, 2021 Status Member Last seen April 26, 2022
Apr 25, 2022 at 11:58 AM
Hi Leapout,

Code line 16 determines the 'Buying' column value. To add to this value instead of replacing, then change that line:
`mWS3.Cells(mFind.Row, Columns.Count).End(xlToLeft).Offset(0, -2).Value = Cells(mRow, "F").Value`

into:
`mWS3.Cells(mFind.Row, Columns.Count).End(xlToLeft).Offset(0, -2).Value = mWS3.Cells(mFind.Row, Columns.Count).End(xlToLeft).Offset(0, -2).Value + Cells(mRow, "F").Value`

If you want to do the same with the 'Selling' column, then change code line 17:
`mWS3.Cells(mFind.Row, Columns.Count).End(xlToLeft).Offset(0, -1).Value = Cells(mRow, "G").Value`

into:
`mWS3.Cells(mFind.Row, Columns.Count).End(xlToLeft).Offset(0, -1).Value = mWS3.Cells(mFind.Row, Columns.Count).End(xlToLeft).Offset(0, -1).Value + Cells(mRow, "G").Value`

Best regards,
Trowa
leapout Posts 20 Registration date Monday March 1, 2021 Status Member Last seen April 26, 2022 1 > TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022
Apr 26, 2022 at 06:06 AM
Hi TrowaD ,
Great ! just last thing , I would replace data when run the macro repeatedly for the same columns BUYING & SELLING because I notice if I run the macro more than one time accidentally for the same columns BUYING & SELLING it continues to sum the values over previous value repeatedly . I don't want it . this makes getting the values are wrong based on the others sheets .
thanks
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 26, 2022 at 12:11 PM
Hi Leapout,

Sure thing. The last BUYING and SELLING columns will be cleared, before the new values will be placed.

Here is the code (also with both adjustments from post 3):
```Sub RunMe()
Dim mWS1, mWS2, mWS3 As Worksheet
Dim mRow, x, lRow, lCol As Long
Dim mFind As Range

Set mWS1 = Sheets("Sheet1")
Set mWS2 = Sheets("Sheet2")
Set mWS3 = Sheets("Summary")
x = 2
lCol = mWS3.Cells(1, Columns.Count).End(xlToLeft).Column
Range(mWS3.Cells(2, lCol - 2), mWS3.Cells(Rows.Count, lCol - 1)).ClearContents
mWS1.Select
lRow = Range("B1").End(xlDown).Row

nSheet:
For mRow = x To lRow
Set mFind = mWS3.Columns("B").Find(Cells(mRow, "B").Value)
mWS3.Cells(mFind.Row, lCol).Offset(0, -2).Value = mWS3.Cells(mFind.Row, lCol).Offset(0, -2).Value + Cells(mRow, "F").Value
mWS3.Cells(mFind.Row, lCol).Offset(0, -1).Value = mWS3.Cells(mFind.Row, lCol).Offset(0, -1).Value + Cells(mRow, "G").Value
If mRow = lRow And ActiveSheet.Name = mWS1.Name Then
mWS2.Select
lRow = Range("B1").End(xlDown).Row
GoTo nSheet
End If
Next mRow
mWS3.Select
End Sub```

Best regards,
Trowa
leapout Posts 20 Registration date Monday March 1, 2021 Status Member Last seen April 26, 2022 1
Updated on Apr 26, 2022 at 04:37 PM
Hi TrowaD,
excellent ! thanks for your time & solution .
Best regards,
Leapout