Rick,

At the danger of sounding over-effusive, that is utterly, utterly fabulous.

It is 7:15 pm on a Friday night in England, and now you've solved my
problem, I'm free to go home!

Thank you.

"Rick Rothstein (MVP - VB)" wrote:

Okay, I think I see what you are looking to do. Put this formula in B6 on
Sheet2 and copy down...

=INDEX(Sheet1!A6:A2750,MATCH("*-"&A6,Sheet1!A6:A2750,0))

Did that do what you wanted?

Rick


"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
Sorry - those won't work.

Let's say we have:

Sheet 1

A6:A2750 contain the new "tag", in the format
Company-Office-ManagerID-Staff
ID

Sheet 2

A6:A2600 contain the old staff IDs

I would want to return on sheet 2, in B6:B2600 any match from sheet 1

To make things harder, there could be up to six hyphens as these sometimes
appear in office names or company names. (Yes, it's rubbish as a
delimiter,
I know)

Thanks again.

"mr tom" wrote:

Thanks Rick.

I want a formula next to the old list which will return the new
identifier.
This will allow me to keep the old data despite moving to a newer
reproting
system.

These formulas look good. I'll play about with them - hopefully one will
do
the job.

Cheers!

Tom.

"Rick Rothstein (MVP - VB)" wrote:

So, I need a formula which I can put in next to the old list (123a
etc)
which looks for the staff id in the list of new unique tags
(wm-bath-jsmith-123a would be a match).

It should only match values after the final hyphen.

I'm not exactly sure what you are looking for here (Formula next to
your old
list? That will return what?). Anyway, maybe you can make use of one of
these...

Get staff ID from new list entry
(assuming number of dashes can vary)
**********************************
=MID(A1,FIND("|",SUBSTITUTE(A1,"-","|",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,255)
where A1 has the new staff ID

Get staff ID if there are always 3 dashes
**********************************
=MID(A1,FIND("|",SUBSTITUTE(A1,"-","|",3))+1,255)
where A1 has the new staff ID

See if old staff ID is in new staff ID
**********************************
=NOT(ISERROR(SEARCH(C1,A1)))
where C1 has old staff ID, A1 has new staff ID


Rick