#### 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 :-)

--
Uka P.

```
 0
ula.i (3)
12/14/2005 10:14:28 AM
excel.misc 78881 articles. 5 followers.

8 Replies
477 Views

Similar Articles

[PageSpeed] 36

```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." <ula.i@pspgroup.pl> wrote in message
news:439FF084.5020109@pspgroup.pl...
> 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 :-)
>
>
> --
> Uka P.
>

```
 0
demechanik (4694)
12/14/2005 1:49:11 PM
```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 :-)
>
>
> --
> Uka P.
>
>
```
 0
12/14/2005 2:18:02 PM
```Not a big deal Ron but you don't need to array enter this

--

Regards,

Peo Sjoblom

"Ron Coderre" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
news:04F9AE8D-5D2C-4A35-81ED-1463FF54EB73@microsoft.com...
> 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.
> >
> >

```
 0
terre081 (3244)
12/14/2005 2:22:29 PM
```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" <ronSKIPTHIScoderre@bigfoot.com> wrote in message
> news:04F9AE8D-5D2C-4A35-81ED-1463FF54EB73@microsoft.com...
> > 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.
> > >
> > >
>
>
>
```
 0
12/14/2005 3:06:05 PM
```
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?

regards,

--
Uka P.

```
 0
ula.i (3)
12/15/2005 9:34:49 AM
```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

for help on the --, see

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

In article <43A138B9.9000905@pspgroup.pl>, "Uka P." <ula.i@pspgroup.pl>
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?
```
 0
jemcgimpsey (6723)
12/15/2005 1:43:43 PM
```Try this:
First Issue:
Lookup SUM in Excel Help.
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.
>
>
```
 0
12/15/2005 1:57:06 PM
```
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.

```
 0
ula.i (3)
12/15/2005 2:51:22 PM

Similar Artilces:

Pivot table or Macro?
Hi! I was asked to manage space in many warehouses. I have was wondering if I was better of using Pivot tables or Excel Macros. Someone must of gone through the same situation. I have to say that I am more familiar with coding macro programs but I am open about learning more on Pivot table! (or both). Any suggestions regarding this matter? Thanks=92 ahead. Hi, You really need to explain what "manage space" entails. This just really isn't enough info to supply a meaningful answer. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Bobby" wro...

Deselecting records in pivot table automatically
I have a file with over 51,000 rows. I need to pivot the data to get at 3 different conclusions. I can pivot, but I need to further filter the pivot and I'm having problems figuring out how. My headings and some sample data: Dist_Nbr Customer_Acct_Nbr Customer_Number Bill-to_Company_Name Bill_Grp 7510 60000100 600001 SUMTER PACKAGING CORPORATION 00 4611 60000200 600002 A SMOOTH MOVE LLC 00 7170 60001200 600012 DOCUMATION INC 00 7170 60001201 600012 DOCUMATION INC 01 0684 60008300 600083 DOCUMATION INC 00 >From this I need 3 lists and I already pivoted the report. One list gives me...

compare
Hello everyone, I have been reading this group for a while, however I am quite a rookie in using of excel. I would appreciate if someone could help with this issue. I quite often have to compare two sheets (from different workbooks-files, but with the same sheet name). Calculations are thus updated time by time and I need to check where were the main differences. Cells contain both values and formulas. I have found through this newsgroup nice add-ins of Myrna Larson and Bill Manville, and Rob Bruce. However I would need the macros to highlight only significant differences (let's say fro...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA\$FileB TO corresponding row of COLA\$FileB Also merge data of corresponding row from COLB\$FileB TO correspondin...

How to Freeze the Pivot Table
Hello: Is it possible to Freeze the Pivot Table Data? I would like to give the table data only. The raw data will be delete to min. the size. Thanks a lot! Just select the whole table edit|copy edit|paste special|values I'd clean up all the stuff I needed to first. (removing subtotals is easier while it's still a pivottable.) And just a hint. Copy that worksheet with the pivottable first. Then when you screw up the formatting, you can start over with the already existing pivottable--you don't have to start from scratch. Alan Tang wrote: > > Hello: > > Is it p...

APA Style Table in Excel
Is there a way to left align digits in excel and still have decimal places in line? Example: Instead of: 0022.11 1122.01 Have: 22.11 1122.01 Thanks, Dan One way: Format/Cells/Number/Custom ???0.00 Left align the cells. In article <s0Gsb.21268\$8x2.8238628@newssrv26.news.prodigy.com>, "Dan" <dslaugh@yahoo.com> wrote: > Is there a way to left align digits in excel and still have decimal places > in line? > > Example: > > Instead of: > > 0022.11 > 1122.01 > > Have: > > 22.11 > 1122.01 > > Thanks, &g...

2 workbooks
I have got 2 workbooks with a list of names. I need to find out if a name appears in both books. One of the workbooks came from a different source and the other one is a report I ran from our database. I need to find out whether the workbook from out of the company has got any of our own names on it. I am using Microsoft Excel 97 and am fairly new at this so please be gentle. One way I did think was to combine the two workbooks into one and find the duplicates but thought there may be another way. Thank you. Hi "queen on", Assume that In book1 the names are in column A, s...

Notes table and IndirectDataInput / Date stamp in Memo field
I am trying out the previous suggestions for a solution regarding time/date stamp in a memo field. Both are very interesting. In the case of a separate Notes Table…how do I transfer the current memo field to the memo field record in the new Notes table? My database resides 900 records with memo fields… Thankful for advice! You will need a foreign key in the Notes table that tells you, for every row in the notes table, which is the row in the main table that the note belongs to. You need to extract both the notes and the PK of the record for each note. Use a query to do...

Comparing
I have two columns of numbers column A could be up to 3500 rows. Column B may be more or less than 3500 rows. What I'm trying to do is see if any number in columns "B" appears anywhere in Column "A", and if so we can just highlight it in both columns. Actually I would want to be able to sort by highlighted. so maybe add someway to sort by matched or unmatched. I hope this makes sense. In column C put... =IF(ISNA(VLOOKUP(\$A1,\$B:\$B,1,FALSE)),"Not in B","In B") In column D put... =IF(ISNA(VLOOKUP(\$B1,\$A:\$A,1,FALSE)),"Not in ...

locked table error
I am trying to open a report via a control button on a form. The form is based off of tblCustomers and the report is based off of a query which includes tblCustomers (I think the problem is due to that). The error I'm getting is "Run-time error '3211': The database engine could not lock 'tblCustomers' because it is already in use by another person or process." The db is local so I'm the only one using it right now. Any suggestions on how I can fix this problem? -- TIA have you tried closing that form before the report is open; as a sanity check? eve...

I need to compare to columns and indicate the matches in another
am wanting to compare 2 columns for exact matching. If there are two matching items I want to be able to say "A match" in a chosen cell for all the ones that match. Column C will be retrived in an random order... so how would I write the formula for that????? Example: A B C 45time 11tune A match 11tune 89time 47doog 43jkjkj A match 123ABC 123ABC If possible include how to highlight the ones that make as another option. Thanks Try something like this: For a value list in B1:B5 and a ...

Pivot Table report discarded in Excel 2003
When I open an Excel workbook in Excel 2003, I occasionally get the message: "Errors were detected in file 'path\Accounts.xls' "The following is a list of repairs: "PivotTable report 'PivotTable1' on '[Accounts.xls]Analysis ex VAT' was discarded due to integrity problems.W" I note there appears to be a fix for Excel XP (SP3) but I can't find a solution for Excel 2003. This problem did not occur with Excel 2000. A solution for Excel XP was to turn off auto-recover, but I don't want to do that. Any ideas? I don't understand how aut...

Hi, Can anyone advise on how I can compare data on 1 sheet with dat compared on another. Example attached. I want to be able to show that whenever the UK is shown (can appea multiple times) on this sheet it checks on sheet2 and enter th corresponding band value in this case for the UK (show in cells B6, B8 will show band A SHEET1 Ref Country Band 1 UK 2 Germany 3 UK 4 France 5 Italy SHEET2 Country Band UK A GERMANY B ITALY B FRANCE D SWEDEN D DENMARK E As always thanks for your help. Simo +------------------------------------------------------------------- |Filename: ...

Conditional Lookup Table?
Hello: In another database program I've used for years, you could set up a conditional lookup table without any programming. For example, if Field A has 2 choices, "Corporate" and Individual", then the lookup table for Field B would be based upon the choice in Field A without writing any code. In other words, if the user enters Corporate in Field A, then the corporate lookup table would be available for Field B. And if the user entered Individual in Field A, then the Individual lookup table would be available for Field B. Can I have Access select the correct lookup t...

I need a blank template, so that we can fill in the team names, and one that add's up the socres etc. ...

Compare and delete row
I need some help - I need to compare a cell, let's say A1, that wil contain a date. I need Excel to compare it to today's date, if it i over 60 days, I need it to go ahead and delete that row. If not, I nee it to leave alone. Thanks in advanc -- Message posted from http://www.ExcelForum.com Try this Sub test() If IsDate(Range("a1")) = True Then If Range("a1").Value > Date + 60 Then Range("a1").EntireRow.Delete End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "intheway >" <<intheway.176s7x@excelforum-nospam.c...

Pivot Table for Inventory Database
I have an inventory database that has separate fields for receipts and dispersements. How can I make a pivot table to show the part numbers, the receipts and dispersements and a running balance? tia Have a look at the PivotTable info on Debra Dalgleish's site. http://www.contextures.on.ca/tiptech.html Scroll down to "P" section. Gord Dibben Excel MVP On Tue, 22 Mar 2005 19:54:19 -0500, "ridgerunner" <&il_utrll@msn.com> wrote: >I have an inventory database that has separate fields for receipts and >dispersements. How can I make a pivot tabl...

comparing cell
i have a spreadsheet that contain a column of name (some the same and some not) and would like to place each name in a row acroos the top of the same spreadsheet. Smith Jones Brown Smith Smith Brown Smith Jones Jones Smith Brown Hi Stanley First insert a heading above your column of names. Then Data>Filter>Advanced Filter mark the source as your column of names. Choose copy to another location and choose a detination cell on the sheet. Select Unique values only. Having got a unique list of names, copy this list and Paste Special>Transpo...

Error message when using Pivot Table
I get an error message when I try to manipulate a very large Pivot Table. The message says "Excel cannot make this change because there are too many row or column items. Drag at least one row or column field off the PivotTable or to the page position. Alternatively, right click a field and then click Hide or Hide levels on the shortcut menu." I can't even figure out how to execute the remedies in the error message! I can manipulate other Pivot Tables from other ss just fine. And my colleagues have no trouble with this particular very large Pivot Table--just me. Is this a...

compare side by side
Using WindowsXP-Media Edition with Service Pack 2 and Office 2003. When I choose the "compare side by side" option, my two workbooks appear one above the other rather than side by side. Am I missing something?? >>Am I missing something?? I commented on that to MS during the beta test. They ignored me! -- Jim "BK" <nospam@nospam.com> wrote in message news:%233jhCdasHHA.1216@TK2MSFTNGP04.phx.gbl... | Using WindowsXP-Media Edition with Service Pack 2 and Office 2003. | | When I choose the "compare side by side" option, my two workbooks appear ...

Report Writer
I have GP 8.0 and am trying to add the Customer PO# to the report POP Purchase Order Blank Form. I found the knowledgebase article that tells you how to create a relationship between table Purchase Order Line TEMP and table SOP_POPLink and then with table Sales Transactions. But when I go in my Report Table Relationships window I can't find table SOP_POPLink when I click New with Purchase Order Line Temp highlighted. All I see is table Item Master when I believe there should be table SOP_POPLink as well. I have verified that the relationships are showing up within the respectiv...