finding common numbers in large lists

Hello, I have two large lists of numbers (on two seperate worksheets).
  By large, I mean that one list is 1000+ numbers, and the second list
is 3000+ numbers (each number is in its own cell).   The numbers are
of the form:
xxxx.xxx (that is, they are precise up to three places after the
decimal)

1) Is there some quick way that I can find the numbers that are common
to both lists?

2) Is there a way I can find the numbers in common to both lists using
only the whole number portion (diregarding what comes after the
decimal of each number)?

Thanks!
0
juicymixx (2)
11/11/2004 7:11:41 PM
excel 39879 articles. 2 followers. Follow

1 Replies
848 Views

Similar Articles

[PageSpeed] 22

Hi
see:
http://www.cpearson.com/excel/duplicat.htm#ExtractingCommon

--
Regards
Frank Kabel
Frankfurt, Germany

"Jenn" <juicymixx@mailinator.com> schrieb im Newsbeitrag
news:806a4f35.0411111111.5507b7ba@posting.google.com...
> Hello, I have two large lists of numbers (on two seperate
worksheets).
>   By large, I mean that one list is 1000+ numbers, and the second
list
> is 3000+ numbers (each number is in its own cell).   The numbers are
> of the form:
> xxxx.xxx (that is, they are precise up to three places after the
> decimal)
>
> 1) Is there some quick way that I can find the numbers that are
common
> to both lists?
>
> 2) Is there a way I can find the numbers in common to both lists
using
> only the whole number portion (diregarding what comes after the
> decimal of each number)?
>
> Thanks!

0
frank.kabel (11126)
11/11/2004 7:42:02 PM
Reply:

Similar Artilces:

Find feature does not work with HTML messages (Content-type: text/html)
I have Outlook 2000 SP-3 running under Windows ME. When using the Find feature to locate all e-mails with a given word in the message, no items are found if the e-mails are of "text/html" content-type. The Find feature appears to work for any messages that have URLs embedded and are "multipart/alternative" or "text/plain" content-type. I noticed a similar post several months ago (see http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=%23qSBYIssDHA.2224%40TK2MSFTNGP09.phx.gbl). Is there a work-around for this problem, or should this be reported t...

Global Address List #23
I have several disabled user accounts, and would like to remove their email addresses from the global address list. In other words, when users send an email and are selecting the recipient, I do not want my disabled users mail addresses to be seen. I know that you can select 'hide address from lists' under advanced features, but that only hides the address from the public folder. Does anyone know how I could do this? ...

Collapsed list view report control
I'm try to set up an EXCEL like window, which apparently one can do with a REPORT control. I actually get it work, but all the columns are scrunched to the left when the window comes up, forcing the user to move the partions. I'm using Visual C++ 7.0. Here' the code. Thanks, Ihor P.S. Is there an easier way of inserting a line of data?? ----------------------------------------------------------------------- CRect rect(16 ,60,450 ,250); m_pendlistctrl.Create(WS_VISIBLE|WS_CHILD|LVS_REPORT,rect, this,IDD_WIRE_DIALOG); //m_pendlistctrl is a CListCtrl m_...

Income in Payee List?
Why does my income (paychecks, child support received, etc.) show up in my Payee list. Are they supposed to? Thanks -- Lori In microsoft.public.money, Lori wrote: >Why does my income (paychecks, child support received, etc.) show up in my >Payee list. Are they supposed to? Thanks It's up to you. If you want to rename or move those "payees" to something more meaningful to you, there are buttons to make those choices. Just select the odd payee, and choose Rename, Move or Delete. Move or Delete will let you combine payees. I'm not sure I understand what you mean by...

List Validation
I was wondering if there was anyway you can have a drop down list in one workbook that comes from a different workbook. Example: I have a workbook saved with a list of names and I want to have that list of names come up in a form that I have saved in another workbook. http://www.contextures.com/xlDataVal05.html note that the other workbook needs to be open -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "Renee" <...

how do I increase drop list font and box size I created
Font is small and you have to scroll through list due to box size If the dropdown was created using Data Validation.............Font size is set at 8pt Dropdown box height cannot be changed. For workarounds like using a ComboBox and using Zoom see Debra Dalgleish's site on DV Lists. http://www.contextures.on.ca/tiptech.html Gord Dibben MS Excel MVP On Fri, 4 Apr 2008 11:36:02 -0700, cdietz1 <cdietz1@discussions.microsoft.com> wrote: >Font is small and you have to scroll through list due to box size ...

Max Limit for Values in List Boxes and Combo Boxes in Outlook forms
Folks, a user is setting up a form. She wants to have several combo boxes and list boxes with between 75 and 300 (yes, 300 -- that's not a typo!) possible values in the combo box or list box. Before she starts work on this, she would like to know if there is a limit on the number of possible values for a combo box or a multi-select list box. Is there a limit? Is it defined by the number of characters in the values box, or the number of actual values? What is the limit? Any help here would be appreciated. Thanks. David consulttech2004@hotmail.com ...

Stop advancing in form with the "next" arrow at end of "list"
I have an editable form for users where they can query by date, etc. However, the next entry arrow allows them to advance beyond the result. For example, if their query resulted in 1 of 4, they can still click to 5, 6, and so on which only adds blank lines to the database table. How do I set this to stop at the last entry of their original query? You can cancel adding new records like this: Private Sub Form_BeforeInsert(Cancel As Integer) Cancel = True End Sub Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Duchess" <Duchess@discuss...

Odd Looking Number
Hi I am using a spreadsheet in Excel 2002 on Windows XP, for some reason the account numbers (which someone input, along with addresses) have a little green mark in the corner of the cell. I thought it was to show that there is a comment, but it is not. When you click on the cell, a little warning sign shows, saying ERROR - number in this cell formatted as text or preceded by an apostrophe. When I click on the arrow by the warning sign, it says Number stored as text Convert to number Ignore error Edit in formula bar Error checking options Show formula in audinting toolbar Now I l...

Numbers turn red when equals to 0
Good day, Im monitoring our stock inventory using excel, i just want to know if I can make a certain cell turn red if my inventory is >5 or equal to zero.? Thanks -- shiela21cute ------------------------------------------------------------------------ shiela21cute's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33169 View this thread: http://www.excelforum.com/showthread.php?threadid=529926 Check out Conditional formatting Ed -- EdMac ------------------------------------------------------------------------ EdMac's Profile: http://www.excelforum...

Setting random number range based on query, not table
I have a "quiz" form that randomly pulls up questions from my table. It works fine, and I don't mind that it repeats questions. This allows me to sit and review for as long as I want. The problem is, that I want to use criteria in a query to limit the available questions. If I base this form on a query and limit the number of questions, the code I am using is still setting the maximum number as the number of records in my table. In the past, I had a similar database (which I have lost) that would open the form, set the selector to the last record available to the form,...

Serial number error in sales order entry window (2nd post)
I'm reposting this because there was no response to my first post. I'm sure I'm not the only one that has had this problem. I noticed that one of my coworkers invoiced and added (overided) a serial number into the Great Plains instead of allocating one of the existing ones. How do I return this serial number w/out affecting my inventory? I need to: 1. Return the item w/the wrong serial number. 2. Reinvoice the correct serial number. 3. NOT HAVE THE WRONG SERIAL NUMBER STUCK IN MY INVENTORY. How do I do this? Hi Paoakalani Try looking at Serial Number edit, by Blue Moon...

How the heck do I find tables, views, forms etc... in Access 2007
Can Access 2007 navigation be any more convoluted? I've been spending close to an hour trying to find a single table, query form, etc... now that the navigation has been changed. Is there a way to get the old style back? It does take some time - more than an hour :-) - to become familiar with the new interface in A2007 (NavPane, ribbon, ...) Suggestions: - The title bar at the top of the Nav Pane includes a tick box for: All Access Objects - Set Category to: Object Type - The categories (Tables, Queries, ...) collapse. - Show and use the Search Bar. It filters objects as...

list.remove() crash.
Hi: I have a short code below which polulats a list with 3 strings and then try to remove them by calling list.remove(). but the program crashes at the 2nd run in the loop (as marked below). Could you see anything I'm doing wrong? Thanks for your input. Polaris ------------------------------------------------------------- #include <list> typedef std::list <char*>::iterator LI; int main (int argc, char* argv[]) { char* array[] = { { "Test1" }, { "Test2" }, { "Test3" } }; ...

Intercompany AP Edit List
I entered an Intercompany AP invoice. I distributed as follows: Originating Company AP $300 Originating Company Expense $150 Destination Company Expense $150 When I print the Payables Transaction Edit List, it shows a completely different account for the Destination Company entry. It is an account in my Originating Company that has nothing to do with the invoice or the vendor. But when I post it, the General Ledger posting journal looks good. Can someone please let me know if this is a known flaw in GP 9.0 or have I set something up wrong? Thanks for any help! ...

create price list from excel sheet
I have been using Excel 2003 to manage a list of approximately 200 items. This is a very simple list comprising of four or five fields one of which is the price. I prefer to use Excel to manage this data because it then becomes very simple for me to use formulas in order to calculate prices after changes or additions to the items. I would like to produce a simple A5 flyer listing all the items together with their prices. What will be the best way to do this? I have explored the possibility of exporting the Excel sheet into something like Microsoft publisher but I am looking for a fa...

when I type a long number it shows up as smaller number and +
when converting from xls to csv format, some of my longer mortgage numbers get condensed into a smaller number with a letter, a plus sign and another number. example: 100020013120 turns into 1.0002E+11. Any way to get rid of this? Thanks, Yes, just specify the format that you want. Format Cells...>Number>0 decimal places Regards, Fred "SandyC" <SandyC@discussions.microsoft.com> wrote in message news:FA52E426-360E-4BE5-B63B-1209E673CA65@microsoft.com... > when converting from xls to csv format, some of my longer mortgage numbers > get conde...

Excel: how to merge data from 2 workbooks with 1 col. in common?
I've got two separate workbooks. Each workbook contains unique columns except for one, which is a "key" column, or an id field. Example: I have a client # column in both workbooks. In the first workbook, I also have first name and last name columns. In the second column, I have phone number and address columns. How do I merge the two workbooks into one so that for each client # I have their first name, last name, phone number, and address. Well, I think I'd start by getting all the data into one workbook. With both workbooks open, select the worksheet with the phone n...

Find touched files since a specific timestamp
Hi, Given the name of a folder, what's the best way to find the list of files (include files in sub-directories) that were changed since a specific timestamp? I tried to add the list of files to a CStringArray and then sort them. But it took a while to process some 1000 files. There must be a better way. Please advise. Best, Isaac http://www.globalrendering.com Which sorting method have you used? qsort does 1000 sorting in several ms. Anyway, if you want to get files in a date range, just check date before adding to array, use CFileFind. -- Igor Green, Grig Software. www.gri...

Rounding up price lists
Can you round to the nearest Dollar? Yes, if the Price Method is one that caculates the selling price (such as % of List, markup from list, etc). Click on Options, select the Round Option and set to multiple of and enter 1.00 in the Round Amount field. -- Richard L. Whaley Author / Consultant / MVP Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "tstrop" wrote: > Can you round to the nearest Dollar? ...

Data List #2
In relation to an earlier question, I am looking for a way of creatin box which displays the contents of a list, which when clicked on can b copied to a current cell, but other items added to this list. For example, in one spreadsheet a list of; John Paul Peter In another spreadsheet, a view of this, so that the values can easil be copied over, but also the abaility to add another name. Something like.. [Players] | John | | Paul | | Peter | ... [ADD NEW] Any ideas? Many thanks, Mic -- Message posted from http://www.ExcelForum.com Mich Lookup data validation. It is very straigh...

Finding Attachments
Does Outlook 2002 Advanced Find provide a method for locating an attachment in an e-mail??? regards, Adrian Yes, you can search for all items with attachments, but you cannot search for a) specific attachments or b) attachment contents. To perform this type of search, simply right click on any Outlook folder, choose Advanced Find. In the Advance Find window, select Other Choices, then select "Only items with: one or more attachments". -- Hope that helps!! Thank you! Sincerely, Chris Lineback, MCSE Microsoft Enterprise Messaging Support - Please do not send email directly...

How do I find duplicate rows in a list in Excel, and not delete it
I have a long list of data in Excel that is 3 columns wide. I need to find and save only the duplicate rows but don't want to delete them. Instead, I could delete the unique rows and keep the duplicate rows. In the customer assistance, I found out how to delete duplicate rows and save the unique rows, but this is exactly opposite of what I want to do. This is one option: =COUNTIF($A$4:$A$18,A4)>1 copy down, and use a Autofilter to find all True HTH Ola Sandstr´┐Żm Picture encl.: http://www.excelforum.com/attachment.php?attachmentid=3498&stc=1 +---------------------------...

Rotating page numbers
I have a document with both page layouts (landscape and portrait) however the page numbers obviously will be pointing in the direction of the associated layout does anyone know how to be consistent in this? ...

Find Blank Cells
I wish to replace the blank cells in a large database with a zero (0). I cannot figure out how to find a blank cell using the Find and Replace option under the Edit menu. Can anyone show me the way? Hi Peter, I always use CTRL+H to bring up the find and replace menu, leave the fine part empty, and put in what you want to replace it with....however, I do notice you say in a "database"? Do you mean in Access?? Dave M. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.Exc...