Excel comparison question

Closed
dare05 Posts 4 Registration date Monday August 2, 2010 Status Member Last seen August 2, 2010 - Aug 2, 2010 at 12:24 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 2, 2010 at 01:35 PM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 2, 2010 at 12:51 PM
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
0
dare05 Posts 4 Registration date Monday August 2, 2010 Status Member Last seen August 2, 2010
Aug 2, 2010 at 01:04 PM
Btw, each column containts 9500+ so it would be cool if there's some more automatic method.
0
dare05 Posts 4 Registration date Monday August 2, 2010 Status Member Last seen August 2, 2010
Aug 2, 2010 at 01:09 PM
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
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 2, 2010 at 01:18 PM
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)
0
dare05 Posts 4 Registration date Monday August 2, 2010 Status Member Last seen August 2, 2010
Aug 2, 2010 at 01:23 PM
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.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 2, 2010 at 01:35 PM
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.
0