comparing tables/vectors

Hi there :-)


I have two columns with numeric data [but the kind of data shouldn't
matter, I suppose]. They're of _different_ lenght, but there are empty
cells below both of them.
I want to compare the columns and get the _number_ [amount] of mutual
elements as a result.

Ie.: if A={2, 3, 5, 7, 9, 23, 25, 46}, and
B={1, 3, 4, 6, 7, 8, 9, 46}
than Result=4



If I defined the columns to have the same lenght [including some of the
empty cells below] would the empty cells be counted as well? I wouldn't
like that :-)

Thanks in advance, regards.

--
Uka P.

Assuming the elements are listed in cols A and B,
with col B's items within say, B1:B100

Put in the formula bar for say, C1,
then array-enter the formula (i.e. press CTRL+SHIFT+ENTER):

=SUMPRODUCT(--ISNUMBER(MATCH(TRANSPOSE(B1:B100),A:A,0)))

Adapt the range to suit ..

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Uka P." <[email protected]> wrote in message
news:[email protected]...
> Hi there :-)
>
>
> I have two columns with numeric data [but the kind of data shouldn't
> matter, I suppose]. They're of _different_ lenght, but there are empty
> cells below both of them.
> I want to compare the columns and get the _number_ [amount] of mutual
> elements as a result.
>
> Ie.: if A={2, 3, 5, 7, 9, 23, 25, 46}, and
> B={1, 3, 4, 6, 7, 8, 9, 46}
> than Result=4
>
>
>
> If I defined the columns to have the same lenght [including some of the
> empty cells below] would the empty cells be counted as well? I wouldn't
> like that :-)
>
> Thanks in advance, regards.
>
> --
> Uka P.
>

Try this:

If your values are in Cells A1:B10

C1: =SUMPRODUCT(--COUNTIF(A1:A10,B1:B10))
Note: Commit that array formula by holding down [Ctrl]+[Shift] when you
press [Enter]

Returns the number of common elements from Col_A and Col_B

Note: There can be no repeats withing the same column.

Does that help?

***********
Regards,
Ron


"Uka P." wrote:

> Hi there :-)
>
>
> I have two columns with numeric data [but the kind of data shouldn't
> matter, I suppose]. They're of _different_ lenght, but there are empty
> cells below both of them.
> I want to compare the columns and get the _number_ [amount] of mutual
> elements as a result.
>
> Ie.: if A={2, 3, 5, 7, 9, 23, 25, 46}, and
> B={1, 3, 4, 6, 7, 8, 9, 46}
> than Result=4
>
>
>
> If I defined the columns to have the same lenght [including some of the
> empty cells below] would the empty cells be counted as well? I wouldn't
> like that :-)
>
> Thanks in advance, regards.
>
> --
> Uka P.
>
>

Not a big deal Ron but you don't need to array enter this

--

Regards,

Peo Sjoblom


"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
> Try this:
>
> If your values are in Cells A1:B10
>
> C1: =SUMPRODUCT(--COUNTIF(A1:A10,B1:B10))
> Note: Commit that array formula by holding down [Ctrl]+[Shift] when you
> press [Enter]
>
> Returns the number of common elements from Col_A and Col_B
>
> Note: There can be no repeats withing the same column.
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
>
> "Uka P." wrote:
>
> > Hi there :-)
> >
> >
> > I have two columns with numeric data [but the kind of data shouldn't
> > matter, I suppose]. They're of _different_ lenght, but there are empty
> > cells below both of them.
> > I want to compare the columns and get the _number_ [amount] of mutual
> > elements as a result.
> >
> > Ie.: if A={2, 3, 5, 7, 9, 23, 25, 46}, and
> > B={1, 3, 4, 6, 7, 8, 9, 46}
> > than Result=4
> >
> >
> >
> > If I defined the columns to have the same lenght [including some of the
> > empty cells below] would the empty cells be counted as well? I wouldn't
> > like that :-)
> >
> > Thanks in advance, regards.
> >
> > --
> > Uka P.
> >
> >

Thanks Peo...I appreciate the feedback.
Sometimes I forget to test if CSE is not necessary.

***********
Regards,
Ron


"Peo Sjoblom" wrote:

> Not a big deal Ron but you don't need to array enter this
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
> "Ron Coderre" <[email protected]> wrote in message
> news:[email protected]...
> > Try this:
> >
> > If your values are in Cells A1:B10
> >
> > C1: =SUMPRODUCT(--COUNTIF(A1:A10,B1:B10))
> > Note: Commit that array formula by holding down [Ctrl]+[Shift] when you
> > press [Enter]
> >
> > Returns the number of common elements from Col_A and Col_B
> >
> > Note: There can be no repeats withing the same column.
> >
> > Does that help?
> >
> > ***********
> > Regards,
> > Ron
> >
> >
> > "Uka P." wrote:
> >
> > > Hi there :-)
> > >
> > >
> > > I have two columns with numeric data [but the kind of data shouldn't
> > > matter, I suppose]. They're of _different_ lenght, but there are empty
> > > cells below both of them.
> > > I want to compare the columns and get the _number_ [amount] of mutual
> > > elements as a result.
> > >
> > > Ie.: if A={2, 3, 5, 7, 9, 23, 25, 46}, and
> > > B={1, 3, 4, 6, 7, 8, 9, 46}
> > > than Result=4
> > >
> > >
> > >
> > > If I defined the columns to have the same lenght [including some of the
> > > empty cells below] would the empty cells be counted as well? I wouldn't
> > > like that :-)
> > >
> > > Thanks in advance, regards.
> > >
> > > --
> > > Uka P.
> > >
> > >
>
>
>

Ron Coderre wrote:

>Try this:
>
>If your values are in Cells A1:B10
>
>C1: =SUMPRODUCT(--COUNTIF(A1:A10,B1:B10))
>
>
>Does that help?
>



Nope :-(
My excel doesn't get it, it says the formula is wrong, because 'cauntif"
needs to have specified data and conditions.

Maybe the problem is with langauge - you wrote it in English, and I work
with Polish version of excel, so I have to translate the formula into
Polish. I know what "countif" is in Polish, but I can't figure out the
"sumproduct" - which I suppose is not the same as "sum"? Why don't you
paste here the article according to "sumproduct" form your English
excels' help, so I can match it to any of my "sums"?

I also don't get the usage of "--". Am I supposed to put it exactly as
you wrote, exchanging only English "countif" to Polish "licz.jezeli"? Or
is it a symbol standing for something different, which I didn't get?

Thanks for your help. :-)

regards,

--
Uka P.

From English XL Help:

> SUMPRODUCT
> Multiplies corresponding components in the given arrays, and returns the sum
> of those products.
> Syntax
> SUMPRODUCT(array1,array2,array3, ...)
> Array1, array2, array3, ... are 2 to 30 arrays whose components you want to
> multiply and then add.

for help on the --, see

http://www.mcgimpsey.com/excel/doubleneg.html

In article <[email protected]>, "Uka P." <[email protected]>
wrote:

> I also don't get the usage of "--". Am I supposed to put it exactly as
> you wrote, exchanging only English "countif" to Polish "licz.jezeli"? Or
> is it a symbol standing for something different, which I didn't get?

Try this:
First Issue:
Lookup SUM in Excel Help.
Under the SUM topic there should be a link to "See Also".
Click that....On my version, the following are listed:
AVERAGE
COUNTA
Math and Trigonometry functions
PRODUCT
SUMPRODUCT

Hopefully, your version will include the Polish version of SUMPRODUCT.

Second Issue:
Many functions return TRUE or FALSE. SUMPRODUCT will not recongnize those as
numbers and cannot add/multiply them. By prepending -- to the function we
force Excel to convert TRUE/FALSE to 1 and 0, respectively.
The first - converts TRUE to -1.
The 2nd - converts the -1 to 1.

However, in the case of COUNTIF, that function actually DOES return a
numeric value so the -- was unnecessary. I'm sure I just included it out of
habit.

Hopefully, that helps solve your problem.

***********
Regards,
Ron


"Uka P." wrote:

>
>
> Ron Coderre wrote:
>
> >Try this:
> >
> >If your values are in Cells A1:B10
> >
> >C1: =SUMPRODUCT(--COUNTIF(A1:A10,B1:B10))
> >
> >
> >Does that help?
> >
>
>
>
> Nope :-(
> My excel doesn't get it, it says the formula is wrong, because 'cauntif"
> needs to have specified data and conditions.
>
> Maybe the problem is with langauge - you wrote it in English, and I work
> with Polish version of excel, so I have to translate the formula into
> Polish. I know what "countif" is in Polish, but I can't figure out the
> "sumproduct" - which I suppose is not the same as "sum"? Why don't you
> paste here the article according to "sumproduct" form your English
> excels' help, so I can match it to any of my "sums"?
>
> I also don't get the usage of "--". Am I supposed to put it exactly as
> you wrote, exchanging only English "countif" to Polish "licz.jezeli"? Or
> is it a symbol standing for something different, which I didn't get?
>
> Thanks for your help. :-)
>
> regards,
>
> --
> Uka P.
>
>

Ron Coderre wrote:

>Try this: [...]
>
>Hopefully, your version will include the Polish version of SUMPRODUCT.
>

No, it wouldn't. But I found it anyway according to the definition that
JE McGimpsey pasted. :-)
It's SUMA.ILOCZYNOW [thought you might need it in some time ;-D]


>Hopefully, that helps solve your problem.
>


YES!!!! YES!!! And thankyouthankyouthankyou! :-))



I'm trying to subvert the probability theory and hit the Jackpot in
Polish Lotto by the way [both unsuccesfully so far ;-))))))], so this
formula is of VITAL meaning to me :-))))))


Thank you once again for your help.



BTW - I thought I knew XL! One learns throughout all his life... *sigh*

Regards,

--
Uka P.