IF statement to compare multiple cells?

[247lanparty]

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

 

[IML]

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]

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]

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]

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]

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]

Book2
	E	F	G	H
1
2	PRTN_PORT=PM1-0021-21	1
3	PRTN_PORT=PM1-0010-16	0
4	PRTN_PORT=PME-0656	1
5	PRTN_PORT=PM1-0010-17	1
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]

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!

 

[just_jon]

OK, and I MEANT to say anything but 0 means Duplicate data!