using VBA to summarize duplicated values in a table

[Somapadidar]

I have a file including 3 different columns. The contents of first one "name", are chosen from a list which has been defined in data validation before. It means its contents is limited. The content of second column "order" is numbers, which may be from 1 to 30 but there is no limitations on it. I want to find repeated rows versus columns "name" & "order" and if it occurs, and write the number(order) which has been repeated and the sum of their 3rd columns, "amount", next to it in a table below, in front of its related name. The point is that for a particular name more than one different repeated order may occur which should be put in a row in the table respectively and of course there may be no duplicated value which means no data would be added in table. I want to have just duplicated value and the number of their repetition if it is possible. I mean those with number of repetition is >1. this is a sample and the desired table which has been filed by mine. I want to use VBA in this project.

NAME ORDER AMOUNT AA 4 84 BB 3 5 CC 6 65 DD 6 12 EE 7 45 FF 9 76 AA 4 89 DD 1 9 BB 3 33 EE 3 12 AA 2 34 EE 7 56 AA 2 76 BB 3 12 BB 1 45
and here is desired result which can be written for example from column "j":
order amount count order amount count AA 4 173 2 2 110 2 BB 3 50 CC DD EE 7 101 3 FF GG HH II JJ KK

 

[Marc L]

​

As you forgot to elaborate where is the source data (just a range or a real Excel table ?!) and where should be the expected result …​

 

[Somapadidar]

NAME	ORDER	date	time	zone	AMOUNT
AA	4				84
EE	7				45
AA	4				89
EE	3				12
AA	2				34
EE	7				56
AA	2				76

here is the my source data​

 

[Marc L]

​

Yes but still don't know where ! Read again post #2 but well this time …​

​

Or are you enough confident with your Excel / VBA skills to amend any VBA demonstration a helper could share ?​

 

[Somapadidar]

I want to have the result table in the same sheet (a bit apart from the main data)
and here is my desired result I want to have it in the sheet which wanted to be produced by VBA:

	order	amount	count	order	amount	count
AA	4	173	2	2	110	2
BB	3	50
CC
DD
EE	7	101	3
FF
GG
HH
II
JJ
KK

 

[Marc L]

​

If you are enough confident with your Excel / VBA skills I could post some 'one shot code'​

meaning I won't modify it for what you forgot to elaborate but are you ?​

 

[Somapadidar]

I am not so professional but I try to get it

 

[Somapadidar]

I am not so professional but I try to get it

I do not know what others details I should give you?

 

[Marc L]

An Excel basics VBA one shot demonstration for starters :​

Sub Demo1()
         Dim F$, S$, V, R&, L&, W, C%
         Application.ScreenUpdating = False
         [J1].CurrentRegion.Clear
    With [A1].CurrentRegion.Columns
        .Item("A:B").AdvancedFilter 2, , [J1], True
         F = .Item(1).Address(, , xlR1C1) & ",RC10," & .Item(2).Address(, , xlR1C1) & ",RC[-"
         S = "=SUMIFS(" & .Item(6).Address(, , xlR1C1) & "," & F & "1])"
         F = "=COUNTIFS(" & F & "2])"
    End With
    With [J1].CurrentRegion.Rows
         If .Count = 1 Then Beep: Exit Sub
        .Sort .Cells(1), 1, Header:=1
         V = .Value2
        .Item("2:" & .Count).Clear
    End With
        R = 1
    For L = 2 To UBound(V)
        If V(L, 1) = W Then C = C + 3 Else C = 11: R = R + 1: W = V(L, 1): Cells(R, 10).Value2 = W
        Cells(R, C).Resize(, 3).Formula = Array(V(L, 2), S, F)
    Next
    With [J1].CurrentRegion.Columns
        .Range("B1:D1").Value2 = [{"Order #1","Amount #1","Count #1"}]
         If .Count > 4 Then .Range("B1:D1").AutoFill .Cells(2).Resize(, .Count - 1)
    End With
         Application.ScreenUpdating = True
End Sub