Filter predefined list of search items and filtered data save in different columns

[nbuddhi]

Dear Team,

There are thousands of data in column A. I have the list of search items located in column B.

I need to filter or search in column A for the item listed in column B and paste in a row. Search result for B1 need to paste in column C, same way B2 result to be paste in column D (Means Bn th result in column n+2).

Kindly help me to write a macro on this requirement.

Thanks & Best Rgds,
nbuddhi

 

[nbuddhi]

Enclosed the dummy data for before and expected data sheet for easy reference. Thank you

 

[Marc L]

Hi, could be easier with the first row as headers for using easy Excel basics so any chance ?​

There are thousands of data in column A. I have the list of search items located in column B.

As your attachment has nothing in column B, so weird when not matching exactly the initial explanation …​

​

 

[nbuddhi]

Dear Mark,

Sorry, corrected the excel document. Thank you for your feedback.

Best Rgds,
nbuddhi

 

[Marc L]

In fact do you need column A to be listed to columns only partialy according to column B items​

or do you need to list every column A unique item so in this case no need to fill manually column B ?​

​

 

[nbuddhi]

Dar Marc,

Data in column A is not refined as dummy and those data long strings with many information. So that I want filter column a if find search criteria text at any area of the string.

Thanms & Best Rgds,
nbuddhi

 

[Marc L]

So, clearly, is it possible not all column A unique items are listed to individual columns but only some according to B list ?​

​

Should be the search cases sensitive or not ?​

​

 

[nbuddhi]

Dear Marc,

Sorry for confusion with my dummy data, I need all the strings as per B list and those data may or may not unique. I changed the dummy data slightly as attached excel data for clarity and added a new sheet to understand the real data to be filtered if search for " S ".

Should be the search cases sensitive or not ? Either way OK

Thanks & Best Rgds,
nbuddhi

 

[Marc L]

I want filter column a if find search criteria text at any area of the string.

But your last attachment Expected Result for 123 shows only a single match like a 'starting with' criteria​

so which one is really needed : 'starting with' or 'any area' ?!​

​

 

[nbuddhi]

I missed to revise 123 area and corrected. Thank you.

 

[Marc L]

According to your last attachment an Excel basics starter VBA demonstration to paste only to the Sheet1 (Before State) worksheet module :​

Sub Demo1()
            Dim Rg As Range, L&
    With UsedRange.Columns
            If .Count > 2 Then .Item(3).Resize(, .Count - 2).Clear
            Set Rg = Cells(Columns.Count).Resize(2)
        For L = 2 To Cells(Rows.Count, 2).End(xlUp).Row
                Rg(2).Formula = "=ISNUMBER(SEARCH($B$" & L & ",A2))"
             .Item(1).AdvancedFilter 2, Rg, Cells(L + 1)
        Next
                Rg.Clear
            Set Rg = Nothing
        With .Item(3).Resize(, UsedRange.Columns.Count - 2)
             .ColumnWidth = .Item(-1).ColumnWidth
             .Rows(1) = Evaluate("TRANSPOSE(B2:B" & L - 1 & ")")
        End With
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[nbuddhi]

Dear Marc,

Wow great. It worked like charm. No words to appreciate. Many thanks for your great support.

Best Rgds,
nbuddhi

 

[nbuddhi]

Dear Marc,

This helped me a lot. Appreciate if you can guide me how I can change the code to use for some other ranges like attached excel chart. I tried to change the code but it not worked.

Thanks & Best Rgds,
Nuwan Buddhika.

 

[Marc L]

According to your last attachment with this last VBA demonstration​

you have all the necessary to learn and to do yourself your future changes :​

Sub Demo2()
        Dim L&
        [Q2].CurrentRegion.ClearContents
    With Range("N2", Cells(Rows.Count, 14).End(xlUp))
        For L = 3 To Cells(Rows.Count, 15).End(xlUp).Row
            [K2].Formula = "=ISNUMBER(SEARCH($O$" & L & ",N3))"
           .AdvancedFilter 2, [K1:K2], Cells(2, L + 14)
        Next
    End With
        If L > 3 Then [K2].Clear: [Q2].Resize(, L - 3) = Evaluate("TRANSPOSE(O3:O" & L - 1 & ")")
End Sub

You may Like it !​

​

 

[nbuddhi]

Dear Marc,

I don't have words to express my gratitude. Many thanks for the support. I did try with another range and understood how it works. Thanks again.
Have a great day!

Best Rgds,
nbuddhi

 

[p45cal]

Without VBA:
On the Before State sheet cell E1:

=TRANSPOSE($B$2:$B$6)

and in cell E2:

=FILTER($A$2:$A$29,ISNUMBER(SEARCH(E1,$A$2:$A$29)))

and drag/copy E2 to the right:

(yellow cells have formulae)

 

[nbuddhi]

Dear P45cal,

Great, thank you for that amazing formula.

Thanks & B est Rgds,
nbuddhi