Good morning. I need a macro that in sheet2 adds the result of sheet 1. For example, in sheet 1 the number of Jhon's student appears, three times appear that I need to add and the number of student and the sum appear in sheet 2 of times in a single result. In the BD tab there is the student number and the name, I need you to compare the student number and put the name. I put on sheet 2 an example of how it should look. Thank you
I offer you an alternative solution to VBA. Power Query
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
alansidman Thanks for the information, I had considered using Power Query, but unfortunately I use it on a company computer that does not allow any plugin to be installed.
Option Explicit Sub test() Dim lr&, i&, k&, rng, t As String, s, arr(), key Dim dic As Object Set dic = CreateObject("Scripting.dictionary") With Worksheets("sheet1") lr = .Cells(Rows.Count, "B").End(xlUp).Row rng = .Range("B2:G" & lr).Value ReDim arr(1 To lr - 1, 1 To 7) For i = 1 To lr - 1 t = rng(i, 2) & "|" & rng(i, 3) & "|" & rng(i, 4) & "|" & rng(i, 5) & "|" & rng(i, 6) If Not dic.exists(rng(i, 1)) Then dic.Add rng(i, 1), t Else s = Split(dic(rng(i, 1)), "|") t = (s(0) + rng(i, 2)) & "|" & (s(1) + rng(i, 3)) & "|" & (s(2) + rng(i, 4)) & "|" & _ (s(3) + rng(i, 5)) & "|" & (s(4) + rng(i, 6)) dic(rng(i, 1)) = t End If Next End With k = 0 For Each key In dic.keys k = k + 1 arr(k, 2) = key arr(k, 1) = WorksheetFunction.VLookup(key, Worksheets("BD").Range("A2:B1000"), 2, 0) For i = 0 To 4 arr(k, i + 3) = Split(dic(key), "|")(i) Next Next With Worksheets("sheet2") .Range("A2").Value = "Name" .Range("B2:G2").Value = Worksheets("sheet1").Range("B1:G1").Value .Range("A3").Resize(dic.Count, 7).Value = arr End With End Sub
Thanks bebo021999, it works fine. I have a doubt, if I want to extend to cells I and J, for example, what part of the code should I modify?
And if at any time I don't want to put the name, what part should I delete.
Thank you very much for your help
With:
lr = .Cells(Rows.Count, "B").End(xlUp).Row
it runs to the last row of column B, that contains student numbers
to not put the name, just remove these lines:
Bookmarks