Excel formula to calculate ID #
I'm trying to automate creating an ID # via formulae based on data in other Table columns. The attachment shows the ID # that was manually created and the formula version I've created. I have created several helper columns (maybe too many?) from which to draw.
The main problem is I cannot come up with a formula to have the second character change based on the unique value in the Sign column. There should only be 4 different characters (A, B, C, D) whereas my current formula goes through the alphabet based on the total count in another column. (This will be more apparent in the attachment.)
Is there a formula I can create to match the actual ID that was manually created?
Many thanks, Experts!
EXEX-Test.xlsx
The main problem is I cannot come up with a formula to have the second character change based on the unique value in the Sign column. There should only be 4 different characters (A, B, C, D) whereas my current formula goes through the alphabet based on the total count in another column. (This will be more apparent in the attachment.)
Is there a formula I can create to match the actual ID that was manually created?
Many thanks, Experts!
EXEX-Test.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Simulog--Thanks so much! That worked perfectly. Really appreciate what you've done here.
Shums--Thanks, too, very much for offering a solution. It was close but repeated the numbers in a way that didn't work as intended.
Shums--Thanks, too, very much for offering a solution. It was close but repeated the numbers in a way that didn't work as intended.
ASKER
Thank you! Great job...simple solution. Really appreciate the assistance!
xlvr_ID-Calc.xlsx