Apply 12 string transformations words in adjacent column

[BlackieHamel]

I apologize for the long post. I am a VBA beginner and a newcomer to this wonderful board, and I may be giving too much preface. My question is way at the bottom.

I'm making a word puzzle that involves finding 6-letter words that can be written in a circle, beginning at any point and going either clockwise or counterclockwise. Given that there are 2 directions and 6 starting points in a circle, there are twelve possible ways a given string, say REY, can be written. I have succeeded at getting a database to return a list of words that work. Here is the REY example, in abridged form:
[TABLE="width: 58"]
<colgroup><col></colgroup><tbody>[TR]
[TD] REY...[/TD]
[/TR]
[TR]
[TD] .REY..[/TD]
[/TR]
[TR]
[TD]DREYER[/TD]
[/TR]
[TR]
[TD]GREYED[/TD]
[/TR]
[TR]
[TD] ..REY.[/TD]
[/TR]
[TR]
[TD]COREYS[/TD]
[/TR]
[TR]
[TD] ...REY[/TD]
[/TR]
[TR]
[TD]AUBREY[/TD]
[/TR]
[TR]
[TD]AUDREY[/TD]
[/TR]
[TR]
[TD]CARREY[/TD]
[/TR]
[TR]
[TD]DELREY[/TD]
[/TR]
[TR]
[TD]OSPREY[/TD]
[/TR]
[TR]
[TD]surrey[/TD]
[/TR]
[TR]
[TD] EY...R[/TD]
[/TR]
[TR]
[TD] R...YE[/TD]
[/TR]
[TR]
[TD]RALLYE[/TD]
[/TR]
[TR]
[TD]REDEYE[/TD]
[/TR]
[TR]
[TD]redeye[/TD]
[/TR]
[TR]
[TD]RIBEYE[/TD]
[/TR]
[TR]
[TD] YER...[/TD]
[/TR]
[TR]
[TD]YERKES[/TD]
[/TR]
[TR]
[TD]YEROUT[/TD]
[/TR]
[TR]
[TD]YERTLE[/TD]
[/TR]
[TR]
[TD] .YER..[/TD]
[/TR]
[TR]
[TD] ..YER.[/TD]
[/TR]
[TR]
[TD]BUYERS[/TD]
[/TR]
[TR]
[TD]buyers[/TD]
[/TR]
[TR]
[TD]DRYERS[/TD]
[/TR]
[TR]
[TD]dryers[/TD]
[/TR]
[TR]
[TD]FLYERS[/TD]
[/TR]
[TR]
[TD]flyers[/TD]
[/TR]
[TR]
[TD]FOYERS[/TD]
[/TR]
[TR]
[TD]foyers[/TD]
[/TR]
[TR]
[TD]FRYERS[/TD]
[/TR]
[TR]
[TD] ...YER[/TD]
[/TR]
[TR]
[TD]BRAYER[/TD]
[/TR]
[TR]
[TD]GRAYER[/TD]
[/TR]
[TR]
[TD]grayer[/TD]
[/TR]
[TR]
[TD]GREYER[/TD]
[/TR]
[TR]
[TD]greyer[/TD]
[/TR]
[TR]
[TD]LAWYER[/TD]
[/TR]
[TR]
[TD]lawyer[/TD]
[/TR]
[TR]
[TD] ER...Y[/TD]
[/TR]
[TR]
[TD] Y...RE[/TD]
[/TR]
[TR]
[TD]YOUARE
[/TD]
[/TR]
</tbody>[/TABLE]

Note that for my first query, REY???, the database had no matches, but for ?REY??, it found GREYED and DREYER and a few others.

Here's my challenge: If I have the above list in Column B, I would like all the extra (i.e., non REY) letters to be displayed, in the order they would need to be written for the word to appear in circular form. Here's the solution for REY:

[TABLE="width: 122"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD] REY...[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] .REY..[/TD]
[/TR]
[TR]
[TD]DRE[/TD]
[TD]DREYER[/TD]
[/TR]
[TR]
[TD]GDE[/TD]
[TD]GREYED[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] ..REY.[/TD]
[/TR]
[TR]
[TD]ROC[/TD]
[TD]COREYS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] ...REY[/TD]
[/TR]
[TR]
[TD]ERB[/TD]
[TD]AUBREY[/TD]
[/TR]
[TR]
[TD]ERD[/TD]
[TD]AUDREY[/TD]
[/TR]
[TR]
[TD]ERR[/TD]
[TD]CARREY[/TD]
[/TR]
[TR]
[TD]ERL[/TD]
[TD]DELREY[/TD]
[/TR]
[TR]
[TD]ERP[/TD]
[TD]OSPREY[/TD]
[/TR]
[TR]
[TD]err[/TD]
[TD]surrey[/TD]
[/TR]
[TR]
[TD]ERR[/TD]
[TD]TORREY[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] EY...R[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] R...YE[/TD]
[/TR]
[TR]
[TD]ALL[/TD]
[TD]RALLYE[/TD]
[/TR]
[TR]
[TD]EDE[/TD]
[TD]REDEYE[/TD]
[/TR]
[TR]
[TD]IBE[/TD]
[TD]RIBEYE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] YER...[/TD]
[/TR]
[TR]
[TD]KES[/TD]
[TD]YERKES[/TD]
[/TR]
[TR]
[TD]OUT[/TD]
[TD]YEROUT[/TD]
[/TR]
[TR]
[TD]TLE[/TD]
[TD]YERTLE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] .YER..[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] ..YER.[/TD]
[/TR]
[TR]
[TD]SBU[/TD]
[TD]BUYERS[/TD]
[/TR]
[TR]
[TD]sbu[/TD]
[TD]buyers[/TD]
[/TR]
[TR]
[TD]SDR[/TD]
[TD]DRYERS[/TD]
[/TR]
[TR]
[TD]sdr[/TD]
[TD]dryers[/TD]
[/TR]
[TR]
[TD]SFL[/TD]
[TD]FLYERS[/TD]
[/TR]
[TR]
[TD]sfl[/TD]
[TD]flyers[/TD]
[/TR]
[TR]
[TD]SFO[/TD]
[TD]FOYERS[/TD]
[/TR]
[TR]
[TD]sfo[/TD]
[TD]foyers[/TD]
[/TR]
[TR]
[TD]SFR[/TD]
[TD]FRYERS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] ...YER[/TD]
[/TR]
[TR]
[TD]BRA[/TD]
[TD]BRAYER[/TD]
[/TR]
[TR]
[TD]GRA[/TD]
[TD]GRAYER[/TD]
[/TR]
[TR]
[TD]gra[/TD]
[TD]grayer[/TD]
[/TR]
[TR]
[TD]GRE[/TD]
[TD]GREYER[/TD]
[/TR]
[TR]
[TD]gre[/TD]
[TD]greyer[/TD]
[/TR]
[TR]
[TD]LAW[/TD]
[TD]LAWYER[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] ER...Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Y...RE[/TD]
[/TR]
[TR]
[TD]AUO[/TD]
[TD]YOUARE

[/TD]
[/TR]
</tbody>[/TABLE]


I have figured out the string functions for the 12 transformations. They are

[TABLE="width: 128"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 2"]=ReverseString(RIGHT(B?,3))[/TD]
[/TR]
[TR]
[TD="colspan: 2"]=CONCATENATE(LEFT(B?,1),ReverseString(RIGHT(B?,2)))[/TD]
[/TR]
[TR]
[TD="colspan: 2"]=CONCATENATE(ReverseString(MID(B?,1,2)),RIGHT(B?,1))[/TD]
[/TR]
[TR]
[TD="colspan: 2"]=ReverseString(LEFT(B?,3))[/TD]
[/TR]
[TR]
[TD="colspan: 2"]=ReverseString(MID(B?,3,3))[/TD]
[/TR]
[TR]
[TD="colspan: 2"]=MID(B?,2,3)[/TD]
[/TR]
[TR]
[TD="colspan: 2"]=RIGHT(B?,3)[/TD]
[/TR]
[TR]
[TD="colspan: 2"]=CONCATENATE(RIGHT(B?,2),LEFT(B?,1))[/TD]
[/TR]
[TR]
[TD="colspan: 2"]=CONCATENATE(RIGHT(B?,1),LEFT(B?,2))[/TD]
[/TR]
[TR]
[TD="colspan: 2"]=LEFT(B?,3)[/TD]
[/TR]
[TR]
[TD="colspan: 2"]=MID(B?,3,3)[/TD]
[/TR]
[TR]
[TD="colspan: 2"]=ReverseString(MID(B?,2,3))

where ? is the current row number.


NOW, FINALLY, MY QUESTION: Given that the database includes headers with periods in place of the ? wildcards, I need to generate a Column A given these headers in B. Note that all 12 headers always appear, even if there are no findings for a given string. I *think* the answer is to create a long macro that (1) searches for a period in Column B; (2) verifies that there is a non-period-containing entry in the next Row of B; (3) goes to Column A and enters the appropriate string function; (4) copies it down until the next Column B entry has a period in it; and (5) repeats until all 12 string functions have been entered.

I don't expect anyone to write the macro for me, but can someone give me a start? How would you attack this?

Blackie
[/TD]
[/TR]
</tbody>[/TABLE]

 

[BiocideJ]

I believe this does more or less what you are asking.
The 'trick' I used is to concatenate the full word to itself... e.g. YouAre = YouAreYouAre
I can then use normal find's which will find words that only exist when 'in a ring'. Simple STRREVERSE function to lookup backwards as well and viola.

Let me know if this doesn't work and also I didn't do anything with the results that had ...

Public Function RemainingLetters(LookupString As String, ScrubString As String)
Dim RingedString As String
RingedString = UCase(LookupString & LookupString)
If InStr(RingedString, ScrubString) > 0 Then
    'string exists running forward
    RemainingLetters = Replace(Mid(RingedString, InStr(RingedString, ScrubString), Len(LookupString)), ScrubString, "")
    
ElseIf InStr(RingedString, StrReverse(ScrubString)) > 0 Then
    'string exists running backwards
    RemainingLetters = Replace(Mid(RingedString, InStr(RingedString, StrReverse(ScrubString)), _
        Len(LookupString)), StrReverse(ScrubString), "")
Else
    'ScrubString does not exist in LookupString
    RemainingLetters = CVErr(xlErrNA)
End If
'The output seemed to be backwards compared to your request so I use STRREVERSE to flip it.
'Take out the line below if the output order is backwards from what you want.
RemainingLetters = StrReverse(RemainingLetters)

End Function

To use this code, place it in a Standard Module and call it through an Excel Formula
For instance, in cell A1 type =remainingletters(B1,"REY")
You can replace "REY" with any partial word you wish to lookup.

 

[BlackieHamel]

Wow. Wow. That is so clever, it's poetic! Thank you SO much for that elegant solution.

BTW, I did need to comment out the last reversal, since GREYED with REY, the string I want is GDE, not EDG. If you write REY above GDE with the G at 8 o'clock, you'll see GREYED in clockwise order.

Michael

 

[BiocideJ]

Excellent. Glad it worked out.
Thanks for replying that it was what you needed.

 

[BiocideJ]

Per your PM, I believe the reason some are backwards is I am reading the remaining letters in the same order for both and since they are a ring, they actually are being read backwards for one of the two examples.

If I am understanding you correctly the below code should be correct, however, if this is backwards then the second code should be good.
At any rate... ONE of these two should be what you are looking for.

Public Function RemainingLetters(LookupString As String, ScrubString As String)
Dim RingedString As String
RingedString = UCase(LookupString & LookupString)
If InStr(RingedString, ScrubString) > 0 Then
    'string exists running forward
    RemainingLetters = StrReverse(Replace(Mid(RingedString, InStr(RingedString, ScrubString), Len(LookupString)), ScrubString, ""))
    
ElseIf InStr(RingedString, StrReverse(ScrubString)) > 0 Then
    'string exists running backwards
    RemainingLetters = Replace(Mid(RingedString, InStr(RingedString, StrReverse(ScrubString)), _
        Len(LookupString)), StrReverse(ScrubString), "")
Else
    'ScrubString does not exist in LookupString
    RemainingLetters = CVErr(xlErrNA)
End If
End Function

Second option:

Public Function RemainingLetters(LookupString As String, ScrubString As String)
Dim RingedString As String
    RingedString = UCase(LookupString & LookupString)
    If InStr(RingedString, ScrubString) > 0 Then
        'string exists running forward
        RemainingLetters = Replace(Mid(RingedString, InStr(RingedString, ScrubString), Len(LookupString)), ScrubString, "")
        
    ElseIf InStr(StrReverse(RingedString), ScrubString) > 0 Then
        'string exists running backwards
        RemainingLetters = Replace(Mid(StrReverse(RingedString), InStr(StrReverse(RingedString), ScrubString), _
            Len(LookupString)), ScrubString, "")
    Else
        'ScrubString does not exist in LookupString
        RemainingLetters = CVErr(xlErrNA)
    End If

End Function

 

[BiocideJ]

Another consideration that I just thought of and it is definitely a unique one is PALINDROMES (words that read the same forward or backward)

For instance, the word
PULLUP
with a ScrubString of "LLU" (or "ULL")
could potentially give you two correct outputs of either UPP or PPU

For the purposes of this macro, it will give the forward reading one only (UPP I believe)
Just thought I'd throw that tidbit out there as when the words are made into a ring some non-Palindromic words may become palindromes.
I.E. BCCBAA becomes palindromic if you are looking up CCB (or BCC) with the resulting answer either "BAA" or "AAB"

If you don't mind me asking also, what is the end use of this 'text ring'. I find the concept interesting, but wonder what the application is.

 

[BlackieHamel]

The first version worked. Thanks a million!