Forum Discussion
Finding the next value in list - nothing to match off of
Hi Everyone,
Bit of a tricky one but I'll do my best to explain my issue.
I have a list starting at B1 to B26.
B1 is "A-A", B2 is "A-B", B3 is "A-C" etc. all the way to "A-Z".
In A1 I want the first value, "A-A", and in A2 I want the next value, "A-B".
Is there a formula for this?
Basically I have a few columns of jumbled up and duplicated values ranging from A-A through to Z-Z.
These codes will set off an INDEX and MATCH to pull more data from another tab, I just need the first few values in alphabetical order.
Any help will be greatly appreciated.
7 Replies
I feel I'm missing a detail but I'll give this a shot.
Presuming the data looks like this with jumbled values in B:
You might create an XMATCH, an INDEX, or whichever lookup you need using some sequencing:
=LET(n,"A-"&CHAR(SEQUENCE(26,,65,1)),XMATCH(n,B1:B26))Patrick2788 Hi Patrick, thank you for trying to help, unfortunately the formula does not work when there are duplications. I'm trying to attach a sample as you have done but I cannot work out how to attach. Would you mind sending me an email on pauljbrag"@"gmail"dot"com and I'll mail you what I'm working with?
- are you looking for a rank order of the text strings in col B or those values sorted or something else?
for example could you use =SORT(B1:B26)
if it is a ranking you need I have a couple questions like will it only change the last character or could both change?
- What about =B1 then drag down the formula till A26?
- I wish it was that simple. My actual list is jumbled and not in order with duplicates.
