Index Array of Unique Values that Contain Substring

[Az566]

Hey guys,

A bit of a convoluted question.

I have a list of values

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]for127[/TD]
[/TR]
[TR]
[TD]for128[/TD]
[/TR]
[TR]
[TD]for13[/TD]
[/TR]
[TR]
[TD]for1[/TD]
[/TR]
[TR]
[TD]for56[/TD]
[/TR]
[TR]
[TD]aga567[/TD]
[/TR]
[TR]
[TD]aga2[/TD]
[/TR]
[TR]
[TD]aga67[/TD]
[/TR]
[TR]
[TD]for12
[/TD]
[/TR]
</tbody>[/TABLE]

I want to be able to enter a substring, in this case "for" and for it to return the next available ID. So in this case the last one used was for128 so I would like excel to return for129.

I would prefer not do it with VBA and only with a function. Can this be done?
Thanks!

 

[Marcelo Branco]

Maybe this


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]

Values​

[/td][td] [/td][td]

Prefix​

[/td][td]

Result​

[/td][/tr]


[tr][td]

2​

[/td][td]

for127​

[/td][td] [/td][td]

for​

[/td][td]

for129​

[/td][/tr]


[tr][td]

3​

[/td][td]

for128​

[/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

4​

[/td][td]

for13​

[/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

5​

[/td][td]

for1​

[/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

6​

[/td][td]

for56​

[/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

7​

[/td][td]

aga567​

[/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

8​

[/td][td]

aga2​

[/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

9​

[/td][td]

aga67​

[/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

10​

[/td][td]

for12​

[/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

11​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]

Array formula in D2
=C2&MAX(IF(LEFT($A$2:$A$10,LEN(C2))=C2,1+SUBSTITUTE($A$2:$A$10,C2,"")))

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[Az566]

Excellent, it works! Thanks!

Can you explain the logic of the formula?

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

Try
Select the cell that houses the formula
go to Formulas > Evaluate Formula to see, step by step, what the formula does.

M.

 

[Az566]

Thanks, I actually worked it out just from the formula

For others though:

=C2&MAX(IF(LEFT($A$2:$A$10,LEN(C2))=C2,1+SUBSTITUTE($A$2:$A$10,C2,"")))

the crucial part is the MAX(IF(LEFT($A$2:$A$10,LEN(C2))=C2

this creates an array of all the strings that match the string you are looking for by taking the length of your string query and looking it up versus the column of strings being searched. The in the second part of the formula

1+SUBSTITUTE($A$2:$A$10,C2,"")))

From that array, it then creates another array for those that matched true, and removes the text from them thereby leaving only the numbers. It then does a simple MAX on that array and concatenates it to the original search term.

I had to amend it slightly as I had numbers such as 007 which were then being translated to 8 rather than 008 but its all working.

Thank you again!

 

[Rick Rothstein]

Maybe this

Array formula in D2
=C2&MAX(IF(LEFT($A$2:$A$10,LEN(C2))=C2,1+SUBSTITUTE($A$2:$A$10,C2,"")))

confirmed with Ctrl+Shift+Enter, not just Enter

Excellent, it works! Thanks!

Actually, there is a potential problem with that formula. If the word you are searching for can be found at the beginning of another word in the list (think "for" and "ford"), then the formula will return a #VALUE! error. If that is a possible scenario with your data, then this revision to Marcelo's formula (still array-entered**) should solve the problem...

=C2&MAX(IF((LEFT($A$2:$A$10,LEN(C2))=C2)*ISNUMBER(0+MID($A$2:$A$10,LEN(C2)+1,1)),1+SUBSTITUTE($A$2:$A$10,C2,"")))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself