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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Lookup Cell[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]1, 2, 4, 12, 14[/TD]
[TD]Relay burnt, Capacitor Burnt, Suction Joint Gas leak, Overload burnt, Thermostat dead[/TD]
[/TR]
</tbody>[/TABLE]


Helper Sheet

[TABLE="class: grid, width: 255, align: left"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Sr No.[/TD]
[TD]Fault & Diagnosing[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Relay burnt[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Capacitor burnt[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Suction Joint Gas leak[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1/3 Compressor low pressure[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1/4 Compressor ceased[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1/3 Condenser Leak[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Capillary broken[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Voltage problem[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Suction Joint Gas leak[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Gas leak[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Overload Burnt[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Overlaod faulty[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Thermostat dead
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[TABLE="class: grid, width: 452"]
<tbody>[TR]
[TD]Sr No.[/TD]
[TD]Fault & Diagnosing[/TD]
[TD]Fault & Diagnosing[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Relay burnt[/TD]
[TD]Relay replaced[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Capacitor burnt[/TD]
[TD]Capacitor replaced[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Suction Joint Gas leak[/TD]
[TD]Gas charged[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]1/3 Compressor low pressure[/TD]
[TD]1/3 Compressor replaced[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]1/4 Compressor ceased[/TD]
[TD]1/4 Compressor replaced[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]1/3 Condenser Leak[/TD]
[TD]1/3 Condenser replaced[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Capillary broken[/TD]
[TD]Gas charged[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Voltage problem[/TD]
[TD]Voltage problem[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Suction Joint Gas leak[/TD]
[TD]Gas charged[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]Gas leak[/TD]
[TD]Gas charged[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]Overload Burnt[/TD]
[TD]Overload Replaced[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]Overlaod faulty[/TD]
[TD]Overload Replaced[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]Thermostat dead[/TD]
[TD]Thermostat replaced[/TD]
[/TR]
</tbody>[/TABLE]

I tried to change these two lines

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

Which resulted as below:

[TABLE="class: grid, width: 985"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Lookup Cell[/TD]
[TD]Result[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]1, 2, 4, 12, 14[/TD]
[TD] [/TD]
[TD]Relay replaced, Capacitor replaced, Gas charged, Overload Burnt, Thermostat dead[/TD]
[/TR]
[TR]
[TD]1,[/TD]
[TD][/TD]
[TD]Relay replaced[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1,[/TD]
[TD][/TD]
[TD]Relay replaced[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1, 2, 4, 12, 19[/TD]
[TD][/TD]
[TD]Relay replaced, Capacitor replaced, Gas charged, Overload Burnt, [/TD]
[/TR]
</tbody>[/TABLE]

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