Finding 2 or more matches in different columns and row - Excel 2003

[adnan_003]

Hi Excel experts,

I need some help in identifying common numbers that are in different columns and rows and need to show a match with a basic result in a new column that says ‘MATCH’.

No sure what function to use: vlookup, if, match, index etc and not an expert in these.

I’m using excel 2003 at work, so I need a function that works in this version and my excel is medium at most. I need a formula that I can type in the new columns to find a match basically when two people ring the same person. My data is in different columns and rows.

Here is an example: I have 5 columns: ref field blank. Attr refer to name. Could not not copy table properly.

A B C D E
Ref Attr C Number R Number Attr

9999 1234 BOB
TOM 5555 9999
CLAIRE 1111 0001
SANDRA 2222 0001
0002 3333 YODA
0002 4444 DARTH

In the above example 9999 (unknown) rings 1234 (BOB)
On another occasion 555 (TOM) rings 9999 (unknown)

9999 is the ‘common number’ communicated between two different people, however
9999 was calling 1234 and on another occasion was receiving from 5555.

My data set could be every long so I don’t want to type in names and numbers specifically (there are loads) but rather search the spreadsheet (not table) using columns and rows.

I want the new column to show the word ‘MATCH’ when 9999 was called by 2 number or greater and this result is shown on the new column. I can then filter the results if need be. 9999 is simply the example, the formula will need to cater any number in column C or D.

Any ideas or help with a formula that will work to do this when looking for any matches by 2 callers or more. If the formula can also show ‘MATCH’ when Claire or Sandra ring the same person (0001 - unknown) or when 0002 (unknown) calls Yoda (3333) and Darth (4444). Data could be anywhere in column C and D.

Thank you very much, every grateful.

I know someone will know!

Adnan

 

[oldbrewer]

9999	1234	bob
tom	5555	9999
claire	1111	0001
sandra	2222	0001				problem statement
0002	2222	yoda
0002	4444	darth
						find any number that occurs
						2 or more times
						anywhere in the table
						is this correct ?

<colgroup><col><col span="9"></colgroup><tbody>
</tbody>

 

[adnan_003]

Not that simple..two numbers or greater calling or receiving the same unique number. The job is to find common numbers between 2 or more parties... Hope that helps clear things

 

[adnan_003]

The table of data are all matches, but dont know how to show a match..I will have 98% data that is no match

 

[adnan_003]

here is test table again.

hope it works

problem - 2 numbers or GREATER in column c or d connecting or shown to 1 number... that is common. data could be in both columns and or 1 side only..

a	b	c	d	e	f
ref	name	c no	r no	name
		9999	1234	bob	show match
	tom	5555	9999		show match
	claire	1111	0001		show match
	sandra	2222	0001		show match
		0002	3333	yoda	show match
		0002	4444	darth	show match
		5896	1789		should be blank
		8952	9634		should be blank

<tbody>
</tbody><colgroup><col><col><col span="2"><col><col></colgroup>

 

[adnan_003]

no takers!

 

[oldbrewer]

I cannot understand the issue - why are names in first and 4th columns. Once we see clearly what you want, a solution will be provided.

caller......number called........respondents name
tom............001.........................sid
fred............002.........................bill
harry..........001.........................sid

in terms of this simplified example what do you want ?

 

[adnan_003]

not sure i can explain any more, but thank you..this is phone analysis and need to find 1 common number called or recieved by 2 or more diffrent numbers.

 

[oldbrewer]

caller......CALLER No......number called........respondents name
tom............001...................007........................sid
fred............002...................008........................bill
harry..........003...................007........................sid

so you want to "FIND" sid has been called by 001 and 003 ?

I am away for 8 days now somebody else will take over I am sure.

 

[adnan_003]

yes but the caller no and number called can be reversed sometimes and I need to pick this up...not sure if a formula is best or something else in excel... in fact find dat one side is easy, If I do a sort, but not when the data reverses itself, receiver now calls called, this is where I miss data or find it very hard to pick up...

caller......CALLER No......number called........respondents name
tom............001...................007........................sid
fred............002...................008........................bill
harry..........003...................007........................sid
sid.............007...................001........................tom
sid.............007...................003........................harry