• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help to calculate values based on sorting with VBA

sanket katkar

New Member
Hi all,

Need help to write a macro.
I have 2 different sheets in a workbook.
Sheet 1 has data, from which it will create sheet 2 on click on button.

So logic is, I have values in column D i.e. team name. Based on this team name, it will search for the employee name and copy the data in another sheet let's say Sheet2 were the data will be in one single row.....

I have attached the Excel sheet for reference.

Appreciate your help and support.

Regards
SK
 

Attachments

  • Macro_Calc.xlsm
    13.5 KB · Views: 5
Try Please This Macro

Code:
Sub Give_data()
Dim SH1 As Worksheet: Set SH1 = Sheets("Sheet1")
Dim SH2 As Worksheet: Set SH2 = Sheets("Sheet2")
SH2.Range("A1").CurrentRegion.Offset(1).ClearContents
Dim source_range As Range
Dim my_rg As Range
Dim Em_name$, mY_st1$, cont%, s#, m%: m = 2
Dim i%, K%, y(), Num%: Num = 1
Set source_range = SH1.Range("L14", Range("L13").End(4))
Set my_rg = SH1.Range("b2", Range("G1").End(4))
For i = 1 To source_range.Rows.Count
    For K = 1 To my_rg.Rows.Count
      If source_range.Cells(i) = my_rg.Cells(K, 3) Then
        cont = cont + 1
         s = s + IIf(IsNumeric(my_rg.Cells(K, 6)), my_rg.Cells(K, 6), 0)
        ReDim Preserve y(1 To Num + 1)
        y(Num) = my_rg.Cells(K, 2)
        Num = Num + 1
      End If
    Next K
If cont = 0 Then GoTo Next_i
     y(1) = source_range.Cells(i) & "-" & y(1)
     mY_st1 = Join(y, ",")
        With SH2.Cells(m, 1)
            .Value = m - 1
            .Offset(, 1) = _
             Mid(mY_st1, 1, Len(mY_st1) - 1): mY_st1 = ""
            .Offset(, 2) = cont: cont = 0
            .Offset(, 3) = s: s = 0
        End With
     Num = 1: m = m + 1: Erase y
Next_i:
 Next i

End Sub
 

Attachments

  • Macro_Calc_salim.xlsm
    28.7 KB · Views: 8
Thanks allot.....
You really saved my time.

Just one more thing I want to do add is, a button... which to this sheet1. On clicking that...it will copy the data from another workbook and paste in this master.

And when I click calculate, it will do the rest. //This I have now


Thanks in advance.
 
According to your attachment a Windows demonstration to paste to the (Sheet2) worksheet module :​
Code:
Sub Demo1()
     Dim V, R&, W
         Me.UsedRange.Offset(1).Clear
    With Sheet1.[A1].CurrentRegion
         V = Application.Index(.Cells, Evaluate("ROW(2:" & .Rows.Count & ")"), [{4,3,7}])
    End With
    With CreateObject("Scripting.Dictionary")
        For R = 1 To UBound(V)
            If .Exists(V(R, 1)) Then
                W = .Item(V(R, 1))
                W(1) = W(1) & ", " & V(R, 2)
                W(2) = W(2) + 1
                W(3) = W(3) + V(R, 3)
               .Item(V(R, 1)) = W
            Else
               .Add V(R, 1), Array(.Count + 1, V(R, 1) & " : " & V(R, 2), 1, V(R, 3))
            End If
        Next
            R = .Count:  If R = 0 Then Exit Sub
            [A2].Resize(R, 4).Value2 = Application.Index(.Items, 0)
           .RemoveAll
    End With
        [B2].Resize(R, 3).Sort [B2], xlAscending, Header:=xlNo
        [A2].Resize(R, 4).Borders.Weight = xlThin
End Sub
Do you like it ? So thanks to click on bottom right Like !
 
Need help,
There is additional request with respect to the above.
When I get the sorted query display in sheet2. I am calculating column G as Column (F*E).
But when I click on the button, it should give me the value in Column G but should display what formula I have used to get this value.

For example:
If I have 2entries of team AAA, then in output I get :
AAA/Member name1, Member name2
Count as 2
Total as 350
So here 350 comes from sheet 1 column G which has 2 different entries 200 and 150.
Till here everything is working correctly.

But additional I need is, when I click on 350 it should show me in formula section how does this 350 comes, so with respect to the above example, it should display
(100*2)+(75*2)

here 100 comes from column E and 2 comes from column F
 
Back
Top