Hi

I'm kind of stumped how to fix this issue without going through thousands of fields by hand and updating them, so I hope someone here can help me! On to the issue.

I've been using a spreadsheet with >5000 rows, all which have a unique identifier (C-xxxxx-x-S2, where x is a number. Example, C-72975-1-S2, C-72975-1-2-S2 etc.. the 1, 2, 3-S2 refers to a unit.

Each row also has a non-unique identifier known as a job # which is in the format of Bxxx, where x is a letter or number. Always starts with B.

So the B # is the house, C# refers to the individual unit/apartment.

I did a lookup to several other spreadsheets in order to fill in common data automatically. That worked fine and everything is correct, except for the unique identifier, which I overwrote because I mistakenly looked up the B # instead of the C #. So now most of my C#'s have the same value. I no longer have a unique identifier to look up from.

An example:

C-70851-1-S2
C-70851-1-S2
C-70851-1-S2
C-70851-1-S2
C-70851-1-S2
C-70851-1-S2
C-70851-1-S2
C-70851-1-S2
C-70851-1-S2
C-70851-1-S2
C-70851-1-S2
C-70851-1-S2
C-70851-1-S2
C-70851-1-S2
C-70851-1-S2
C-70851-1-S2
C-70851-1-S2
C-71441-10-S2
C-71441-10-S2
C-71441-10-S2
C-71441-10-S2
C-71441-10-S2
C-71441-10-S2
C-71441-10-S2
C-71441-10-S2
C-71441-10-S2
C-71441-10-S2
C-71441-10-S2
C-71441-10-S2

What I've tried:

- Get the -xx-S2 value in a cell by using right(), then eliminating the dashes and S2 so it gives me the number. Theoretically if I can make a formula to create the series I can just use A2&B2 (example cell) to add them back together. Unfortunately I don't know how to do that. From what I've tried it seems like I have to make a circular reference to get the result I want. I'm not familiar with VB so I'm limited to excel formulas. If anyone has a VB solution I know how to implement them, at least though.

- Grab a sheet which was (supposedly) a clone of my main sheet and still had the correct C#'s. It does, but as that's the unique identifier I can't do a lookup. I tried sorting them side by side and just doing a copy paste job but as I've discovered the lists aren't identical. So to do that I'll have to go through it one by one and make them match before I can copy it across.

If anyone has any advice I would be very grateful because this is quite embarassing, to be honest.

Cheers