Rearrange different numbers from different rows in one row and in consecutive columns

Hi,

One way - change the C5:X23 range as necessary or better still set it with a variable that detects the data range

Sub singlerow()
    Dim lLastRow As Long, lLastCol As Integer, lCols As Long, lRows As Long
    Dim r As Range, c As Range, lCounter As Long
    Set r = Range("C5:X23")

    lLastRow = r.Rows.Count
    lLastCol = r.Columns.Count

    With r
        For lRows = 1 To lLastRow
            For lCols = 1 To lLastCol
                If LCase(.Cells(lRows, lCols)) = "x" Then
                    Range("D29").Cells(1, lCounter) = .Cells(lRows, lCols).Offset(-2, 0)
                    lCounter = lCounter + 1
                End If
            Next lCols
        Next lRows
    End With
 
End Sub

@ Richard,

I was going to write some VBA for this problem but decided to see if I could do it using Array formulas. I got so close using Min and Max but am having problems solving it with Small( If(arrays), Columns()-2). What am I missing?? I can use the Array formulas with Min and Max but I'm missing the Small conversion. See the attached and I've got the green cells figured. Now how about using Small for the yellow cells.

@MarvinP

Apologies for butting in, since I see that your question was aimed at Richard, but I think you can use this array formula**:

=SMALL(IF($C$3:$X$23="X",$C$1:$X$21),COLUMNS($A:A))

and drag to the right.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

@xor

Again...nice!

Been a good day for array formulae....