Match formula
 

hope i can get some help on this one
looking for a formula that looks into a group of numbers (8) and then
looks into another group of numbers (8) and returns a value if if there
are any matchs.

example
a b c d e
1 3 7 1 5 0
2 2 6 3 4 8 "result=1"
3
4
5

thanks in advance

Match formula
 

Hi!

What result would you expect if these were the numbers:

a b c d e
1 3 7 1 8 0
2 3 6 3 4 8

Biff

"bob" wrote in message
oups.com...
hope i can get some help on this one
looking for a formula that looks into a group of numbers (8) and then
looks into another group of numbers (8) and returns a value if if there
are any matchs.

example
a b c d e
1 3 7 1 5 0
2 2 6 3 4 8 "result=1"
3
4
5

thanks in advance

Match formula
 

thanks Biff
ok i want to count the number of times a matching number appears in
row1 from a group of numbers in row2 so i would expect the result of
your question to be "3". i can do a countif (a1,a2:e2) in cell W1 then
do a countif(b1,a2:e2) in cell X1etc.but i have a group of 8 cells to
try and match and was hoping there would be an better way to do it in
just one cell for all 8 cells

something like =countif (a1:e1, anymatchs a2:e2) but i know thats not
it
thanks in advance

bob


Biff wrote:
Hi!

What result would you expect if these were the numbers:

a b c d e
1 3 7 1 8 0
2 3 6 3 4 8

Biff

"bob" wrote in message
oups.com...
hope i can get some help on this one
looking for a formula that looks into a group of numbers (8) and then
looks into another group of numbers (8) and returns a value if if there
are any matchs.

example
a b c d e
1 3 7 1 5 0
2 2 6 3 4 8 "result=1"
3
4
5

thanks in advance

Match formula
 

Try this:

=SUMPRODUCT(COUNTIF(A1:E1,A2:E2))

Biff

"bob" wrote in message
oups.com...
thanks Biff
ok i want to count the number of times a matching number appears in
row1 from a group of numbers in row2 so i would expect the result of
your question to be "3". i can do a countif (a1,a2:e2) in cell W1 then
do a countif(b1,a2:e2) in cell X1etc.but i have a group of 8 cells to
try and match and was hoping there would be an better way to do it in
just one cell for all 8 cells

something like =countif (a1:e1, anymatchs a2:e2) but i know thats not
it
thanks in advance

bob


Biff wrote:
Hi!

What result would you expect if these were the numbers:

a b c d e
1 3 7 1 8 0
2 3 6 3 4 8

Biff

"bob" wrote in message
oups.com...
hope i can get some help on this one
looking for a formula that looks into a group of numbers (8) and then
looks into another group of numbers (8) and returns a value if if there
are any matchs.

example
a b c d e
1 3 7 1 5 0
2 2 6 3 4 8 "result=1"
3
4
5

thanks in advance

Match formula
 

I think you need something like:

=SUM(--(COUNTIF(A1:E1,A2:E2)<0))

entered as array formula (i.e. confirmed with Shift+Ctrl+Enter). The
philosophy of this formula is that it goes over each element in row 2
and asks if the element appears at least once.

Is it what you want?
Kostis Vezerides

Match formula
 

Try also:
=SUMPRODUCT(--ISNUMBER(MATCH(A2:E2,A1:E1,0)))
Adapt to suit for an 8 cells stretch ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Match formula
 

thanks much max works like a charm..for some reason the other two did
not but thanks to all for trying will perhaps use those as needs
require

bob

Max wrote:
Try also:
=SUMPRODUCT(--ISNUMBER(MATCH(A2:E2,A1:E1,0)))
Adapt to suit for an 8 cells stretch ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Match formula
 

You're welcome !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Match formula
 

Max
is there a way to change the font to red for any matchs found if your
formula returns a returns a value of 1 or more
Max wrote:
You're welcome !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Match formula
 

"bob" wrote:
.. is there a way to change the font to red
for any matchs found if your formula
returns a returns a value of 1 or more

Use conditional formatting

Select A2:E2 (ensure A2 is active)
Format Cond Format
Formula is: =ISNUMBER(MATCH(A2,$A$1:$E$1,0))
Format to taste (font tab red & bold?) OK out

Numbers in A2:E2 which match those within A1:E1
will be conditionally formatted
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Match formula
 

Max
What do you mean by (ensure a2 is active) i should have also told you
that i am working with excel 2000. I went into conditional formatting
and did not see anywhere that i could install a formula it just says at
least, equal, more etc. Am i missing something. a BIG thanks for taking
the time to work with me and others

bob

Max wrote:
"bob" wrote:
.. is there a way to change the font to red
for any matchs found if your formula
returns a returns a value of 1 or more

Use conditional formatting

Select A2:E2 (ensure A2 is active)
Format Cond Format
Formula is: =ISNUMBER(MATCH(A2,$A$1:$E$1,0))
Format to taste (font tab red & bold?) OK out

Numbers in A2:E2 which match those within A1:E1
will be conditionally formatted
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Match formula
 

"bob" wrote:
.. What do you mean by (ensure a2 is active) i should have also told you
that i am working with excel 2000. I went into conditional formatting
and did not see anywhere that i could install a formula it just says at
least, equal, more etc. Am i missing something.

Let's take it again, nice and slow <g ..

Select A2:E2 (ensure A2 is active)

A2 will be active cell if we select the range A2:E2 by left-clicking on cell
A2, then sweep right to E2. If we select the range the other way, by
left-clicking on cell E2, then sweep left to A2, then E2 will be the active
cell. The active cell will be the one filled "white" in the selected range,
the rest of the cells will be filled "black".

Selecting the range properly will ensure that the conditional format formula
we're going to place below (which is framed for the active cell) will then
"propagate" correctly/relatively within each of the cells in the range when
we complete all the steps for the Conditional format

Format Cond Format
With the range selected properly, click Format Conditional Formatting

In the Conditional Formatting dialog:
Under Condition 1, click the droplist (where you see "Cell Value Is")
and select "Formula Is:"

Formula is: =ISNUMBER(MATCH(A2,$A$1:$E$1,0))
Then copy the formula above and paste into the box to the right of "Formula
Is:"
(After you copy the part "=..." from the post,
click inside the box, then press CTRL+V to paste)

Format to taste (font tab red & bold?) OK out
Click the Format button choose the Font tab
Then select red under "Color" and "Bold" for Font style, click OK
Click OK at the main dialog

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Match formula
 

Max
oooook got the active part down <g and did the conditional formatting
as you said works to a degree. Am i to asume it reads left to right but
not right to left? Reason i am asking is two of the cells that matched
were formatted red but one was not. The two that were red were from
numbers to the right of A2 the one that did not was to the left of AE2

Example
A1 6636 3133 6661 3611 1113 6316 3313 6116
A2 1336 3163 1113 6316 3611 1616 3311 6366
1113 and 6316 were formatted but the 3611 which is also a match did not
thanks again !!!

bob


Max wrote:
"bob" wrote:
.. What do you mean by (ensure a2 is active) i should have also told you
that i am working with excel 2000. I went into conditional formatting
and did not see anywhere that i could install a formula it just says at
least, equal, more etc. Am i missing something.

Let's take it again, nice and slow <g ..

Select A2:E2 (ensure A2 is active)

A2 will be active cell if we select the range A2:E2 by left-clicking on cell
A2, then sweep right to E2. If we select the range the other way, by
left-clicking on cell E2, then sweep left to A2, then E2 will be the active
cell. The active cell will be the one filled "white" in the selected range,
the rest of the cells will be filled "black".

Selecting the range properly will ensure that the conditional format formula
we're going to place below (which is framed for the active cell) will then
"propagate" correctly/relatively within each of the cells in the range when
we complete all the steps for the Conditional format

Format Cond Format
With the range selected properly, click Format Conditional Formatting

In the Conditional Formatting dialog:
Under Condition 1, click the droplist (where you see "Cell Value Is")
and select "Formula Is:"

Formula is: =ISNUMBER(MATCH(A2,$A$1:$E$1,0))
Then copy the formula above and paste into the box to the right of "Formula
Is:"
(After you copy the part "=..." from the post,
click inside the box, then press CTRL+V to paste)

Format to taste (font tab red & bold?) OK out
Click the Format button choose the Font tab
Then select red under "Color" and "Bold" for Font style, click OK
Click OK at the main dialog

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Match formula
 

ops i found out why ..was going to use it on other cells so dumb me
took out the $ ..when i put it back in works like a charm......BIG
THANKS !!!

bob

Match formula
 

Max
i know i know gessssss this guy is turning into a pain in the b***<g
anyway now that i or we have the A1 A2 format down pat is there perhaps
a way to add a second conditional format to A1 but this time go to a
group of cells which has been marked with a "x" in another cell. I am
trying to follow 2 groups of cells this way

something like
=ISNUMBER(MATCH(A2,$A$1:$E$1,0))+ISNUMBER(MATCH(A2 ,$A$22:$E$22)) IF H22
"X"

Match formula
 

"bob" wrote in message
ups.com...
... now that i or we have the A1 A2 format down pat is there perhaps
a way to add a second conditional format to A1 but this time go to a
group of cells which has been marked with a "x" in another cell. I am
trying to follow 2 groups of cells this way, something like
=ISNUMBER(MATCH(A2,$A$1:$E$1,0))
+ISNUMBER(MATCH(A2,$A$22:$E$22)) IF H22 = "X"

Here's one way ..

A sample construct is available at:
http://www.savefile.com/files/3175912
Complex Match Formula in Cond Formatting.xls

Putting it as the 2nd** condition (using 2 separate conditions 1 & 2):
(CF range is A2:E2)

Condition 1, Formula is: =ISNUMBER(MATCH(A2,$A$1:$E$1,0))
Format font tab red/bold

(above as before, no change)

Click "Add", to add 2nd condition

Condition 2, Formula is:
=SUMPRODUCT(--ISNUMBER(MATCH(A2,OFFSET($H$1,MATCH("X",$H:$H,0)-1,-7,,5),0)))
0
Format font tab blue/bold (say)
OK out

**Equivalent to the structu
=IF(Cond1,"red/bold",IF(Cond2,"blue/bold",""))
where Cond1 will be evaluated first before Cond2, going from left to right

Note that it's assumed col H will contain only a single "X" at any one time,
to indicate the range, eg: A22:E22 to be used as the MATCH lookup array (2nd
condition)

---

The other way (which returns different results), is if we say, wish to
include the additional check as a single condition (under Condition 1) using
OR:

Putting it as a single condition (under Condition 1) using OR:
(CF range is A2:E2)

Condition1, Formula is:
=OR(ISNUMBER(MATCH(A2,$A$1:$E$1,0)),
SUMPRODUCT(--ISNUMBER(MATCH(A2,
OFFSET($H$1,MATCH("X",$H:$H,0)-1,-7,,5),0)))0)

Format font tab red/bold OK out
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Match formula
 

Wow unbelievable thanks.can I call you Mr. Excel? And would you mind if
I pass your name on to Bill Gates

Match formula
 

You're welcome, Bob !
Thanks for the feedback ..

(I'm just one of the many folks enjoying himself here <g!)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"bob" wrote in message
ups.com...
Wow unbelievable thanks. can I call you Mr. Excel?
And would you mind if I pass your name on to Bill Gates