Excel comparison question

[Closed]
Report
Posts
4
Registration date
Monday August 2, 2010
Status
Member
Last seen
August 2, 2010
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

Here's my problem:

I have 2 columns that contain around 90% same but 10% different values (column A starts from second row A2, column B starts from second row as well B2).

Now, I need to a script or a function that can:

COMPARE column A with B

In column C, write the CELLS which are in column B but NOT in column A.

For example, column A contains the following data: a1, b1, c1, d1, e1

Column B contains the following data: a1, c1, f1, g1

In this case, the data that needs to be in column C is f1 and g1.

Believe me, I've read a lot on this forum and got some software apps to help me do that but all they do is compare on the basis of rows, if data in 2 columns is different in 2 rows, they mark that as a difference. In the previous example, c1 is in column B (2nd row) and b1 is in column A (2nd row.) b1 and c1 are both in row 2 but that doesn't mean something is different. The same data is contained in one cell (doesn't matter where it's located) in both columns so the script/function should treat it as matching data when comparing.


3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
Posts
4
Registration date
Monday August 2, 2010
Status
Member
Last seen
August 2, 2010

Btw, each column containts 9500+ so it would be cool if there's some more automatic method.
Posts
4
Registration date
Monday August 2, 2010
Status
Member
Last seen
August 2, 2010

Okay here it is:

https://authentification.site/files/23633005/links-page-www.whitepages.com.xlsx

As I've mentioned, each column has 9700+ rows. I've modified cell B27 so it's the only cell in column B that's different that column A.

The script/function should output ONLY that cell in column C because it's the only cell that's in column B and NOT in column A.

Regards
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
all you need is a match function like this
https://ccm.net/forum/affich-438055-matching-2-columns-in-excel#p438092

Only difference being that you want to know when the match is not found (in the solution, when then match is not found "" is displayed)
Posts
4
Registration date
Monday August 2, 2010
Status
Member
Last seen
August 2, 2010

Man that will work but you must remember that I can't scroll 9000 rows to see values that don't match. I need this in order to get a clean view of values that don't match without the effort of scrolling 9000+ rows and identifying them manually.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You dont have to scroll. Copy the formula down (for that you just have enter the formula in the first cell and then double click that black box that appears at the bottom of the cell), apply filter on sheet where column C is not blank and you should see only those rows that has some data


If you still want to bring the rows at the top,
1 copy the formula down
2 select col C and choose copy
3. select pastespecial and paste as values
4. sort only column C.