clear duplicated items in specific columns and arrange below for each item based on column

[Mussa]

hi
I search for macro to clear repeated items in COL A and arrange again below for each item .



sheet data

ttt.xlsm
	A	B	C	D	E	F
1	DEL NO	BATCH NO	TTL	TT1	IMPORT	EXPORT
2	CCD-1	CC-1	SS-1	TRU	12	10
3	CCD-1	CC-1	SS-1	LTR	12	5
4	CCD-1	CC-2	SS-2	FG	5	5
5	CCS-2	CC-1	SS-1	TRR	10	5
6	CCS-2	CC-1	SS-1	LTR	20	10
7	CSD-1	CS-1	LL-1	RRL	15	5
8	CSD-1	CS-2	LL-2	TTY	10	10
9	CCD-1	CS-3	LL-3	MMW	20	10
10	CCL	CS-4	LL-4	NNW	10	10
11	CCL	CC-2	SS-2	LTR	21	5
12	CCM	CC-3	SS-3	LTR	22	5
13	CSD-1	CS-1	LL-1	RRL	15	5
DATA

expected result
sheet result

ttt.xlsm
	A	B	C	D	E	F
1	DEL NO	BATCH NO	TTL	TT1	IMPORT	EXPORT
2	CCD-1	CC-1	SS-1	TRU	12	10
3		CC-1	SS-1	LTR	12	5
4		CC-2	SS-2	FG	5	5
5		CS-3	LL-3	MMW	20	10
6	CCS-2	CC-1	SS-1	TRR	10	5
7		CC-1	SS-1	LTR	20	10
8	CSD-1	CS-1	LL-1	RRL	15	5
9		CS-2	LL-2	TTY	10	10
10		CS-1	LL-1	RRL	15	5
11	CCL	CS-4	LL-4	NNW	10	10
12		CC-2	SS-2	LTR	21	5
13	CCM	CC-3	SS-3	LTR	22	5
RESULT

 

[Peter_SSs]

My mistake. The way I wrote the 'Evaluate' line did allow for different sheets being active when the code was rune. try this version.

Sub Mussa_v2a()
  With Sheets("Sheet2")
    'Clear any existing data
    .UsedRange.Clear
    'Copy the table from Sh1 to Sh2
    Sheets("Sheet1").Range("A1").CurrentRegion.Copy .Range("A1")
    With .Range("A1").CurrentRegion
      'Sort based on column A to get all the groups together
      .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlYes
      With .Columns(1).Offset(1).Resize(.Rows.Count - 1)
        'Look at the column A cells from row 2 down and replace with True if they are the same as the cell above
        .Value = Evaluate(Replace(Replace("if(#=%,True,#)", "#", .Address(External:=True)), "%", .Offset(-1).Address(External:=True)))
        'Clear contents from all cells that contain True
        On Error Resume Next
        .SpecialCells(xlConstants, xlLogical).ClearContents
        On Error GoTo 0
      End With
    End With
  End With
End Sub

 

[Mussa]

@veyselemre thanks very much for your code

 

[Mussa]

@Peter_SSs great!
thanks very much for your cooperation

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.