Finding data position in table

ab123456

New Member
Joined
May 15, 2019
Messages
4
Hello, can anybody help me with a solution to my problem.

I receive a data set from a 3rd party that i have no control over the formatting of. I have my own sheet with various formulas that refer to data in the 3rd party sheet however the producer of the original sheet regularly changes the ordering of columns and rows on his sheet. Is there a way i can find data position in a table when both the row and column may vary???

The formulas should return A2 for the first example below and D3 for the 2nd.

A
B
C
D
1
2
DATA
3
4

<tbody>
</tbody>


A
B
C
D
1
2
3
DATA
4

<tbody>
</tbody>


Thanks.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Do you know how many columns that DATA might vary from? I have a solution if it lies within the first 4 columns but it is sort of messy and not practical if DATA could appear in say, column W

=SUBSTITUTE(IFNA(ADDRESS(MATCH("DATA",A:A,FALSE),1),IFNA(ADDRESS(MATCH("DATA",B:B,FALSE),2),IFNA(ADDRESS(MATCH("DATA",C:C,FALSE),3),IFNA(ADDRESS(MATCH("DATA",D:D,FALSE),4),"Not in A B C or D")))),"$","")

Put this formula anywhere except for the first 4 columns.
 
Last edited:
Upvote 0
If you have TEXTJOIN you can use:

Code:
=SUBSTITUTE(TEXTJOIN("",1,IF(A1:D4="DATA",ADDRESS(ROW(A1:D4),COLUMN(A1:D4)),"")),"$","")
accept with Ctrl+Shift+Enter (as it is array formula)
 
Upvote 0
So, adapt tyija1995 formula I am afraid won't be possible (too long) and my solution taaaaaakes a while but finally should gives results.

 
Upvote 0
Yeah my formula will be too ridiculous to span over 20+ columns :LOL: your array formula works a lot better - can adapt the range of it easy enough, good stuff!
 
Upvote 0
Kokosek, dont have textjoin. Only running 2016 version of software but looks like textjoin only appeared in 2019
 
Upvote 0
Hey, I found this formula online that replicates TEXTJOIN for older versions that don't have the built in one:

Code:
Function TEXTJOINS(delimiter As String, ignore_empty As Boolean, ParamArray textn() As Variant) As String
    Dim i
    Dim rng
    For Each rng In textn
        If IsObject(rng) Or IsArray(rng) Then
            For Each i In rng
                If Len(i) = 0 Then
                    If Not ignore_empty Then
                        TEXTJOINS = TEXTJOINS & i & delimiter
                    End If
                Else
                    TEXTJOINS = TEXTJOINS & i & delimiter
                End If
            Next
        Else
            If Len(rng) = 0 Then
                If Not ignore_empty Then
                    TEXTJOINS = TEXTJOINS & rng & delimiter
                End If
            Else
                TEXTJOINS = TEXTJOINS & rng & delimiter
            End If
        End If
    Next
    TEXTJOINS = Left(TEXTJOINS, Len(TEXTJOINS) - 1)
End Function

Then use this in cell U1:
=SUBSTITUTE(TEXTJOINS("",1,IF($A$1:$T$1000="DATA",ADDRESS(ROW($A$1:$T$1000),COLUMN($A$1:$T$1000)),"")),"$","")
ENTER AS AN ARRAY FORMULA! (Ctrl+Shift+Enter)

Then in cell U2:
=MATCH("DATA",INDIRECT(U1&":"&U1),FALSE)
Enter this as a normal formula

Finally cell U3:
=CONCAT(U1,U2)

The UDF made doesn't produce the row number but it gives the column number - so with an additional formula (cell U2) it can be discovered and concatenated.
 
Upvote 0
Thanks both for you help, the suggestion of a function gave me an idea and i came up with this

Function FindCol(searchterm, rng As range)

Dim cell As range
For Each cell In rng
If cell.Value = searchterm Then
targetcol = cell.Column
Exit For
End If

Next
FindCol = targetcol

End Function

In combination with a similar function to find the row it is a bit slow but works.

Thanks again.
 
Upvote 0
you do not need similar for row, little tweak do the job:

Code:
Function FindAddress(searchterm As String, rng As Range) As String
Dim cell As Range
For Each cell In rng
    If cell.Value = searchterm Then
        targetcol = Replace(cell.Address, "$", "")
    Exit For
    End If
Next
FindAddress = targetcol
End Function

Excel 2013/2016
ABCDEF
1B3
2
3data
4
5

<tbody>
</tbody>


Worksheet Formulas
CellFormula
F1=FindAddress("data",A1:D5)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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
Back
Top