Previous Topic Next Topic

Summing values based on matching column across sheets

Solved
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 replies

Answer 1 / 2
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 510
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
1
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
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 510 > 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
1
leapout
Posts
20
Registration date
Monday March 1, 2021
Status
Member
Last seen
April 26, 2022
 1 > TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 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
0
Answer 2 / 2
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 510
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
1
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
1