IF statement to compare multiple cells?

247lanparty

New Member
Joined
Feb 27, 2004
Messages
4
I am trying to perform a function to lookup and compare a single cell at a time in one column to an entire seperate column on another worksheet to see if the values are reoccuring. I tried using an IF but couldn't get it to work.. any ideas how to do this?

Thanks
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
to get a count of how many times the value of cell A1 is in column H, you could use
=COUNTIF(H:H,A1)

to use get a true false answer if column h contains the cell A1 value at least once, use
=ISNUMBER(MATCH(H:H,A1,0))

in both cases, you would be better served from a performance standpoint by replacing H:H with cell references or a named range
 

shades

Well-known Member
Joined
Mar 20, 2002
Messages
1,550
Not sure I fully have what you want, but perhaps this will get you started. Suppose list 1 is in Column A, and the other in Column E.

In B1 put this formula:

=IF(COUNTIF(E:E,A1)>0,3,1)

copy down to the bottom of column A.

Then in F1 put this formula:

=IF(COUNTIF(A:A,E1)>0,3,2)

and copy down to bottom of column E.


Now, those in column B with 1 are unique to Column A, those with 3, are in both Col. A and Col E. In Column F, if 2, then unique to Col E, if 3, then both in Col. A and Col. C.

Then use this formula as the tally, in G1:

=COUNTIF(B:B,1)+COUNTIF(F:F,2)
 

247lanparty

New Member
Joined
Feb 27, 2004
Messages
4
Hmm... I think this would have worked but The problem is that I have to compare cells with text (numbers & letters)...

can I still do this cause it didn't work
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

Can you an example of the formula you tried, what your answer was and what you expected? Post a small, representative sample of your data?
 

247lanparty

New Member
Joined
Feb 27, 2004
Messages
4
Sheet1 column D has data such as "PRTN_PORT=PM1-0010-17" all through the column only each cell is a little bit different... Here is an example few cells in COlumn D:

PRTN_PORT=PM1-0010-17
PRTN_PORT=PM1-0021-21
PRTN_PORT=PM1-0007-17
PRTN_PORT=PME-0656


Now on sheet2, I have column E with the same sort of entries, but the data is from a different date and I need to reference which entries are reoccuring. Like, is PRTN_PORT=PME-0656 showing up on Sheet 2 and Sheet 1 or is it unique.

Thanks for the help.. i love this site!
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

Book2
EFGH
1
2PRTN_PORT=PM1-0021-211
3PRTN_PORT=PM1-0010-160
4PRTN_PORT=PME-06561
5PRTN_PORT=PM1-0010-171
Sheet2


This sort of thing did not work for you? Is your data coming from 2 different applications? [BTW, anything other than 1 means Duplicated data.]
 

247lanparty

New Member
Joined
Feb 27, 2004
Messages
4
ok.. got it now.. not sure why I couldn't get it to work first time... Now I did some conditional format to where anything duplicated (in my case with a 1) is hilighted yellow.. thanks!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,159
Messages
5,768,520
Members
425,479
Latest member
Neerajcool

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top