find all values corresponding to each values

[Deepk]

hi all,

I want vba code for following activity. Sample data is provided below.

Copy each value in 'Find value' column and find all in column 'For Topic 1'. Paste the find all values in the column 'Topic 1'. Repeat this activity for all column Topic 1 to 4.

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Find Value[/TD]
[TD]Topic 1[/TD]
[TD]Topic 2[/TD]
[TD]Topic 3[/TD]
[TD]Topic 4[/TD]
[TD]For Topic 1[/TD]
[TD]For Topic 2[/TD]
[TD]For Topic 3[/TD]
[TD]For Topic 4[/TD]
[/TR]
[TR]
[TD]TA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TA|CA|PA|KA[/TD]
[TD]TA|CA|PA|KA[/TD]
[TD]CA|PA|KA[/TD]
[TD]PA|KA[/TD]
[/TR]
[TR]
[TD]PA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CA|PA|KA[/TD]
[TD]CA|PA|KA[/TD]
[TD]KA[/TD]
[TD]CA|PA|KA[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PA|KA[/TD]
[TD]CA|PA|KA[/TD]
[TD]PA|KA[/TD]
[TD]PA|KA[/TD]
[/TR]
[TR]
[TD]KA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]KA[/TD]
[TD]KA[/TD]
[TD]KA[/TD]
[TD]KA[/TD]
[/TR]
</tbody>[/TABLE]

The final data should appear like the table below.

Please help me with the code. Thank you.
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Find Value[/TD]
[TD]Topic 1[/TD]
[TD]Topic 2[/TD]
[TD]Topic 3[/TD]
[TD]Topic 4[/TD]
[TD]For Topic 1[/TD]
[TD]For Topic 2[/TD]
[TD]For Topic 3[/TD]
[TD]For Topic 4[/TD]
[/TR]
[TR]
[TD]TA[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]TA|CA|PA|KA[/TD]
[TD]TA|CA|PA|TA[/TD]
[TD]CA|PA|KA[/TD]
[TD]PA|KA[/TD]
[/TR]
[TR]
[TD]PA[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]CA|PA|KA[/TD]
[TD]CA|PA|KA|TA[/TD]
[TD]KA[/TD]
[TD]CA|PA|KA[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]PA|KA[/TD]
[TD]CA|PA|KA[/TD]
[TD]PA|KA[/TD]
[TD]PA|KA[/TD]
[/TR]
[TR]
[TD]KA[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]KA[/TD]
[TD]KA[/TD]
[TD]KA[/TD]
[TD]KA

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

 

[Deepk]

If "Find Value" is in A1

The put =COUNTIF(F:F, "*" & $A2 & "*") in B2 and drag down and right.

Macro code was needed. Anyways, never expected that it is so simple. Thank you :D

 

[mikerickson]

If you need VBA, this is a non-looping approach

Sub test()
    With Sheet1.Range("A:A")
        With Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
            With .Offset(0, 1).Resize(, 4)
                .FormulaR1C1 = "=COUNTIF(C[4], ""*"" & RC1 & ""*"")"
                .Value = .Value
            End With
        End With
    End With
End Sub