Compare 2 columns

May 27, 2009 at 09:51:25
Specs: Windows XP
I have a sheet with two columns (is more, but need the info about the two), say D (Part#) and AK (Part#).
I have to compare the two Col with part# to filter the usage out (usage is in Col AP and AS)
I just need to compare the two Col (one part# with the other) to see the same # is in both Columns. Any help out there?

Thanks hr


See More: Compare 2 columns

Report •


#1
May 27, 2009 at 10:08:30
Sort of like "if(<col1>=<col2>,"Same","Different")" would be if(D1=AK1,"Same","Different"). Then copy this formula to all of the rows in your table.

Report •

#2
May 27, 2009 at 10:08:49
re: I just need to compare the two Col (one part# with the other) to see the same # is in both Columns.

If that is all you really need to do, then run this formula down alongside column D:

=IF(ISNA(VLOOKUP(D1,AK:AK,1,0)),"Not In Both","In Both")

If the VOOKLUP doesn't find the part number in AK, it will return #N/A and the IF will return Not In Both. If it finds it, it will return "In Both"


Report •

#3
May 27, 2009 at 10:19:09
@ cyoung311

Even assuming that the 2 columns are sorted in the same order, as soon as Dx doesn't match it's corresponding AKx - meaning a part # is missing in AK, then all the remaining Dx's will not match their corresponding AKx's, even if the number is in both lists.

D       E         AK

1     Same         1      
2     Same         2      
3     Different    4       
4     Different    5  <-- Wrong Answer
5     Different       <-- Wrong Answer


Report •

Related Solutions

#4
May 27, 2009 at 10:21:55
One more note...

If the list in AK is longer than the list in D, then swap the VLOOKUP arguments:

=IF(ISNA(VLOOKUP(AK1,D:D,1,0)),"Not In Both","In Both") and drag to the bottom of the AK list.


Report •

#5
May 27, 2009 at 10:32:05
Hi and thank for the quick response.
I tried the: if(<col1>=<col2>,"Same","Different")" it don't work. It compare only one row with eachoteher. In my case:
one rowCol start with 13541 and the other row/Col with 15621. I would need some where it compares both col.

Report •

#6
May 27, 2009 at 12:54:00
Hi i am back,
=IF(ISNA(VLOOKUP(AK1,D:D,1,0)),"Not In Both","In Both")
What does ISNA stay's for? I tried it but get only "In Both" back. That isn't true.
I sorted both columns with the part#, put the longer one now i column A, column B got the usage of last year, column c is on hand and colum F is the second column with part#.
I tried to to use the Vlookup my self but can't get it to work, so I tried your version. I also draged it to the bottom but it is ether (for all numbers the same) In Both or Not in Both.

Report •

#7
May 27, 2009 at 13:43:53
@ DerbyDad03

"Even assuming that the 2 columns are sorted in the same order, as soon as Dx doesn't match it's corresponding AKx - meaning a part # is missing in AK, then all the remaining Dx's will not match their corresponding AKx's, even if the number is in both lists.

D E AK

1 Same 1
2 Same 2
3 Different 4
4 Different 5 <-- Wrong Answer
5 Different <-- Wrong Answer"

What are you smoking? When D=4 and AK=5 they are indeed different. When D=5 and AK=null they are indeed different. Why is that a wrong answer?

@hr

You have to align the comparison operators between the two fields that you are comparing. I did not intend for you to insert that exact code into your worksheet but use the concept as an example. An easier way for all of this is to import the sheet into a database table and write an "instance not found" query. Sheesh!


Report •

#8
May 27, 2009 at 14:38:51
Here is what I am suggesting...

First, let's begin with the assumption that you are simply trying to determine which of the part numbers in a given list is also in another. If that is not what you are trying to do, please give us some more details.

OK, now let's say you have these 2 lists:

      D            AK

1  Part # 1     Part # 3
2  Part # 3     Part # 4
3  Part # 7     Part # 1
4  Part # 4

As you can see, Part # 1, 3 & 4 are in both lists, Part # 7 is not.

In a cell someplace, you could put:

=IF(ISNA(VLOOKUP(D1,$AK$1:$AK$3,1,0)),"Not In Both","In Both")

and drag it down 3 more cells to get:

=IF(ISNA(VLOOKUP(D2,$AK$1:$AK$3,1,0)),"Not In Both","In Both")

etc.

I'll break it down into its various parts so you'll see how it should work, based on my testing:

=VLOOKUP(D1,$AK$1:$AK$3,1,0) will look up whatever is in D1 in AK1:AK3. If the value exists, it would return that value since we asked it to return the value in the first column of the lookup_array. The 0 tells Excel that AK1:AK3 does not need to be sorted.

If VLOOKUP does not find the value in AK1:AK3, it will return #N/A.

=ISNA(VLOOKUP(D1,$AK$1:$AK$3,1,0)) will either return TRUE or FALSE depending on whether VLOOKUP returns a value or the #N/A error.

We're basically asking Excel to tell us if the result of the VLOOKUP is #N/A or not: Is it #N/A? ISNA(). Get it? ;-)

So, wrapping that in a IF statement we get:

=IF(ISNA(VLOOKUP(D1,$AK$1:$AK$3,1,0)),"Not In Both","In Both")

If ISNA(VLOOKUP(D1,$AK$1:$AK$3,1,0)) is FALSE, it means that VLOOKUP found the value in AK1:AK3 and therefore the value is in both columns. Since the value_if_false of the IF statement is "In Both" the formula will return "In Both"

If ISNA(VLOOKUP(D1,$AK$1:$AK$3,1,0)) is TRUE, it means that VLOOKUP did not find the value in AK1:AK3 and therefore the value is not in both columns. Since the value_if_true of the IF statement is "Not In Both" the formula will return "Not In Both"

With the formula starting in E1,your final result should look like this:

      D          E            AK

1  Part # 1   In Both       Part # 3
2  Part # 3   In Both       Part # 4
3  Part # 7   Not In Both   Part # 1
4  Part # 4   In Both

Let me know if you need anything else.


Report •

#9
May 27, 2009 at 14:43:48
@ cyoung311

re: What are you smoking?

It's amazing how quickly you have become someone not worth having a conversation with.


Report •

#10
May 27, 2009 at 22:02:21
That is an intelligent explanation of your previous assertion.

I assumed the two columns were in a simple spreadsheet and that there was a one-to-one correspondence between the two columns. If you are assuming that there are two columns that could be in any order but still must be checked for similar contents then that changes the whole equation.

Assumptions can be misleading...


Report •

#11
May 28, 2009 at 05:35:34
@ cyoung311

Thank you for that response. I agree that assumptions can be misleading which is why you will see that in both of my responses to the OP I clearly stated what my assumptions were.

However, regardless of what you were assuming, I don't think that there was any need for comments like "What are you smoking" and "Sheesh".

We are all just trying to help each other and it would be a shame if this forum turned into one - like so many others - where insults are more prevalent than solutions.


Report •

#12
May 28, 2009 at 06:51:00
Apologies. No insult intended.

BTW, cool formula.


Report •

#13
May 28, 2009 at 08:39:25
Cool...friends again!

Report •


Ask Question