Transpose from columns only found items

[RIC63]

For each column A÷AV -of variable height- I would like to transpose -in an orderly way- the values ​​found equal to those in A2÷F2 in AX3, AX4, AX5 and so on

thanks for any suggestion that may be useful for this purpose

[June7]

I don't understand any of that.

1) "For each column A÷AV -of variable height-" - did you mean "each row" and how is height relevant?

2) A2÷F2 is 1÷77. Exactly what cells in that matrix should match and what values should be retrieved?

3) If output should be in column AX, what are AY, AZ, BA for?

What Excel version do you have?

[RIC63]

First of all thank you for your attempt to help me, I apologize if I was not able to be clear


1) I mean Colum A A11÷A55, column B B11÷B55...up to column AV AV11÷AV55 (by variable column height I meant that they can contain a variable amount of data )


2) each of the 6 numbers of the matrix 1÷77 that is found among the 48 columns should be transposed starting from AX3...so for example in the range A11÷A55 only the numbers 1 and 27 of the matrix are found and they should be transposed into AX3 AY3, in the range B11÷B55 there are 4 values ​​and they should be transposed starting from AX4 towards the right in an orderly fashion and so on until the last column - in my sheet the equal values ​​between the matrix and those in the data area A11 ÷ AV55 are highlighted with equal colors-.

31777


3) I think it's clarified in the previous point


I am working with Excel 2021




Thanks again
I hope I have clarified my request

[June7]

So that should not be a division sign? A range is described with a semicolon, not division sign (just learned it is called obelus).
A11:A55
B11:B55
etc.

Possibly some sort of fancy conditional array formula could accomplish but beyond my expertise.

Use SQL. I think this could involve an UNPIVOT to normalize data then a PIVOT with filter criteria. PIVOT requires 3 fields to generate RowHeader, ColumnHeader, Value.
I can do this in Access. Linked data, used UNION to unpivot and save to a table, ran CROSSTAB with a DCount() expression to calculate ColumnHeader and static WHERE criteria to filter for the specified values. Took about 30 minutes. Excerpt of result:


SrcCol
Data1
Data2
Data3
Data4


1
1
27






2
20
27
41
77


3
1
20
41
77


4
1
20
60




6
27
60
77




7
20
60
77




Possibly PowerQuery can also accomplish but, again, something I have no experience doing in Excel. I got as far as UNPIVOT to normalize data but have no idea how to calculate ColumnHeader for the PIVOT output nor how to apply filter criteria from the A2:F2 range.

Certainly VBA could do it. Like this:

Sub GetData()
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Dim cn As Object, rs As Object, i As Integer, c As Integer, r As Integer
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text"""
With ThisWorkbook.Worksheets("Table1")
.Range("AX:BC").ClearContents
r = 3
For i = 1 To 48
rs.Open "SELECT [" & i & "] FROM [DataTable] " & _
"WHERE [" & i & "] IN(" & .Range("H2") & ") " & _
"ORDER BY [" & i & "]", cn, adOpenStatic, adLockOptimistic, adCmdText
c = 50
.Cells(r, 49) = i
Do While Not rs.EOF
.Cells(r, c) = rs(0)
c = c + 1
If Not rs.EOF Then rs.MoveNext
Loop
r = r + 1
rs.Close
Next
End With
End Sub
I did calc in cell H2: =CONCAT(A2,",",B2,",",C2,",",D2,",",E2,",",F2)

Whichever approach is used, I expect it to be complicated.

[June7]

Or consider this VBA:

Dim i As Integer, x As Integer, r As Integer, c As Integer
With ThisWorkbook.Worksheets("Table1")
.Range("AW:BC").ClearContents
r = 3
For i = 1 To 48
c = 50
For x = 11 To 55
.Cells(r, 49) = i
If Not IsError(Application.Match(.Cells(x, i), .Range("A2:F2"), 0)) Then
.Cells(r, c) = .Cells(x, i)
c = c + 1
End If
Next
r = r + 1
Next
End With
Getting values for each output row into numerical order will require more code, probably involving an array or collection and a sort operation.

And I figured out an Excel formula to return data for a column.
Will have to modify the column letter for each row in the output range. Example for first two.
=TRANSPOSE(SORT(FILTER(A$11:A$55,COUNTIF($A$2:$F$2,A$11:A$55))))
=TRANSPOSE(SORT(FILTER(B$11:B$55,COUNTIF($A$2:$F$2,B$11:B$55))))
No idea if a single formula could be constructed to accomplish this dynamically for all columns.

[arnelgp]

you may also try this VBA:


Public Sub subTranspose()


Const SHT As String = "Table 1"

Const FIRST_COLUMN As String = "A"
Const FIRST_ROW As Integer = 11

Const LAST_COLUMN As String = "AV"
Const LAST_ROW As Integer = 55


Const RESULT_COLUMN As String = "AX"
Const RESULT_ROW As Long = 3

Dim j As Long, k As Long
Dim c As String
Dim rstl_col As String
Dim rslt_row As Long
Dim value As Long

Dim cond As Object
Set cond = CreateObject("scripting.dictionary")
' add the conditions here
cond(1) = 1
cond(20) = 1
cond(27) = 1
cond(41) = 1
cond(60) = 1
cond(77) = 1



rslt_row = RESULT_ROW

With Sheets(SHT)

For k = ColumnLetterToNumber(FIRST_COLUMN) To ColumnLetterToNumber(LAST_COLUMN) Step 1
c = ColumnNumberToLetter(k)
rstl_col = RESULT_COLUMN
For j = FIRST_ROW To LAST_ROW Step 1
value = Val(.Range(c & j) & "")
If cond.exists(value) Then
.Range(rstl_col & rslt_row) = value
rstl_col = ColumnNumberToLetter(ColumnLetterToNumber(rstl_col) + 1)
End If
Next
rslt_row = rslt_row + 1
Next

End With


End Sub


Public Function ColumnLetterToNumber(ByVal col As String) As Long
ColumnLetterToNumber = Range(col & "1").Column
End Function


Public Function ColumnNumberToLetter(ByVal col As Long) As String
ColumnNumberToLetter = Replace$(Replace$(Cells(1, col).Address, "1", ""), "$", "")
End Function

[p45cal]

In cell AX3:
=FILTER($A$2:$F$2,ISNUMBER(MATCH($A$2:$F$2,TRANSPOSE(OFFSET($A$11:$A$55,,RO W()-3)),0)),"")
Copy down to AX50
Copy the conditional formatting from AX3 to all of AX3:BC50

[RIC63]

Thanks to all for the support


all the solutions lead to the result I was looking for

sincerely
thanks again