Lookup Numbers & Result in Corresponding Text

[mohdamir1989]

Hi All,

Is it possible to lookup a string of numbers in any cell from other workbook and result in the corresponding text (Excel 2007)

Thank you in advance.


For example:

Lookup Cell	Result
1, 2, 4, 12, 14	Relay burnt, Capacitor Burnt, Suction Joint Gas leak, Overload burnt, Thermostat dead

<tbody>
</tbody>

Helper Sheet

Sr No.	Fault & Diagnosing
1	Relay burnt
2	Capacitor burnt
4	Suction Joint Gas leak
5	1/3 Compressor low pressure
6	1/4 Compressor ceased
7	1/3 Condenser Leak
8	Capillary broken
9	Voltage problem
10	Suction Joint Gas leak
11	Gas leak
12	Overload Burnt
13	Overlaod faulty
14	Thermostat dead

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[mohdamir1989]

Glad we could help & thanks for the feedback.

If you have Office 365 then it might be possible with TextJoin, other than that, it might be possible with a shed load of helper columns.
Formulae aren't my strong suit so not sure.

Sorry for almost no knowledge about VBA. There's one more query to the same task

Can results be expanded like

Sr No.	Fault & Diagnosing	Fault & Diagnosing
1	Relay burnt	Relay replaced
2	Capacitor burnt	Capacitor replaced
4	Suction Joint Gas leak	Gas charged
5	1/3 Compressor low pressure	1/3 Compressor replaced
6	1/4 Compressor ceased	1/4 Compressor replaced
7	1/3 Condenser Leak	1/3 Condenser replaced
8	Capillary broken	Gas charged
9	Voltage problem	Voltage problem
10	Suction Joint Gas leak	Gas charged
11	Gas leak	Gas charged
12	Overload Burnt	Overload Replaced
13	Overlaod faulty	Overload Replaced
14	Thermostat dead	Thermostat replaced

<tbody>
</tbody>

I tried to change these two lines

".Item(Cl.Value) = Cl.Offset(, 2).Value
Cl.Offset(, 2).Value = Tmp"

Which resulted as below:

Lookup Cell	Result	Result
1, 2, 4, 12, 14		Relay replaced, Capacitor replaced, Gas charged, Overload Burnt, Thermostat dead
1,		Relay replaced
1,		Relay replaced
1, 2, 4, 12, 19		Relay replaced, Capacitor replaced, Gas charged, Overload Burnt,

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I don't know to mention both at the same time.

 

[Fluff]

How about

Sub MyConcat()
   Dim Cl As Range
   Dim Sws As Worksheet, Mws As Worksheet
   Dim Wrds As Variant, Tmp As Variant, Tmp2 As Variant
   Dim i As Long
   
   Set Sws = Workbooks("book1.xlsm").Sheets("List")
   Set Mws = Sheets("Sheet3")
   With CreateObject("scripting.dictionary")
      For Each Cl In Sws.Range("A2", Sws.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Array(Cl.Offset(, 1).Value, Cl.Offset(, 2).Value)
      Next Cl
      For Each Cl In Mws.Range("A2", Mws.Range("A" & Rows.Count).End(xlUp))
         Wrds = Split(Cl.Value, ", ")
         For i = 0 To UBound(Wrds)
            If .Exists(CLng(Wrds(i))) Then
               If Tmp = "" Then
                  Tmp = .Item(CLng(Wrds(i)))(0)
                  Tmp2 = .Item(CLng(Wrds(i)))(1)
               Else
                  Tmp = Tmp & ", " & .Item(CLng(Wrds(i)))(0)
                  Tmp2 = Tmp2 & ", " & .Item(CLng(Wrds(i)))(1)
               End If
            End If
         Next i
         Cl.Offset(, 1).Value = Tmp
         Cl.Offset(, 2).Value = Tmp2
         Tmp = "": Tmp2 = ""
      Next Cl
   End With
End Sub