If/Then Data Comparison Formula help!??

cDuck28Z

New Member
Joined
Jun 22, 2010
Messages
2
Im at work doing the same tedious work over and over for 18k+ entries and i know excel can do this work for me. here's what i have set up:

ּ ּ ּ ּ A ּ ּ ּ ּ ּ ּ B ּ ּ ּ ּ ּ ּ C
01. 101-A.......101-A.......-------.....
02. 102-A.......103-B.......Error.....
03. 103-A.......106-A.......-------.....
04. 104-A.......107-A.......-------.....
05. 105-A.......109-2........Error.....
06. 106-A.......113-1........Error.....
07. 107-A.......115-A.......-------.....
08. 109-A.....--------
09. 110-A.....--------
10. 112-B.....--------
11. 113-B.....--------
12. 115-A.....--------

I need column C to tell me what the discrepancy between column A and B if there is one at all. I have a formula in column C to tell me that they don't match, but i don't know how to to make it pull any information from the other cells:
=IF(B2=0,"",IF(COUNTIF(A:A,B2),"",IF(ISERROR(FIND(LEFT(B2,3)*A:A,2))=TRUE,"Error","")))

NOTE: this is an extremely simplified example; my document has far more numbers, mixed with some letters in each field. Macro's are also not an option unfortunately. Aligning column B to column A also isn't an option because there are some fields in column B that just don't exist in Column A
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

RayAnderson

New Member
Joined
Apr 22, 2010
Messages
6
Have you tried using an advanced filter?

From what you have said, it seems you are triying to determing if the infortmation in Colum A exists in Column B (or vice versa)

Column A is your Criteria, Column B is your List , copy data to a different loctuion with unique records.

Cheers
 

Forum statistics

Threads
1,144,374
Messages
5,723,983
Members
422,529
Latest member
mbilal429

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top