Find repeated CELLS in VBA and create another cell

[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, 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. 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
1	AA	4				84
2	BB	3				5
3	CC	6				65
4	DD	6				12
5	EE	7				45
6	FF	9				76
7	AA	4				89
8	DD	1				9
9	BB	3				33
10	EE	3				12
11	AA	2				34
12	EE	7				56
13	AA	2				76
14	BB	3				12
15	BB	1				45
											order	amount	order	amount
										AA	4	173	2	110
										BB	3	50
										CC
										DD
										EE	7	101
										FF
										GG
										HH
										II
										JJ
										KK

 

[maabadi]

Welcome to MrExcel Message Board.
Try:

Sub Test()
Dim Lr As Long, Lr2 As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
  Range("A1:B" & Lr).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("J1:K1"), Unique:=True
Lr2 = Range("J" & Rows.Count).End(xlUp).Row
Range("J1:K" & Lr2).Sort Key1:=Range("J1"), Order1:=xlAscending, Key2:=Range("K1"), Order2:=xlAscending, Header:=xlYes
Range("L1").Value = "Amount"
Range("L2:L" & Lr2).Formula = "=SUMIFS($F$2:$F$" & Lr & ",$A$2:$A$" & Lr & ",J2,$B$2:$B$" & Lr & ",K2)"

End Sub

 

[Somapadidar]

Sub Test() Dim Lr As Long, Lr2 As Long Lr = Range("A" & Rows.Count).End(xlUp).Row Range("A1:B" & Lr).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("J1:K1"), Unique:=True Lr2 = Range("J" & Rows.Count).End(xlUp).Row Range("J1:K" & Lr2).Sort Key1:=Range("J1"), Order1:=xlAscending, Key2:=Range("K1"), Order2:=xlAscending, Header:=xlYes Range("L1").Value = "Amount" Range("L2:L" & Lr2).Formula = "=SUMIFS($F$2:$F$" & Lr & ",$A$2:$A$" & Lr & ",J2,$B$2:$B$" & Lr & ",K2)" End Sub

Thanks a lot
it worked
the only point is that how i can do this just for order>1?

 

[Somapadidar]

Thanks a lot
it worked
the only point is that how i can do this just for order>1?

i mean that orders that have happened more than once not the number of order!

 

[maabadi]

Try this:

Sub Test()
Dim Lr As Long, Lr2 As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
  Range("A1:B" & Lr).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("J1:K1"), Unique:=True
Lr2 = Range("J" & Rows.Count).End(xlUp).Row
Range("J1:K" & Lr2).Sort key1:=Range("J1"), order1:=xlAscending, Key2:=Range("K1"), Order2:=xlAscending, Header:=xlYes
Range("L1:M1").Value = Array("Amount", "Count")
Range("L2:L" & Lr2).Formula = "=SUMIFS($F$2:$F$" & Lr & ",$A$2:$A$" & Lr & ",J2,$B$2:$B$" & Lr & ",K2)"
Range("M2:M" & Lr2).Formula = "=CountIFS($A$2:$A$" & Lr & ",J2,$B$2:$B$" & Lr & ",K2)"
Range("J1:M" & Lr2).AutoFilter Field:=4, Criteria1:="=1", Operator:=xlAnd
Range("J2:M" & Lr2).SpecialCells(xlCellTypeVisible).ClearContents
Range("J1:M" & Lr2).AutoFilter
Range("J1:M" & Lr2).Sort key1:=Range("J1:J" & Lr2), order1:=xlAscending, Header:=xlYes
End Sub

 

[Somapadidar]

									NAME	ORDER	Amount	Count
NAME	ORDER				AMOUNT				AA	2	110	2
AA	4				84				AA	4	173	2​
BB	3				5				BB	3	50	3​
CC	6				65				EE	7	101	2​
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
											amount	order	amount

 

[Somapadidar]

I appreciate you a lot. Here is the result. It makes a table for me at the top of the sheet(thanks)
, but the point is that it runs correctly just once and I cannot find why it did happen?

 

[maabadi]

but the point is that it runs correctly just once and I cannot find why it did happen?

Just Once?
1. What occurs 2nd time?
2. What you want 2nd time?

 

[Sanjeev1976]

Same result using Pivot Table

Book3
	A	B	C	D	E	F	G	H
1	NAME	ORDER	AMOUNT				Values
2	AA	4	84		NAME	ORDER	Amount	Count
3	BB	3	5		AA	2	110	2
4	CC	6	65			4	173	2
5	DD	6	12		BB	3	50	3
6	EE	7	45		EE	7	101	2
7	FF	9	76
8	AA	4	89
9	DD	1	9
10	BB	3	33
11	EE	3	12
12	AA	2	34
13	EE	7	56
14	AA	2	76
15	BB	3	12
16	BB	1	45
Sheet1

 

[Somapadidar]

It just filters column j to m, it means it just shows 4 sliding flash in the first row of mentioned columns(J, K, L ,M)!