Delete rows on basis of duplicate & blank cell

[sanjuss2]

Hi
thanks in advance
I have a database which contains information related to ATM cards
in Sheet
A B C .............................
1 Card no Account no. Branch Code ...........................
2 123 1001
3 123 1001
4 1002
5 124 1002
6 1003
7 125 1004
8 126 1004

I need the record as follows

A B C .............................
1 Card no Account no. Branch Code ...........................
3 123 1001
5 124 1002
8 126 1004

it will remove duplicate rows i.e. 3
it will remove rows if card no. is blank i.e. 6
it will remove the earlier record & keep latest i.e. 8

I need a macro for this because it is continuing job everyday

[Kaper]

3 Steps:
1) remove duplicates on B and C
2) remove empty (autofilter) on C
3a) sort descending on A
3b) remove duplicates on C (always first - as we already did 3a = with highetest A) will remain, others if exist (like row 7) will be removed
3c) sort back ascending on A


If that is not enough consider attaching a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[sanjuss2]

I have attached file of random data

I want if account no. & Card no are duplicate remove row
if card no. is blank remove row
if account is duplicate & card no. is different then remove earlier record.
duplicate blank row delete.xlsx
regards

[Kaper]

Try the following. This is basically registered and a bit fine-tuned (for instance checking if any row is visible after autofilter):

Please Login or Register  to view this content.