Remove Data which is not match with other column
 

Hi,

I am downloading data from 2 different system in to xls and need to remove invoice no and amount which is not match with other data.

A and B data from one system and D and E data from another system, currently i am doing foll. manually step to remove data which is very time consuming process for me as data is almost 15000 to 18000.

sort A , B and D,E data in assending order, in C column applying formula A3-D3 and B3-E3 and copying to till end and whenevere there is diffence removing each Invoice no and amount from both data .

inv no amount inv no amt
A B D E
4053 15,000.00 4153 15,000.00
5949 99,200.00 5950 99,200.00
6081 20,928.00 6081 20,900.00
10525 5,165.00 10519 5,165.00
11531 19,600.00 11534 19,600.00
11535 2,000.00 11635 2,000.00
13143 20,000.00 13145 20,000.00
13930 16,000.00 13835 16,000.00
13984 327,762.00 13984 327,000.00


Pls help if there is any other formula / macro to remove A and B column data which is not match with D and E .

there are foll. possibilities to be consider.

A and D data would match but B and E may be mismatch
B and E data would match but A and D may be mismatch
data may be not in either A and B or D and E also.

Remove Data which is not match with other column
 

On Sunday, October 7, 2012 8:05:16 AM UTC-5, Dingdang wrote:
Hi,



I am downloading data from 2 different system in to xls and need to

remove invoice no and amount which is not match with other data.



A and B data from one system and D and E data from another system,

currently i am doing foll. manually step to remove data which is very

time consuming process for me as data is almost 15000 to 18000.



sort A , B and D,E data in assending order, in C column applying formula

A3-D3 and B3-E3 and copying to till end and whenevere there is diffence

removing each Invoice no and amount from both data .



inv no amount inv no amt

A B D E

4053 15,000.00 4153 15,000.00

5949 99,200.00 5950 99,200.00

6081 20,928.00 6081 20,900.00

10525 5,165.00 10519 5,165.00

11531 19,600.00 11534 19,600.00

11535 2,000.00 11635 2,000.00

13143 20,000.00 13145 20,000.00

13930 16,000.00 13835 16,000.00

13984 327,762.00 13984 327,000.00





Pls help if there is any other formula / macro to remove A and B column

data which is not match with D and E .



there are foll. possibilities to be consider.



A and D data would match but B and E may be mismatch

B and E data would match but A and D may be mismatch

data may be not in either A and B or D and E also.









--

Dingdang

This can be done with a macro since formulas can only return values. However, in your sample NOTHING is left ...???

Hi There,

What about trying Advanced Filter. You don't need VBA for this situation.
All you need is to set up a criteria range that constitute some logical functions that either return True or False. You can customize the criteria as per your need (make sure you don't use a column name in your original data). Once you apply the Advanced Filter with this criteria range it filters your data (or copies the filtered data to another sheet). This way you don't have to worry about multiple filtering/sorting of the data.

Let me know if you aren't sure of how Advanced Filters work with formulas. Attached is a sample from your own data.

Let me know your thoughts.

Thank You,
Prashant