Consulting

Results 1 to 8 of 8

Thread: Transpose from columns only found items

  1. #1
    VBAX Regular
    Joined
    Jan 2015
    Posts
    98
    Location

    Transpose from columns only found items

    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
    Attached Files Attached Files

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    373
    Location
    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.

  3. #3
    VBAX Regular
    Joined
    Jan 2015
    Posts
    98
    Location
    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

  4. #4
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    373
    Location
    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.
    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.

  5. #5
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    373
    Location
    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.
    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.

  6. #6
    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
    Attached Files Attached Files
    Last edited by arnelgp; 09-03-2024 at 01:29 AM.

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,931
    In cell AX3:
    =FILTER($A$2:$F$2,ISNUMBER(MATCH($A$2:$F$2,TRANSPOSE(OFFSET($A$11:$A$55,,ROW()-3)),0)),"")
    Copy down to AX50
    Copy the conditional formatting from AX3 to all of AX3:BC50
    Attached Files Attached Files
    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.

  8. #8
    VBAX Regular
    Joined
    Jan 2015
    Posts
    98
    Location
    Thanks to all for the support


    all the solutions lead to the result I was looking for

    sincerely
    thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •