Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Les
 
Posts: n/a
Default 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   Report Post  
Max
 
Posts: n/a
Default

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   Report Post  
Les
 
Posts: n/a
Default

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   Report Post  
Max
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Printing Problem kimmyrt New Users to Excel 3 January 26th 05 09:55 PM
Row Autofit problem Excel 2003 Matthias Klaey Excel Discussion (Misc queries) 0 January 19th 05 06:33 PM
Problem with date base units for x axis Peter Carr Charts and Charting in Excel 1 December 15th 04 10:11 AM
Paper Tray selection Problem, Michael Hoffmann Excel Discussion (Misc queries) 4 December 3rd 04 10:08 PM
matching problem using VBA mango Excel Worksheet Functions 1 November 1st 04 02:51 PM


All times are GMT +1. The time now is 11:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"