• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Arrange the Alphabetic Characters of word / sentence in descending order of Indexed database.

V

VDS

Member
Dear All,

I have created a file of two columns. Column A is date from A to Z ascending order. While in Column B, I have defined from A to Z in descending order i.e from Z to A.

Then whatever character is typed in column C, the corresponding data from Column A can be extracted into Colum D through INDEX and MATCH.

Since the Characters from A to Z are defined, I want to interchange them. For Eg, the Word JPRT = QKIG and EPIC = VKRX, HOW = SLD and like that.

Is it possible to get a formula like that ? In the attachment, Yellow colour words is the question and Red Colour words will be the answer.

In other words, whatever the words typed in Column C, Column D should display the words corresponding to letters typed in Column B. (more than one character)

VDS
 

Attachments

  • Varanasi_Road permit.xlsm
    35.9 KB · Views: 12
Hi VDS,

I think you need VBA here, but this can be done with formulas too.

As you have already applied INDEX/MATCH, so you jest need to concatenate them like:

=INDEX($B$2:$B$27,MATCH(LEFT(C6,1),$A$2:$A$27,0))&INDEX($B$2:$B$27,MATCH(MID(C6,2,1),$A$2:$A$27,0))&INDEX($B$2:$B$27,MATCH(MID(C6,3,1),$A$2:$A$27,0))&INDEX($B$2:$B$27,MATCH(MID(C6,4,1),$A$2:$A$27,0))

or:
=IFERROR(VLOOKUP(LEFT(C6,1),$A$2:$B$27,2,FALSE),"")&IFERROR(VLOOKUP(MID(C6,2,1),$A$2:$B$27,2,FALSE),"")&IFERROR(VLOOKUP(MID(C6,3,1),$A$2:$B$27,2,FALSE),"")&IFERROR(VLOOKUP(MID(C6,4,1),$A$2:$B$27,2,FALSE),"")

This is just an example how to achieve this with formula, you can concatenate add more formulas with above logic.

Regards,
 
@Khalid

The given formula can be applied to only 4 characters. If it is beyond limit, formula has to be restructured. You are right. VB has to be applied. Some EXPERT may help us to get it done.



VDS
 
Yes there is a limitation with formula, you can try adding more formulas with same logic:
Just copy the formula and change the MID part:
&IFERROR(VLOOKUP(MID(C6,5,1),$A$2:$B$27,2,FALSE),"")

this will cover upto 10:
=IFERROR(VLOOKUP(LEFT(C6,1),$A$2:$B$27,2,FALSE),"")&IFERROR(VLOOKUP(MID(C6,2,1),$A$2:$B$27,2,FALSE),"")&IFERROR(VLOOKUP(MID(C6,3,1),$A$2:$B$27,2,FALSE),"")&IFERROR(VLOOKUP(MID(C6,4,1),$A$2:$B$27,2,FALSE),"")&IFERROR(VLOOKUP(MID(C6,5,1),$A$2:$B$27,2,FALSE),"")&IFERROR(VLOOKUP(MID(C6,6,1),$A$2:$B$27,2,FALSE),"")&IFERROR(VLOOKUP(MID(C6,7,1),$A$2:$B$27,2,FALSE),"")&IFERROR(VLOOKUP(MID(C6,8,1),$A$2:$B$27,2,FALSE),"")&IFERROR(VLOOKUP(MID(C6,9,1),$A$2:$B$27,2,FALSE),"")&IFERROR(VLOOKUP(MID(C6,10,1),$A$2:$B$27,2,FALSE),"")

But again there is limitation in formula solution.
 
Interesting solution @Khalid NGO.

@VDS as you have noted, using VBA is better in this case.

You can use a simple UDF to achieve this task.

See this code (add this to a module or to your personal macros workbook).

Code: 
Public Function convertText(thisText As String, mapping As Range) As String
    ' converts the characters in thisText using given mapping range
   
    Dim char As Integer, retval As String
   
    For char = 1 To Len(thisText)
        retval = retval & Application.WorksheetFunction.VLookup(Mid(thisText, char, 1), mapping, 2, False)
    Next char
   
    convertText = retval
End Function

Note: you get #VALUE! error if the mapping table is incomplete.
 
@ Sir,

I tried this code, but getting #Value.

I do not know about this concept of mapping. Could u please tell me further.


VDS
 
VDS said:
@ Sir,

I tried this code, but getting #Value.

I do not know about this concept of mapping. Could u please tell me further.


VDS
Click to expand...
Hi VDS,

Have you tried?
=convertText(C6,A2:B27)

where C6 = ABCDEFGHIJ
output would be = ZYXWVUTSRQ

Regards,
 
@Dear Khalid/r1c1 Sir

I more thing come to my mind. Is the same concept can be applied to numbers ?

For Eq. Column A gives numbers from from 1 to 9 and column B assigned from 9 to 1.

If we type any numbers in Column C more than one digit, Is the same concept can be applied ?

How this can be worked out ? Either through formula or through Macro.


VDS
 
VDS said:
@Dear Khalid/r1c1 Sir

I more thing come to my mind. Is the same concept can be applied to numbers ?

For Eq. Column A gives numbers from from 1 to 9 and column B assigned from 9 to 1.

If we type any numbers in Column C more than one digit, Is the same concept can be applied ?

How this can be worked out ? Either through formula or through Macro.


VDS
Click to expand...

Hi VDS,

I think you've got the answer here:
http://chandoo.org/forum/threads/ar...ending-order-and-reverse-within-a-cell.26384/
 
Ok, this is too cool to not share. Here is one more approach with formulas that works up to 10 characters. This one uses the BASE formula :)

Given the text in I6, mapping table in A2:B27, and these additional ranges:
  • Running numbers 1 to 10 in a range with first cell named as numStart
  • ASCII Codes minus 55 for the mapping letters in the range E2:E27
This formula gives mapped word (works up to 10 letters only).


= BASE( SUMPRODUCT(
N(OFFSET($E$1,MATCH(MID(I2, OFFSET(numStart,,,LEN(I2),1),1),list,0),,1,1)),
(36^(LEN(I2)-OFFSET(numStart,,,LEN(I2),1)))),
36)

The blue part generates an array of numbers by looking in to the E2:E27 range that correspond to each letter of the entered word in I6.
The red part generates powers of 36

SUMPRODUCTS gives the total product of these two arrays.

BASE then converts this number to base 36 representation using the 0 to 9 & A to Z as codes.

See attached.

Tagging @Khalid NGO, @VDS, @John Jairo V ... :awesome: :)
 

Attachments

  • word-mapping-v1.xlsx
    13.1 KB · Views: 13
WoW @r1c1... That was amazing!!! Nice Formula.

Could be BASE function "open the door" for concatenate ranges without VBA?
Blessings!
 
Last edited:
@r1c1 nice use of BASE function! It follows for the particular case of reversing characters one can fill this formula down from J2 for the same results:

=BASE((36^LEN(I2)-1)*9/7-DECIMAL(I2,36),36)

(since adding the input string to the reversed character string returns 19, 1A9, 1AA9, 1AAA9, ... for strings of length 2,3,4,5,... respectively in base 36.)
 
You must log in or register to reply here.
Back
Top