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
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
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?
Last edited by June7; 09-02-2024 at 09:56 AM.
How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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-.
TranspFV_.jpg
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
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:
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.
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
Certainly VBA could do it. Like this:
I did calc in cell H2: =CONCAT(A2,",",B2,",",C2,",",D2,",",E2,",",F2)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
Whichever approach is used, I expect it to be complicated.
Last edited by June7; 09-02-2024 at 04:23 PM.
How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Or consider this VBA:
Getting values for each output row into numerical order will require more code, probably involving an array or collection and a sort operation.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
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.
Last edited by June7; 09-02-2024 at 08:30 PM.
How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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
Last edited by arnelgp; 09-03-2024 at 01:29 AM.
In cell AX3:Copy down to AX50=FILTER($A$2:$F$2,ISNUMBER(MATCH($A$2:$F$2,TRANSPOSE(OFFSET($A$11:$A$55,,ROW()-3)),0)),"")
Copy the conditional formatting from AX3 to all of AX3:BC50
Last edited by p45cal; 09-03-2024 at 02:45 AM.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Thanks to all for the support
all the solutions lead to the result I was looking for
sincerely
thanks again