["vba"] Sum 3 days of work per person

[montecarlo2012]

Hi,
I am trying to figure out how to coding a total sales by person, I just upload 3 days info.
what I have now and where :

as you see for example Derek total per day, I would like the same for the rest.
I can think about the pseudocode (maybe) like this

start

On I3:I10 if B2:B33 names match H3:H10 then
Sum(offset , c,d,e,)

On J3:J10 if B2:B33 names match H3:H10 then
Sum(offset , c,d,e,)

On k3:k10 if B2:B33 names match H3:H10 then
Sum(offset , c,d,e,)

End

what I was reading in different website is more or less how to use sum and offset, but honestly I didn't figure out, so here I am. Please give a hand here.
Thanks

 

[ebea]

Here an example, with Formulas!

Mappe1
	A	B	C	D	E	F	G	H	I
1	workers	day1	day2	day3		workers	day1	day2	day3
2	derek	5	2	7		derek	7	10	12
3	derek	2	8	5		jane	7	2	10
4	jane	7	2	10		john	25	37	60
5	john	6	7	15		kim	10	40	9
6	john	9	5	19		peter	2	8	6
7	john	4	10	21		terri	12	8	13
8	john	6	15	5		tim	25	13	8
9	kim	2	19	2		tom	40	8	10
10	kim	8	21	7
11	peter	2	8	6
12	terri	7	2	9
13	terri	5	6	4
14	tim	10	9	6
15	tim	15	4	2
16	tom	19	6	8
17	tom	21	2	2
Ark1

Cell Formulas
Range	Formula
G2:I9	G2	=SUMPRODUCT(($A$2:$A$17=$F2)*(($B$1:$D$1=G$1))*B$2:B$17)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Or using a non array formula

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1	workers	day1	day2	day3		workers	day1	day2	day3
2	derek	5	2	7		derek	7	10	12
3	derek	2	8	5		jane	7	2	10
4	jane	7	2	10		john	25	37	60
5	john	6	7	15		kim	10	40	9
6	john	9	5	19		peter	2	8	6
7	john	4	10	21		terri	12	8	13
8	john	6	15	5		tim	25	13	8
9	kim	2	19	2		tom	40	8	10
10	kim	8	21	7
11	peter	2	8	6
12	terri	7	2	9
13	terri	5	6	4
14	tim	10	9	6
15	tim	15	4	2
16	tom	19	6	8
17	tom	21	2	2
18
Master

Cell Formulas
Range	Formula
G2:I9	G2	=SUMIFS(B:B,$A:$A,$F2)

 

[Fluff]

Or with VBA

Sub montecarlo()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long, rr As Long, c As Long
   
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
   With CreateObject("Scripting.dictionary")
      For r = 2 To UBound(Ary)
         If Not .Exists(Ary(r, 1)) Then
            nr = nr + 1
            .Add Ary(r, 1), nr
            Nary(nr, 1) = Ary(r, 1)
         End If
         rr = .Item(Ary(r, 1))
         For c = 2 To UBound(Ary, 2)
            Nary(rr, c) = Nary(rr, c) + Ary(r, c)
         Next c
      Next r
   End With
   Sheets("Sheet1").Cells(2, c + 1).Resize(nr, UBound(Ary, 2)).Value = Nary
End Sub

 

[montecarlo2012]

Ebea, thank you so much, specially you took the time to re-write my data and upload on xl2BB, awesome, sorry but I tried to download the application 2BB but I got a lot problem after.
anyway, thanks again.

 

[montecarlo2012]

Fluff, thank you so much. Is a really pretty code, I understand you work with the info provided by ebea
thats why the answer is on F and not on H like the first images,
so I move my data to column A and delete the answers I have before,
and I am working now on figuring out how can I change locations to any where.

I found only one place they talk about scripting dictionary,
but not to the level you show here, is a really complex code.

what I can see is there are two for next loops, one for rows [for r = ] and one for columns [for c =]
Ary is for the code to read the sheet, Redim is reading the array,

.Add Ary(r, 1), nr , Nary(nr, 1) = Ary(r, 1) here I get lost

I assume if this line is out -- > With CreateObject("Scripting.dictionary")
and then the code will be longer (maybe?)
I don't know, is really beautiful and awesome code Mr. Fluff

Thank you again for your GREAT work.

 

[ebea]

Your welcome Regarding Xl2BB it should be straight forward. The only problem I have experienced, is that I have to reactivate the Plug In, under "File", and "settings".

 

[montecarlo2012]

ebea, You did awesome job, and really appreciate your time, thank you
in my case I download the app but my computer start some kind of weird behave like virus or malware
then I delete the app, and everything come back to normal.
I hope to see you arround

 

[Fluff]

Glad to help & thanks for the feedback.

 

[ebea]

ebea, You did awesome job, and really appreciate your time, thank you
in my case I download the app but my computer start some kind of weird behave like virus or malware
then I delete the app, and everything come back to normal.
I hope to see you arround

Your welcome! Check your Computer, for malware, and then retry with the XL2BB