If row contains specific text, return column Number

babar2019

Board Regular
Joined
Jun 21, 2019
Messages
89
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 AColumn BColumn CColumn D
1Jack Henry123 Main StPlaying Basketball
2Mark Davis101 Main St1234567890
3David Warner102 Main Sttest@test.com
4Oswald Cobblepottest@test.com123 Main St
5Kathryn AligatorPlaying Basketball0987654321

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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
For this data set (on my Sheet3), I get the result shown below it when running the code below that.
Book1
ABCD
11Jack Henry123 Main StPlaying Basketball
22Mark Davis plays basketball101 Main St1234567890
33David Warner102 Main Sttest@test.com
44Oswald Cobblepottest@test.com123 Main St
55Kathryn AligatorPlaying Basketball987654321
Sheet3

Book1
AB
114
222
33NA
44NA
553
Sheet4

VBA Code:
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.
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
In a sepreate column put this in say "E1" and drag down
Excel Formula:
=MATCH("Playing Basketball",A1:D1,0)
You can also substitute the actual text "playing Basketball" for a cell reference that contains your required text
Excel Formula:
=MATCH(A6,A1:D1,0)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,250
Messages
5,600,539
Members
414,386
Latest member
PARAMATHMA SENTHILNATHAN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top