Does anyone have any suggestion on how to count the matched values?

There is a list of values under column A & B
[Column A] 1,5,6,8,9,10,3,20
[Column B] 6,4,9

I would like to count the number of matched value between both columns, but
I would like to set an range [+/- 1] for each values in column B. The set
value 1 is inputed in cell D1. Such as 4 will not be found in column A, but
after the range applied to value 4, if any number is found in column A
between 3 [4-1] and 5 [4+1], then it should belong to matched value.
The matched values are 6, 4 and 9, and it will return 3 in cell C1.
Does anyone have any suggestions on how to do it?

Thanks in advance for any suggestions
Eric

Why isn't it 6, i.e.. matching 5,6,8,9,10 and 3?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Eric" <(E-Mail Removed)> wrote in message
news:F5B23F0E-F4B2-4C4F-8DD5-(E-Mail Removed)...
> Does anyone have any suggestion on how to count the matched values?
>
> There is a list of values under column A & B
> [Column A] 1,5,6,8,9,10,3,20
> [Column B] 6,4,9
>
> I would like to count the number of matched value between both columns,
> but
> I would like to set an range [+/- 1] for each values in column B. The set
> value 1 is inputed in cell D1. Such as 4 will not be found in column A,
> but
> after the range applied to value 4, if any number is found in column A
> between 3 [4-1] and 5 [4+1], then it should belong to matched value.
> The matched values are 6, 4 and 9, and it will return 3 in cell C1.
> Does anyone have any suggestions on how to do it?
>
> Thanks in advance for any suggestions
> Eric

There is a list of values under column A & B
[Column A] 1,5,6,8,9,10,3,20
[Column B] 6,4,9

Because I would like to count the number of matched values on column B
rather than on column A.
Do you have any suggestions?
Thank you for any suggestions
Eric

"Bob Phillips" wrote:

> Why isn't it 6, i.e.. matching 5,6,8,9,10 and 3?
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "Eric" <(E-Mail Removed)> wrote in message
> news:F5B23F0E-F4B2-4C4F-8DD5-(E-Mail Removed)...
> > Does anyone have any suggestion on how to count the matched values?
> >
> > There is a list of values under column A & B
> > [Column A] 1,5,6,8,9,10,3,20
> > [Column B] 6,4,9
> >
> > I would like to count the number of matched value between both columns,
> > but
> > I would like to set an range [+/- 1] for each values in column B. The set
> > value 1 is inputed in cell D1. Such as 4 will not be found in column A,
> > but
> > after the range applied to value 4, if any number is found in column A
> > between 3 [4-1] and 5 [4+1], then it should belong to matched value.
> > The matched values are 6, 4 and 9, and it will return 3 in cell C1.
> > Does anyone have any suggestions on how to do it?
> >
> > Thanks in advance for any suggestions
> > Eric
>
>
>

Hi,

Try this:

=MATCH(B1:B3,A1:A8)

the range can be changed
enter as an array formula: hold Ctrl and Shift and then hit Enter

Thanks,
--
Farhad Hodjat


"Eric" wrote:

> Does anyone have any suggestion on how to count the matched values?
>
> There is a list of values under column A & B
> [Column A] 1,5,6,8,9,10,3,20
> [Column B] 6,4,9
>
> I would like to count the number of matched value between both columns, but
> I would like to set an range [+/- 1] for each values in column B. The set
> value 1 is inputed in cell D1. Such as 4 will not be found in column A, but
> after the range applied to value 4, if any number is found in column A
> between 3 [4-1] and 5 [4+1], then it should belong to matched value.
> The matched values are 6, 4 and 9, and it will return 3 in cell C1.
> Does anyone have any suggestions on how to do it?
>
> Thanks in advance for any suggestions
> Eric

Hi,

Sorry i missed a part of formula:

=MATCH(B1:B3,A1:A8,1)

enter it as an array formula.

Thanks,
--
Farhad Hodjat


"Farhad" wrote:

> Hi,
>
> Try this:
>
> =MATCH(B1:B3,A1:A8)
>
> the range can be changed
> enter as an array formula: hold Ctrl and Shift and then hit Enter
>
> Thanks,
> --
> Farhad Hodjat
>
>
> "Eric" wrote:
>
> > Does anyone have any suggestion on how to count the matched values?
> >
> > There is a list of values under column A & B
> > [Column A] 1,5,6,8,9,10,3,20
> > [Column B] 6,4,9
> >
> > I would like to count the number of matched value between both columns, but
> > I would like to set an range [+/- 1] for each values in column B. The set
> > value 1 is inputed in cell D1. Such as 4 will not be found in column A, but
> > after the range applied to value 4, if any number is found in column A
> > between 3 [4-1] and 5 [4+1], then it should belong to matched value.
> > The matched values are 6, 4 and 9, and it will return 3 in cell C1.
> > Does anyone have any suggestions on how to do it?
> >
> > Thanks in advance for any suggestions
> > Eric

Thank everyone for suggestions
Even through it returns 3 in cell C1, but it does not seem what I described
in the post.
Thank you for any suggestions
Eric

"Farhad" wrote:

> Hi,
>
> Sorry i missed a part of formula:
>
> =MATCH(B1:B3,A1:A8,1)
>
> enter it as an array formula.
>
> Thanks,
> --
> Farhad Hodjat
>
>
> "Farhad" wrote:
>
> > Hi,
> >
> > Try this:
> >
> > =MATCH(B1:B3,A1:A8)
> >
> > the range can be changed
> > enter as an array formula: hold Ctrl and Shift and then hit Enter
> >
> > Thanks,
> > --
> > Farhad Hodjat
> >
> >
> > "Eric" wrote:
> >
> > > Does anyone have any suggestion on how to count the matched values?
> > >
> > > There is a list of values under column A & B
> > > [Column A] 1,5,6,8,9,10,3,20
> > > [Column B] 6,4,9
> > >
> > > I would like to count the number of matched value between both columns, but
> > > I would like to set an range [+/- 1] for each values in column B. The set
> > > value 1 is inputed in cell D1. Such as 4 will not be found in column A, but
> > > after the range applied to value 4, if any number is found in column A
> > > between 3 [4-1] and 5 [4+1], then it should belong to matched value.
> > > The matched values are 6, 4 and 9, and it will return 3 in cell C1.
> > > Does anyone have any suggestions on how to do it?
> > >
> > > Thanks in advance for any suggestions
> > > Eric

Just a warning...

Eric has reposted the same question in .worksheet.functions.



Eric wrote:
>
> Does anyone have any suggestion on how to count the matched values?
>
> There is a list of values under column A & B
> [Column A] 1,5,6,8,9,10,3,20
> [Column B] 6,4,9
>
> I would like to count the number of matched value between both columns, but
> I would like to set an range [+/- 1] for each values in column B. The set
> value 1 is inputed in cell D1. Such as 4 will not be found in column A, but
> after the range applied to value 4, if any number is found in column A
> between 3 [4-1] and 5 [4+1], then it should belong to matched value.
> The matched values are 6, 4 and 9, and it will return 3 in cell C1.
> Does anyone have any suggestions on how to do it?
>
> Thanks in advance for any suggestions
> Eric

--

Dave Peterson

Thank everyone for suggestions
Do you have any suggestions?
Eric

"Dave Peterson" wrote:

> Just a warning...
>
> Eric has reposted the same question in .worksheet.functions.
>
>
>
> Eric wrote:
> >
> > Does anyone have any suggestion on how to count the matched values?
> >
> > There is a list of values under column A & B
> > [Column A] 1,5,6,8,9,10,3,20
> > [Column B] 6,4,9
> >
> > I would like to count the number of matched value between both columns, but
> > I would like to set an range [+/- 1] for each values in column B. The set
> > value 1 is inputed in cell D1. Such as 4 will not be found in column A, but
> > after the range applied to value 4, if any number is found in column A
> > between 3 [4-1] and 5 [4+1], then it should belong to matched value.
> > The matched values are 6, 4 and 9, and it will return 3 in cell C1.
> > Does anyone have any suggestions on how to do it?
> >
> > Thanks in advance for any suggestions
> > Eric
>
> --
>
> Dave Peterson
>

Only that you shouldn't multipost your question.

Eric wrote:
>
> Thank everyone for suggestions
> Do you have any suggestions?
> Eric
>
> "Dave Peterson" wrote:
>
> > Just a warning...
> >
> > Eric has reposted the same question in .worksheet.functions.
> >
> >
> >
> > Eric wrote:
> > >
> > > Does anyone have any suggestion on how to count the matched values?
> > >
> > > There is a list of values under column A & B
> > > [Column A] 1,5,6,8,9,10,3,20
> > > [Column B] 6,4,9
> > >
> > > I would like to count the number of matched value between both columns, but
> > > I would like to set an range [+/- 1] for each values in column B. The set
> > > value 1 is inputed in cell D1. Such as 4 will not be found in column A, but
> > > after the range applied to value 4, if any number is found in column A
> > > between 3 [4-1] and 5 [4+1], then it should belong to matched value.
> > > The matched values are 6, 4 and 9, and it will return 3 in cell C1.
> > > Does anyone have any suggestions on how to do it?
> > >
> > > Thanks in advance for any suggestions
> > > Eric
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Sorry for multipost question
Do you have any suggestions?
Thank everyone for any suggestions
Eric

"Dave Peterson" wrote:

> Only that you shouldn't multipost your question.
>
> Eric wrote:
> >
> > Thank everyone for suggestions
> > Do you have any suggestions?
> > Eric
> >
> > "Dave Peterson" wrote:
> >
> > > Just a warning...
> > >
> > > Eric has reposted the same question in .worksheet.functions.
> > >
> > >
> > >
> > > Eric wrote:
> > > >
> > > > Does anyone have any suggestion on how to count the matched values?
> > > >
> > > > There is a list of values under column A & B
> > > > [Column A] 1,5,6,8,9,10,3,20
> > > > [Column B] 6,4,9
> > > >
> > > > I would like to count the number of matched value between both columns, but
> > > > I would like to set an range [+/- 1] for each values in column B. The set
> > > > value 1 is inputed in cell D1. Such as 4 will not be found in column A, but
> > > > after the range applied to value 4, if any number is found in column A
> > > > between 3 [4-1] and 5 [4+1], then it should belong to matched value.
> > > > The matched values are 6, 4 and 9, and it will return 3 in cell C1.
> > > > Does anyone have any suggestions on how to do it?
> > > >
> > > > Thanks in advance for any suggestions
> > > > Eric
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>