If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Searcing multiple numbers
I want to build or import a list of numbers, each with
specific information in a description field, then search that list with another list of numbers.....and I want it to be one step. Scenario: I have a list of 10,000 numbers, each with specific information. I have another list of 200 numbers. I need to know what numbers in the list of 200 are on the master list of 10,000 and the specific information that follows each of those numbers that makes a match. Can someone point me in the right direction? |
#2
|
|||
|
|||
Hi Ken
if you use the VLOOKUP statement it will tell you whether the number is in the master list and the associated information for that number e.g. Main list sheet2 A1:B10000 - lst column numbers, 2nd column description field other list to match with this on sheet1 A1:A200 in b1 of sheet 1 type =IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$B$10000,2,0)),"No t found",VLOOKUP(A1,Sheet2!$A$1:$B$10000,2,0)) this will return the words "not found" if the number isn't in the master list and will return the information from column B of the list if the information is found. Cheers JulieD "Ken" wrote in message ... I want to build or import a list of numbers, each with specific information in a description field, then search that list with another list of numbers.....and I want it to be one step. Scenario: I have a list of 10,000 numbers, each with specific information. I have another list of 200 numbers. I need to know what numbers in the list of 200 are on the master list of 10,000 and the specific information that follows each of those numbers that makes a match. Can someone point me in the right direction? |
#3
|
|||
|
|||
Another option to play with ..
Assume the 10,000 numbers, with associated data are in Sheet1, cols A to C, from row1 down (The 10K numbers are in A1:A10000 and are assumed unique) 100 Data1 Data11 101 Data2 Data12 102 Data3 Data13 103 Data4 Data14 104 Data5 Data15 105 Data6 Data16 106 Data7 Data17 107 Data8 Data18 108 Data9 Data19 109 Data10 Data2 etc In Sheet2 ----------- Say the other list of 200 numbers is in A1:A200 Select B1:B200 (i.e. a range to match that in col A) Put in the formula bar: =IF(ISERROR(SMALL(IF(COUNTIF(Sheet1!A1:A10000,A1:A 200)=1,A1:A200),ROW())),"" ,SMALL(IF(COUNTIF(Sheet1!A1:A10000,A1:A200)=1,A1:A 200),ROW())) Array-enter the formula with CTRL+SHIFT+ENTER instead of just pressing ENTER Col B will list the numbers in col A which are found / match those within col A of Sheet1 (in ascending order) Put in C1: =IF(B1="","",OFFSET(Sheet1!$A$1,MATCH($B1,Sheet1!$ A:$A,0)-1,COLUMN(A1))) Copy C1 across to D1, fill down to D200 Cols C and D will extract the associated data from Sheet1 corresponding to the numbers extracted in col B Adapt / extend to suit -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik atyahoodotcom --- "Ken" wrote in message ... I want to build or import a list of numbers, each with specific information in a description field, then search that list with another list of numbers.....and I want it to be one step. Scenario: I have a list of 10,000 numbers, each with specific information. I have another list of 200 numbers. I need to know what numbers in the list of 200 are on the master list of 10,000 and the specific information that follows each of those numbers that makes a match. Can someone point me in the right direction? |
#4
|
|||
|
|||
Max,
Thanks for the info on posting. I will likely have multiple columns of specific info for each number, so thanks for bringing that up and addressing that scenario! Ken |
#5
|
|||
|
|||
You're welcome !
But do hang around and monitor your original post awhile There could be invaluable insights from others which I'm sure you wouldn't want to miss out on g -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik atyahoodotcom --- "Ken" wrote in message ... Max, Thanks for the info on posting. I will likely have multiple columns of specific info for each number, so thanks for bringing that up and addressing that scenario! Ken |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formatting Numbers | Allison | Worksheet Functions | 3 | March 31st, 2004 09:55 PM |
How to get excel to find the numbers of times 2 numbers appear | Gary Hunt | Worksheet Functions | 2 | March 21st, 2004 10:32 PM |
Opening multiple workbooks in multiple windows | Dave Peterson | Worksheet Functions | 0 | February 22nd, 2004 04:20 PM |
Random Numbers from a List of Numbers | [email protected] | Worksheet Functions | 3 | February 13th, 2004 01:27 AM |
letter and numbers substitute | Paul | Worksheet Functions | 4 | November 12th, 2003 07:44 AM |