PDA

View Full Version : Excel compare data



t_kid
04-20-2012, 11:34 PM
Hi guys,

I have a VBA problem and I was hoping you could help me. The problem is as follows.

I have 2 columns A and B, each with many names. I want to create a macro that takes the cell A1, and tells me if a cell in column B is equal, by writing "Y" in C1 if there is such a cell, and "N" otherwise.

I want the macro to do this for each cell in column A, and also have no repeats. So if there are 2 cells in column A with the name "James" but only 1 such cell in column B, then only the first "James" should find a match and display "Y" in column C.

Any help would be greatly appreciated.

mikerickson
04-21-2012, 02:07 AM
There's no need for VBA. Putting this in C1 and dragging down will do what you want.

=IF(COUNTIF($A$1:$A1, $A1)<=COUNTIF($B:$B, $A1), "Y", "N")

t_kid
04-21-2012, 09:32 AM
Hi Mike,

Thank you for that, it seems to work.

Could you please explain what is happening in this statement?

Aussiebear
04-21-2012, 06:11 PM
Have you placed this formula in C1 and dragged down? The formula supplied looks at each value in Column A and finds only the first instance of the same value in Column B by placing the value Y in the relevant cell in Column C. It also takes care of the event where a value is repeated in Column A. Any repeated value in Column B is therefore marked by N in column C

mikerickson
04-21-2012, 06:43 PM
This part COUNTIF($A$1:$A1, $A1), when dragged to row n will have changed to
COUNTIF($A$1:$An, $An) which is the number of occurances of An in the cells above it.

COUNTIF($B:$B, $An) is the total number of occurances of An in column B.

If one is COUNTIF($A$1:$An, $An)<=COUNTIF($B:$B, $An) then An has been duplicated in A (above n) fewer times than it has in column B, thus the "Y"

Rlb53
04-21-2012, 07:36 PM
1

t_kid
04-22-2012, 01:02 AM
Thanks again Mike, I appreciate that.

And thanks for the help guys.