VBA SUM DATA between 2 sheets
Closed
abdelfatah_0230
Posts
73
Registration date
Thursday July 18, 2019
Status
Member
Last seen
July 23, 2022
-
Updated on Sep 24, 2019 at 12:03 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Sep 19, 2019 at 11:53 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Sep 19, 2019 at 11:53 AM
Related:
- VBA SUM DATA between 2 sheets
- Tentacle locker 2 - Download - Adult games
- Fnia 2 - Download - Adult games
- Euro truck simulator 2 download free full version pc - Download - Simulation
- Plants vs zombies 2 pc download - Download - Strategy
- Red dead redemption 2 free download - Download - Action and adventure
2 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Sep 16, 2019 at 12:19 PM
Sep 16, 2019 at 12:19 PM
Hi Abdel,
Running your code gives me a different result then your sample data. Posted a different code maybe?
Anyway to get the desired result I made some changes marked by the green text (">" meaning changed to).
Have a look:
Let us know how this looks for you now.
Best regards,
Trowa
Running your code gives me a different result then your sample data. Posted a different code maybe?
Anyway to get the desired result I made some changes marked by the green text (">" meaning changed to).
Have a look:
Sub AnyThing() Dim lastrow_1 As Long, counter As Long Dim lastrow_2 As Long, key As Variant Dim sh1 As Worksheet, sh2 As Worksheet Dim rng1, rng2 As Range, p As Variant Dim dict As Object Set sh1 = Sheets("SHEET1") Set sh2 = Sheets("SHEET2") sh2.Range("K4").Resize(1000, 5).ClearContents 'I3 > K4 & 3 > 5 lastrow_1 = sh1.Cells(sh1.Rows.Count, "B").End(3).Row lastrow_2 = sh2.Cells(sh2.Rows.Count, "C").End(3).Row 'sh1 > sh2 Set rng1 = sh1.Range("B4:E" & lastrow_1) '3 > 4 Set rng2 = sh2.Range("C5:E" & lastrow_2) '2 > 5 Set dict = CreateObject("Scripting.Dictionary") For Each p In rng1.Columns(1).Cells '2 > 1 If Not dict.Exists(p.Value & "," & p.Offset(, 1) & "," & p.Offset(, 2)) Then 'Add & "," & p.Offset(, 2) dict.Add p.Value & "," & p.Offset(, 1) & "," & p.Offset(, 2), p.Offset(, 3) 'Adjust according to previous line Else dict(p.Value & "," & p.Offset(, 1) & "," & p.Offset(, 2)) = _ dict(p.Value & "," & p.Offset(, 1) & "," & p.Offset(, 2)) + p.Offset(, 3) 'Adjust according to previous lines End If Next p '=============================== For Each p In rng2.Columns(1).Cells '2 > 1 If Not dict.Exists(p.Value & "," & p.Offset(, 1) & "," & p.Offset(, 2)) Then 'Add & "," & p.Offset(, 2) dict.Add p.Value & "," & p.Offset(, 1) & "," & p.Offset(, 2), p.Offset(, 3) 'Adjust according to previous line Else dict(p.Value & "," & p.Offset(, 1) & "," & p.Offset(, 2)) = _ dict(p.Value & "," & p.Offset(, 1) & "," & p.Offset(, 2)) + p.Offset(, 3) 'Adjust according to previous lines End If Next p '============================== counter = 3 '2 > 3 With sh2 For Each key In dict.Keys counter = counter + 1 .Cells(counter, "K").Resize(1, 3) = Split(key, ",") '2 > 3 .Cells(counter, "N") = dict(key) 'O > N Next key End With dict.RemoveAll: Set dict = Nothing Set sh1 = Nothing: Set sh2 = Nothing Set rng1 = Nothing: Set rng2 = Nothing End Sub
Let us know how this looks for you now.
Best regards,
Trowa
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Sep 17, 2019 at 11:59 AM
Sep 17, 2019 at 11:59 AM
Hi Abdel,
That is strange, as I'm getting the exact result you want.
Have a look at the testbook below to see what happens there:
http://ge.tt/1AMRM9y2
If you can't figure out what is happening, then consider posting your own workbook (always be careful with sensitive data) for us to have a look.
Best regards,
Trowa
That is strange, as I'm getting the exact result you want.
Have a look at the testbook below to see what happens there:
http://ge.tt/1AMRM9y2
If you can't figure out what is happening, then consider posting your own workbook (always be careful with sensitive data) for us to have a look.
Best regards,
Trowa
abdelfatah_0230
Posts
73
Registration date
Thursday July 18, 2019
Status
Member
Last seen
July 23, 2022
Sep 18, 2019 at 06:59 AM
Sep 18, 2019 at 06:59 AM
you're right this is very strange do me fever where is exactly my problem this is my workbook
https://files.fm/u/nkemd3q8
https://files.fm/u/nkemd3q8
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Sep 19, 2019 at 11:53 AM
Sep 19, 2019 at 11:53 AM
Hi Abdel,
You have stored most of your numbers as text. You will need to change them back to numbers.
Let me provide 2 ways to change them back:
1.
It will depend on your settings (formula settings under Excel options), but I see a small green triangle in the top left of those text cells that contain numbers. When you select a cell like that you will notice a yellow "!". Clicking on that will allow you to convert the cell to numbers. When you do this for all your numbers, make sure your selection starts with a green triangle marked cells AND that your range doesn't contain merged cells.
2.
When you don't see the green triangle and don't want to mess with your setting, you can also use the Text Delimiter option. Select your numbers, again no merged cells, so you can't select the entire column. Easiest way to do this is by selecting the top value, hit the "End" key, hold the "Shift" key and then hit the "Arrow down" key. Once you have your cells selected, go to Data ribbon and click on "Text Delimiter", then click on "Complete" (or "Finish" not sure on the translation) and you are done.
Do you get the correct result now?
Best regards,
Trowa
You have stored most of your numbers as text. You will need to change them back to numbers.
Let me provide 2 ways to change them back:
1.
It will depend on your settings (formula settings under Excel options), but I see a small green triangle in the top left of those text cells that contain numbers. When you select a cell like that you will notice a yellow "!". Clicking on that will allow you to convert the cell to numbers. When you do this for all your numbers, make sure your selection starts with a green triangle marked cells AND that your range doesn't contain merged cells.
2.
When you don't see the green triangle and don't want to mess with your setting, you can also use the Text Delimiter option. Select your numbers, again no merged cells, so you can't select the entire column. Easiest way to do this is by selecting the top value, hit the "End" key, hold the "Shift" key and then hit the "Arrow down" key. Once you have your cells selected, go to Data ribbon and click on "Text Delimiter", then click on "Complete" (or "Finish" not sure on the translation) and you are done.
Do you get the correct result now?
Best regards,
Trowa
Updated on Sep 16, 2019 at 12:54 PM
you can see my images