Finding data position in table

[ab123456]

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.

[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]DATA
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DATA
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks.

 

[tyija1995]

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.

 

[KOKOSEK]

If you have TEXTJOIN you can use:

=SUBSTITUTE(TEXTJOIN("",1,IF(A1:D4="DATA",ADDRESS(ROW(A1:D4),COLUMN(A1:D4)),"")),"$","")

accept with Ctrl+Shift+Enter (as it is array formula)

 

[ab123456]

Thanks but data source can be quite large, at least 20 cols and 1000 rows.

 

[KOKOSEK]

So, adapt tyija1995 formula I am afraid won't be possible (too long) and my solution taaaaaakes a while but finally should gives results.

 

[tyija1995]

Yeah my formula will be too ridiculous to span over 20+ columns your array formula works a lot better - can adapt the range of it easy enough, good stuff!

 

[ab123456]

Kokosek, dont have textjoin. Only running 2016 version of software but looks like textjoin only appeared in 2019

 

[tyija1995]

Hey, I found this formula online that replicates TEXTJOIN for older versions that don't have the built in one:

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.

 

[ab123456]

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.

 

[KOKOSEK]

you do not need similar for row, little tweak do the job:

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

	A	B	C	D	E	F
data

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]B3[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F1[/TH]
[TD="align: left"]=FindAddress("data",A1:D5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]