Interesting formula or macro for a n00b

[BustedAvi]

Hi all,

First off, let me say I am no expert whatsoever in VBA or any type of excel formulas. I know the basics well, but my problem here is way passed my ability to program. I hope someone doesn't mind help me out .

Here it goes.

I have column A and column B.

I would like a formula or macro that would enable me to know if a value in column A that appears more than once (say in rows 1 and 2 for example) corresponds to identical values in column B.

For example.

If
A1 = 1
A2 = 2
A3 = 2
A4= 1
and
B1 = X
B2 = Y
B3 = Z
B4 = X

I'd like it to tell me that:
- value "1" in column A matches value "X" twice
-value "2" in column A matches value "Y" once
-value "2" in column A matches value "Z" once

Or, more simply, maybe just:
-value "1" in column A matches 2 identical values
-value "2" in column A matches 2 non-identical values

I realize this might be a lot to ask, but counting the occurrences by hand has become extremely time consuming! I would be eternally grateful!

 

[dmash]

It is not the most elegant solution, but it should work.

In column C, concatenate column A and B, i.e. in cell C1 type in
=a1&b1

Then pivot column C, and put it in say, column D.
In cell E1 type in
=countif(c:c,d1)

Lastly, do auto filter on column E for values greater than 1.

 

[njimack]

Excel Workbook
	A	B	C
1	1	X	2
2	2	Y	1
3	2	Z	1
4	1	X	2
Sheet1

 

[BustedAvi]

wow njimack,
that was so quick.

I'm having trouble with it though.
I've pasted your formula and dragged it down column C, but I'm getting values of 0's???

I think the problem may be that I forgot to mention that values in column A can appear in column B, but column A will never equal column B.

I took a snapshot of my data.

 

[dmash]

Highlight column C and do Find&Replace.

Find: $4
Replace: $23 (or whatever is the last row of your data)

 

[BustedAvi]

Wow, awesome!
Thanks a million.

Now I have to do the same comparing column B to column A.
My thoughts were to just reverse the columns in a new sheet, and use the same formula.

Unless if theres an easier way to see both comparisons side by side?

 

[BustedAvi]

I have to apologize.
It seems I became overly excited.

This formula is useful for telling me how many times Ax matches with Bx, but what I really need is a more categorical approach.

For example, If one matches with a specific other all the time, result = unique
If one matches with more than one other = multiple.

I'm so sorry for being a bit slow at this.

 

[BustedAvi]

Anyone have a clue how i could organize this?

If you refer to the image i posted, I need to know how many times a value in column A matches with different unique values in column B, and vice versa.

For example, if you look at P209 in column A, I need it to tell me it pairs with 4 unique values in column B (namely, P277, P287,P217, P278, but I only need a total #) and that P209 in column B pairs with 2 values in column A ( namely, P13, P208).

Any suggestions are welcome.