vba help - fill blank and remove duplicate

[Mallesh23]

Hi Team,

My Input files Data is not coming in a same Row.it is Coming in two Row.

I want to Remove duplicate transaction ID. After adjusting top Row of Column E and F.

Below is my input Data.from Range("A1:F13")

Expected Output is in Range("a18:f24")

Below is my Data and expected Output. Thanks for your help in advance !

Book8
	A	B	C	D	E	F
1	Sr NO	Transaction ID	Amount	User ID	Remittance	User Name
2	1	435325		xxx	421mfg17	abc
3	2	435325	586	xxx
4	1	438751		xxx	63715rt	xyz
5	2	438751	701	xxx
6	1	438765		xxx	2715or1	JKL
7	2	438765	587	xxx
8	1	438777		xxx	4715or1	PQR
9	2	438777	98	xxx
10	1	34908		xxx	311326-2-Charge	JKL
11	2	34908	687	xxx
12	1	34925		xxx	319265-2-3-Refund	XYZ
13	2	34925	738	xxx
14
15
16
17		Expected output
18	Sr NO	Transaction ID	Amount	User ID	Remittance	User Name
19	2	435325	586	xxx	421mfg17	abc
20	2	438751	701	xxx	63715rt	xyz
21	2	438765	587	xxx	2715or1	JKL
22	2	438777	98	xxx	4715or1	PQR
23	2	34908	687	xxx	311326-2-Charge	JKL
24	2	34925	738	xxx	319265-2-3-Refund	XYZ
Sheet1

Thanks
mg

 

[Fluff]

How about

Sub Mallesh()
   Intersect(Range("C:C").SpecialCells(xlBlanks).EntireRow, Range("A:D")).Delete xlUp
   Range("E:F").SpecialCells(xlBlanks).Delete xlUp
End Sub

 

[Mallesh23]

Hi Fluff,

Wow!! its amazing, it worked ! Thanks

Thanks
mg

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Mallesh23]

Hi Fluff,

Your code worked on given example,

But When Run on Actual data , I noticed some extra blanks (UserName and Remittance missing) , data huge noticed lately.

Range("A10:D14") which are highlighted in Red Color, There are no blanks and all transaction ID are Unique.

But there are Blanks in username and Remittance Column.

Found some exception in My Data. how to handle this situation.

Reanalysed data

Input Data ("A1:F22")
Expected OutPUt ("A26:F39")

Below Table.

Book9
	A	B	C	D	E	F
1	Sr NO	Transaction ID	Amount	User ID	Remittance	User Name
2	1	435325		xxx	421mfg17	abc
3	2	435325	586	xxx
4	1	438751		xxx	63715rt	xyz
5	2	438751	701	xxx
6	1	438765		xxx	2715or1	JKL
7	2	438765	587	xxx
8	1	438777		xxx	4715or1	PQR
9	2	438777	98	xxx
10	1	40012	480	xxx
11	1	413822	480	xxx
12	1	412213	5000	xxx
13	1	41904	322	xxx
14	1	41268	555	xxx
15	1	34908		xxx	311326-2-Charge	JKL
16	2	34908	687	xxx
17	1	34925		xxx	319265-2-3-Refund	XYZ
18	2	34925	738	xxx
19	1	7896			askfjs	PQR
20	2	7896	20
21	1	85123			shfak	abc
22	2	85123	19	xxx
23
24
25		Expected output
26	Sr NO	Transaction ID	Amount	User ID	Remittance	User Name
27	2	435325	586	xxx	421mfg17	abc
28	2	438751	701	xxx	63715rt	xyz
29	2	438765	587	xxx	2715or1	JKL
30	2	438777	98	xxx	4715or1	PQR
31	1	40012	480	xxx
32	1	413822	480	xxx
33	1	412213	5000	xxx
34	1	41904	322	xxx
35	1	41268	555	xxx
36	2	34908	687	xxx	311326-2-Charge	JKL
37	2	34925	738	xxx	319265-2-3-Refund	XYZ
38	2	7896	20		askfjs	PQR
39	2	85123	19	xxx	shfak	abc
Sheet1

Thanks
mg

 

[Fluff]

Is this an accurate representation of your data?

 

[Mallesh23]

Hi Fluff,

Yes this is actual representation of data now. checked all input files of one week.


Thanks
mg

 

[Fluff]

Ok, how about

Sub Mallesh()
   With Intersect(Range("C:C").SpecialCells(xlBlanks).EntireRow, Range("A:D"))
      .Offset(1, 4).Delete xlUp
      .Delete xlUp
   End With
End Sub

 

[Mallesh23]

Hi Fluff,

its perfect, it worked, Below is the code which I used in my actual data,
But I want understand how this lovely piece code works, Can you add comment plz.

Sub Mallesh()
   With Intersect(Range("T:U").SpecialCells(xlBlanks).EntireRow, Range("A:V"))
      .Offset(1, 22).Delete xlUp  
      .Delete xlUp
   End With
End Sub '

Thanks.
mg

 

[Fluff]

If you change the .Delete xlUp to .Select & step through the code, you can see what it's doing.