• 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.

Return multiple values into one cell.

blazy

Member
Hey chandoo,

I have one problem. Everything it is mentioned in attached excel file, I think this will be an easy one for you.

I have sheet one that shows the word in Arabic in first column and its Transliteration in second column.

Sheet two has The Arabic title in first column and would like column 2 to have a FUNCTION that will generate the English Transliteration by using the first Sheet that has the Words/Transliteration.

So any time I add a new Arabic title i should see an English Transliteration if I have the word and its Transliteration. If I dont have the word it should stay in Arabic so i know It does not exist in sheet one which should have the WORD and their Transliteration.

Thanks for your help!
 

Attachments

  • Book.Transliteration_chandoo.xlsx
    10.8 KB · Views: 10
Please try this formula in cell B4:

=IF(ISBLANK(VLOOKUP(A4,Words!A:B,2,FALSE)),VLOOKUP(A4,Words!A:B,1,FALSE),VLOOKUP(A4,Words!A:B,2,FALSE))
 
hey thank you for reply. I get the na error... have you tried in example file and did it worked for you?

the formula must return multiple values (translaton), because there can be multiple arabic words in one cell...
 
Hi Blazy,

Use below UDF code to get the result.

Code:
Option Explicit

Function translation(rng As Range) As String

Dim lookupSheet As Worksheet
Dim temp As Variant
Dim lastRow As Long
Dim i As Long, j As Long
Dim result As String

Set lookupSheet = ThisWorkbook.Worksheets("Words")

lastRow = lookupSheet.Cells(Rows.Count, 1).End(xlUp).Row

temp = Split(rng, " ")
For i = LBound(temp) To UBound(temp)
    For j = 2 To lastRow
        If temp(i) = lookupSheet.Range("A" & j) Then
            result = result & lookupSheet.Range("B" & j) & "_"
        End If
    Next j
Next i

If Len(result) > 0 Then
    result = Left(result, Len(result) - 1)
Else
    result = "No Result Found"
End If

translation = result
End Function

Regards,
 
One other question, because I can see that it is now yet done:

If I dont have the word it should stay in Arabic so i know It does not exist in sheet one which should have the WORD and their Transliteration. Because in your code if the word is not known it is just ignored.

Thanks!
 
Back
Top