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!

"tim m" wrote:

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" wrote:

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.