# Excel Column Comparison Formula Needed



## Rockz (Oct 1, 2007)

Hello everyone,

I am trying to put together an Excel formula that will compare two columns of data that are on the same Worksheet and I would like the results on the same Worksheet as well. One column of data is 4600 rows and the other column is 3900 rows. I simply want to know if each number in the first column is included in the second column or not. Results can just be yes or no. 

Also, one of the data columns is being pulled as Text format from the system, is there a way to change an entire column to Numeric?

Please let me know if this needs further clarification and I'll do my best.

Thanks a ton in advance!
Rockz


----------



## jimr381 (Jul 20, 2007)

Welcome to the forum.

You can change the datatype by selecting the column, right-clicking on it and selecting the "Format Cells" option. This dialog box will allow you to change the datatype among other things. For the Comparison I might do a simple Vlookup to lookup the values in the first column in the second or vice versa. Any of the "N/A"s will not be found in the other column.


----------



## SolarDiablo (Oct 1, 2007)

See comparison code in the attached file.


----------



## Rockz (Oct 1, 2007)

Thanks guys, I REALLY appreciate the responses, quite fast too I might add.

SolarDiablo, your example spreadsheet is exactly what I was looking for. However, when applying it to my data, it tends to stop working once it comes across a number in column A that has its equivalent number listed in column B prior to the point that it is listed in column A.

I have made a change in your example to show what I mean. On row 8 I added a 7 to column B. So now row 9 should actually show Yes, but it is showing No. Do you know of a way to have the results search the entire column rather than just from that point and beyond? It seems to be neglecting any criteria that is listed previously. Your formula looks like it should include the entire column no matter when the data is listed, but for some reason it isn't picking it up correctly. Any thoughts would be appreciated.

Thanks again for your help.
Rockz


----------



## jimr381 (Jul 20, 2007)

Here is the same thing using a vlookup function.


----------



## SolarDiablo (Oct 1, 2007)

The VLOOKUP will work, but it leaves the NA. I changed the example again. Using an array ( putting the {} in the equation), it will find each instance. Unfortunately, I had to take the one nested IF statement out to get the array to work. So I added another column to give the desired "Yes" and "No" results. You could always hide the column with the arrays in it.

Cheers!


----------



## Rockz (Oct 1, 2007)

Thanks again to both of you guys!

I'll implement them tomorrow and see how they work out.

Rockz


----------



## Rollin_Again (Sep 4, 2003)

You can also use an *IF* function combined with *COUNTIF*

See attached Workbook. Just copy the formula into your first cell and copy down for all subsequent rows.

=IF(COUNTIF(Sheet1!$E$3:$E$15,B3)>0,"FOUND","NOT FOUND")

Regards,
Rollin


----------



## SolarDiablo (Oct 1, 2007)

Rollin_Again,

Nice! Yours is much cleaner. Thanks!
SolarDiablo


----------



## Rollin_Again (Sep 4, 2003)

Just as a side note, I have noticed that sometimes when I export data from one of my work applications to Excel some of the columns of numbers are exported as text. These columns retain their text format even if you right click the column and physically change the cell/column format to number. If this happens you can easily force the range of text cells to a number format by doing the following.

1) Enter the number 1 in any un-used cell in the sheet.

2) Right click the cell containing the number 1 and select "COPY"

2) Highlight your range of data that is formatted as text (or select the entire column ) 

3) Right click the selected range of text and select "PASTE SPECIAL"

4) Choose the option "MULTIPLY"

Multiplying each cell's value by the number of 1 will force the cell to take a number format while retaining the original value.

Regards,
Rollin


----------



## Rockz (Oct 1, 2007)

Thanks again for all of the responses guys, I think I've implemented a piece of each one in my project. I am now facing a new dilemma however.

I've attached an example to hopefully make my explanation a little clearer. I'm now needing to compare two columns against another pair of columns, but I need each pair to stay together during the comparison. 

Referencing the example, on row 6, I need to compare the information in columns C & D with the entire F & G columns to see if that information is included anywhere or not, in this case the information is included. The problem I'm running into is that I need to make sure that the rate of 1.1 is tied to customer 1 during the comparison, because if you look down at row 11 you will see a discrepancy, of which the goal of my project is to weed these out. 

So on row 11, I need to compare customer 6 and his rate of 1.24 with the entire columns of F & G, but the 6 and 1.24 must stay together because the rate of 1.24 is tied to this customer. The basis is very similar to what has been provided by several of you in previous posts, but the difference this time is keeping the information of two cells tied together during the comparison.

So is there any way to tie two columns together and then compare them to another set of columns? I have almost 5000 lines to apply this to so I'm really hoping there is a way to do it.

Please let me know if I can provide any further clarification. Many thanks.
Rockz


----------



## Rollin_Again (Sep 4, 2003)

Why don't you just add a helper column and concatenate the values for each set of two columns and then use the formulas provided earlier to compare the values in the helper columns?

In your example you can place the following formula in cell E6 and copy down for all rows.

*=CONCATENATE(C6 & " " & D6)*

Then put a similar formula in cell H6 and copy down for all rows

*=CONCATENATE(F6 & " " & G6)*

Once you have these two new "helper" columns created you can use the formulas provided earlier to compare the values of the helper columns (columns E and H) to determine if a match is made or not.

See attached example

Regards,
Rollin


----------



## S0crates9 (Jul 22, 2008)

Gentlemen,

The following formula suggested: =IF(COUNTIF(Sheet1!$E$3:$E$15,B3)>0,"FOUND","NOT FOUND")

Would work great in some regards but here is where it differs for me and I am not sure how to accomplish this - I need to be able to compare one value to a range on a separate sheet, then apply the result in a column associated with the range value. for Example:

Sheet 1 Value is (A1): Word
to be compared with the range from Sheet 2 which has these values:
(A1) (B1)
trend 10
and 30
word 600
if 2

So if the word in sheet 1 ("word") is found in the A1 column in sheet 2, then 600 should be published. The problem is that I have multiple cells that need to do this so the formula would be reused and needs to select the correct row to appear in each case. The logic behooves me...

I appreciate help on this folks!


----------



## Aj_old (Sep 24, 2007)

S0crates9 said:


> Gentlemen,
> 
> The following formula suggested: =IF(COUNTIF(Sheet1!$E$3:$E$15,B3)>0,"FOUND","NOT FOUND")
> 
> ...


Hi and welcome to TSG Forum!
Is always better to stat your own thread than just posting in someone's thread!
So you should start a new thread!
As well, posting a sample file will help to understand better your needs!
But for the moment I can tell you to use Vlookup function


----------



## slurpee55 (Oct 20, 2004)

Rollin_Again said:


> Just as a side note, I have noticed that sometimes when I export data from one of my work applications to Excel some of the columns of numbers are exported as text. These columns retain their text format even if you right click the column and physically change the cell/column format to number. If this happens you can easily force the range of text cells to a number format by doing the following.
> 
> 1) Enter the number 1 in any un-used cell in the sheet.
> 
> ...


I use this a lot Rollin - one caveat, if you highlight the whole column, all the cells with no data will be filled with a zero - not always an outcome one wants.


----------



## slurpee55 (Oct 20, 2004)

S0crates9 said:


> Gentlemen,
> 
> The following formula suggested: =IF(COUNTIF(Sheet1!$E$3:$E$15,B3)>0,"FOUND","NOT FOUND")
> 
> ...


Hi Socrates - as AJ said, you should start your own thread. However, this is pretty simple - the formula =VLOOKUP(A1,Sheet2!A:B,2,0) should work for you. Just put it in column B on sheet1 and run it down next to your words to look up.


----------



## jimr381 (Jul 20, 2007)

If you do not want to select the first cell but want to select the rest of the column you can click on the second cell in the and do "CTRL+SHIFT+Down Arrow" this will take you to the end of a list. If you hit it a second time then it will take you to very end of the spreadsheet.


----------

