Remove Data which is not match with other column

[dingdang]

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. [COLOR=#3366CC !important]manually[/COLOR] 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.

[tommyt61]

I would suggest that you post a file with some sample data. that way everyone can see or understand what exactly you need to accomplish. enter some sample data as it would appear after import , then show the result you need after the formula or VBA code is ran against that data.

[dingdang]

Hi tommyt61,

Thanks for the reply.

Attached sample data as required.

column A and B is pair Data which match with D and E , if mismatch any of the column with A,B and D,E should remove a side and which match both the invoice no and Amount should remain .

pls provide if any formula or vba code .

[tommyt61]

See if this is will work.

[dingdang]

Sir,

Thanks for the reply. pls note both data will not in assending order ,this formula will fulfill my below 2 possibilities but not last one. if pair of data ( inv no and Amt ) not in second pair of data should also consider as mismatch. I am not sure but I think vlookup or index match formula will work ??

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.***

attached file again for your ref. pls check A12 data which is not in DE which result entire below data will be mismatch with your formula.
pls guide.

[tommyt61]

Sir,

Thanks for the reply. pls note both data will not in assending order ,this formula will fulfill my below 2 possibilities but not last one. if pair of data ( inv no and Amt ) not in second pair of data should also consider as mismatch. I am not sure but I think vlookup or index match formula will work ??

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.***

attached file again for your ref. pls check A12 data which is not in DE which result entire below data will be mismatch with your formula.
pls guide.

Is there a reason why you cannot sort AB and DE Ascending ?