Home |
Search |
Today's Posts |
#1
|
|||
|
|||
A matching problem
I'd like to be able to match a name (text) from column B
with an assigned number from a number group range from Column A (that's also text the way it is entered). What is my best way? Pivot or formula or something else? My problem is that: in Column A I have booklets numbered: 1-50 51-100 101-150 151-200 201-250 251-300 in Column B I have: John Bill Ralph Ted Betty Sara But then whoever finishes a booklet first gets 301-350 and so on until the list is completed at the booklet #2951- 3000 so the name list start order mixes up at this point. My idea is to type in a real number from 1 to 3000 in D1 and get the name of who issued the number in E1. I could manipulate column A if need be. Thanks for any ideas! |
#2
|
|||
|
|||
One try ..
With the set-up as described, and assuming the listings in cols A and B will be similarly extended down for subsequent booklet numbers (from 301 till 3000 .. ) Put in the formula bar for E1: =INDEX(B1:B100,MATCH(D1,--LEFT(A1:A100,SEARCH("-",A1:A100)-1),1)) Array-enter the formula with CTRL+SHIFT+ENTER instead of just pressing ENTER -- Rgds Max xl 97 -- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom --- "Les" wrote in message ... I'd like to be able to match a name (text) from column B with an assigned number from a number group range from Column A (that's also text the way it is entered). What is my best way? Pivot or formula or something else? My problem is that: in Column A I have booklets numbered: 1-50 51-100 101-150 151-200 201-250 251-300 in Column B I have: John Bill Ralph Ted Betty Sara But then whoever finishes a booklet first gets 301-350 and so on until the list is completed at the booklet #2951- 3000 so the name list start order mixes up at this point. My idea is to type in a real number from 1 to 3000 in D1 and get the name of who issued the number in E1. I could manipulate column A if need be. Thanks for any ideas! |
#3
|
|||
|
|||
You have saved my day! Thanks
-----Original Message----- One try .. With the set-up as described, and assuming the listings=20 in cols A and B will be similarly extended down for subsequent booklet=20 numbers (from 301 till 3000 .. ) Put in the formula bar for E1: =3DINDEX(B1:B100,MATCH(D1,--LEFT(A1:A100,SEARCH("- ",A1:A100)-1),1)) Array-enter the formula with CTRL+SHIFT+ENTER instead of just pressing ENTER --=20 Rgds Max xl 97 -- GMT+8, 1=B0 22' N 103=B0 45' E xdemechanik <atyahoo<dotcom --- "Les" wrote in=20 message ... I'd like to be able to match a name (text) from column B with an assigned number from a number group range from Column A (that's also text the way it is entered). What=20 is my best way? Pivot or formula or something else? My problem is that: in Column A I have booklets numbered: 1-50 51-100 101-150 151-200 201-250 251-300 in Column B I have: John Bill Ralph Ted Betty Sara But then whoever finishes a booklet first gets 301-350=20 and so on until the list is completed at the booklet #2951- 3000 so the name list start order mixes up at this=20 point. My idea is to type in a real number from 1 to 3000 in D1 and get the name of who issued the number in E1. I could manipulate column A if need be. Thanks for any ideas! . |
#4
|
|||
|
|||
Glad to hear that !
You're welcome .. -- Rgds Max xl 97 -- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom --- "Les" wrote in message ... You have saved my day! Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing Problem | New Users to Excel | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) | |||
Problem with date base units for x axis | Charts and Charting in Excel | |||
Paper Tray selection Problem, | Excel Discussion (Misc queries) | |||
matching problem using VBA | Excel Worksheet Functions |