# Comparing Two Columns

• What I'm attempting to do is analyze data on spread sheet. I've attached an example. what I need to do is compare columns "E" and "I". But I have numerous means of comparing the data. I think I will need three different formulas which is okay and am unsure if VBA is better. For example: I compare "E2" and "I2"...ect:
1) I would need to compare complete cell
2) I would need to compare the first three digits
3) I would need to compare the middle digits
If you towards the end you can see that some will compare to a BLANK cell if column "E" is ever blank. I first tried using countif but couldn't get it done. I only need totals that do not match in this category. i.e. 25 of 140 do not match. I appreciate any help.

BillyJ

## Files

• Re: Comparing Two Columns

billyj,

Assuming you want to compare columns C and G:

=SUMPRODUCT((G2:G187=C2:C187)*1) for complete cell comparison
=SUMPRODUCT((LEFT(G2:G187,3)=LEFT(C2:C187,3))*1) for first three digits

See if that will get you started.

Jim

• Re: Comparing Two Columns

Thanks I thought it would be a sumproduct formula just not very good at using them. I have an additional question. how can I make my automate my range for data. in this situation I had 187 lines but tomorow I will have 225. Is there a way so I do not have to change my formula everyday. I also adde formula for middle two and stillworking the blanks.
Thanks BillyJ

=SUMPRODUCT((G2:G187=C2:C187)*1) for complete cell comparison
=SUMPRODUCT((LEFT(G2:G187,3)=LEFT(C2:C187,3))*1) for first three digits
=SUMPRODUCT((MID(G2:G187,2,2)=MID(C2:C187,2,2))*1)

• Re: Comparing Two Columns

You could use a dynamic range name for each column of data. One caution, for sumproduct to work, both ranges should be the same size. This can still be done as long as you can know which column has the most entries. Someone really good with formulas could probably do it without knowing up front which column is the longest.

Jim

• Re: Comparing Two Columns

Thanks if I know column "G" will always be longer than "C" does that make it easier?

• Re: Comparing Two Columns

Have a look at the attached for [Insert][Names] from the Excel menu. See how you can use the length of column G to specify the range for column C?

## Files

• Re: Comparing Two Columns

Thanks never really understood how to ranges via the Insert>names I'll work on that. I tried to increase the data in column "C" and "G" and the data did not change. am I doing something incorrect? Thanks again...I'll keep pressing

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!