Fun with text functions - search for text

[mr tom]

Hi,

Apologies in advance - text functions are not my strong point.

I'm introducing a unique identifier to an old data set.

Previously, total transactions would be tagged with a staff member's ID,
e.g. 123a.

Where staff move about, this occurs for each manager, operating company or
office they have been based in, and therefore this ID with a total next to it
can appear more than once.

I can find these cases easily enough with a sort and formula to bring out
duplicates. There will be few.

My problem is getting the new "tag" I have created against the old
transaction list (ignore the non-unique cases).

The "tag" is a concatenation of their company-office-manager's id-staff id,
and as such I've got a list that runs, e.g.

WM-Bath-jsmith-123a (where jsmith is the manager's id and 123a is the staff
id)
WM-Bath-jsmith-234a
....
....
and later...
WM-Bristol-gbrown-123a (where the staff member moved from Bath to Bristol
and changed manager in the process).

The old list is just the staff ids (with total transactions against them):
123a
234a
345a
456b
....


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.

As I say, don't worry about the duplicates - there are only a handful and I
can manage them easily enough - my main concern is the few thousand other
cases.

Thanks in advance (and virtual hugs & kisses etc) for any pointers /
solutions.

Cheers,

Tom.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[tim m]

Are the codes at the end of the new tag always 4 in length?

One way of doing it (though probably not the most efficient) would be to
insert a column just before your list of new tags and put this formula in the
1st cell and then copy down.

=RIGHT(E1,4)

This looks at the new tag (in E1 in the example) and then picks out the 4
right most characters thus you end up with 123a in a cell.

Then you could put a vlookup formula next to your column of old tags,
something like this.

=VLOOKUP(A1,D1:E3,2,FALSE)

This would look at the old tag (in A1), then look at the list of RIGHT
formulas and new tags (D1:E3), and return the 2nd column in that range (which
is the new tag), FALSE just means to find an exact match.

Once copied down you should have your list of old tags with the new tags
listed next to them via the vlookup.

 

[mr tom]

Apologies - that's really relevant info that I forgot to include.
Unfortunately, the final string can be any length (within reason) but will
never contain a hyphen.

Thanks!

 

[mr tom]

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.

 

[mr tom]

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.

 

[Rick Rothstein \(MVP - VB\)]

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]

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.