If row contains specific text, return column Number

[babar2019]

Hi,

I have an excel sheet where Column A is 'App ID' which is unique and it also has 20 other columns of data. The problem is not every row has equal number of columns. Some may have 10 and others 15, 20. So basically the data except Column A is jumbled.

I have a sheet 2 full of App ID's and I'm trying to find a specific text for each of these App ID's and trying to find what column number the text is located in.

I don't mind if the result is the cell address, column number or the actual cell contents. I just need to know where that specifix text is for each of the unique App ID's.


Example:

Column A	Column B	Column C	Column D
1	Jack Henry	123 Main St	Playing Basketball
2	Mark Davis	101 Main St	1234567890
3	David Warner	102 Main St	test@test.com
4	Oswald Cobblepot	test@test.com	123 Main St
5	Kathryn Aligator	Playing Basketball	0987654321

assuming my Sheet 1 is what the above table is, If I have to know which column the word Basketball contains, my result should look like:

Column A Column B
1 4
2 NA
3 NA
4 NA
5 3

 

[JoeMo]

For this data set (on my Sheet3), I get the result shown below it when running the code below that.

Book1
	A	B	C	D
1	1	Jack Henry	123 Main St	Playing Basketball
2	2	Mark Davis plays basketball	101 Main St	1234567890
3	3	David Warner	102 Main St	test@test.com
4	4	Oswald Cobblepot	test@test.com	123 Main St
5	5	Kathryn Aligator	Playing Basketball	987654321
Sheet3

Book1
	A	B
1	1	4
2	2	2
3	3	NA
4	4	NA
5	5	3
Sheet4

Option Compare Text
Sub WhichColumns()
Const Keywrd As String = "Basketball"  'change to suit
Dim ShtS As Worksheet, ShtR As Worksheet, i As Long, j As Long, Ct As Long
Dim Vin As Variant, Vout As Variant, x As Variant
Set ShtS = Sheets("Sheet3")  ' data source sheet: change sheet name to suit
Set ShtR = Sheets("Sheet4")  ' data output sheet: change sheet name to suit
Vin = ShtS.Range("A1").CurrentRegion
ReDim Vout(1 To UBound(Vin, 1), 1 To 2)
For i = 1 To UBound(Vin, 1)
    For j = 1 To UBound(Vin, 2)
        If InStr(Vin(i, j), Keywrd) Then
            Ct = Ct + 1
            Vout(i, 1) = Vin(i, 1)
            x = x & ", " & j
        End If
    Next j
    If x <> "" Then Vout(i, 2) = Right(x, Len(x) - 2)
    If Ct = 0 Then
        Vout(i, 1) = Vin(i, 1)
        Vout(i, 2) = "NA"
    End If
    x = ""
    Ct = 0
Next i
With ShtR
    .Range("A1").Resize(UBound(Vout, 1), 2) = Vout
End With
End Sub

This is NOT case sensitive. Remove the Option Compare Text line at the top of the code if you prefer it to be case sensitive.

 

[Michael M]

In a sepreate column put this in say "E1" and drag down

=MATCH("Playing Basketball",A1:D1,0)

You can also substitute the actual text "playing Basketball" for a cell reference that contains your required text

=MATCH(A6,A1:D1,0)