Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
iris22
 | Femeie
23 ani
Bucuresti
cauta Barbat
32 - 63 ani |
|
|
TRaP
Moderator
Inregistrat: acum 7 ani
Postari: 840
|
|
Key words: VBA excel UNIQUE SUMIF INDEX MATCH
We extract 3 columns of data from SAP: A = material code, B = consumption, C = unit of measurement.
The following snippet will extract UNIQUE material codes, make dynamic SUMIF by material code and then dynamic INDEX-MATCH to find out unit by material code.
Formula2 is how Excel VBA handles spill formulas.
Sub Calculate_Totals()
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = ActiveSheet
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.count, "A").End(xlUp).Row
With ws
.Range("E2").Formula2 = "=UNIQUE(A2:A" & lastRow & ")"
.Range("F2").Formula2 = "=-SUMIF(A2:A" & lastRow & ",E2#,B2:B" & lastRow & ")"
.Range("G2").Formula2 = "=INDEX(C2:C" & lastRow & ",MATCH(E2#,A2:A" & lastRow & ",0))"
lastRow2 = .Cells(.Rows.count, "E").End(xlUp).Row
.Range("E2:G" & lastRow2).Select
.Range("E1").Value = "Data already selected, you can copy (CTRL+C)"
End With
Application.ScreenUpdating = True
End Sub |
|
|
| pus acum 11 zile |
|