transpose a vertical range of cells...

[Waxaholic]

I am needing to transpose a vertical range of cells that varies in length. Can someone help me with creating the formula to accomplish this?

Here is a sample of the data:

1 100
1 200
1 215
1 225
1 300
2 220
2 222
2 244
2 310
2 300
2 249
3 600
3 500

I want to have it lookup the value in column 1 and transpose everything in column 2 that belongs to the unique identity in column 1 only. Would appreciate any and all help.

Thanks,

Brian

 

[ETID]

Highlite the columns, and copy, select the first cell to paste to, then edit>paste special>...check the transpose box, then ok.

NOTE: check the values only button if the column contains formulas, to return the data.

 

[SkipVought]

Hi,
I do not think that you can write a cell formulae to do what you want. There is a way to identify the first and last entry in a lookup (assuming the lookup values are in Column A Rows 1:999:
first lookup index is =MATCH(A1,$A$1:$A$999,0)
last lookup index is =MATCH(A1,$A$1:$A$999,1)
I prefer to use named ranges, so if you name the Column A range "Lookups" then each formula is respectively:
=MATCH(Lookups,Lookups,0)
=MATCH(Lookups,Lookups,1)
But how you would get the range to be transposed is what I cannot figure out.

Here's how it can be done using VBA -- it assumes that
1 the lookup values are in Column A
2. the data values are in Column B
3. the transposed values go into the last row of the lookup value in Column D.

Sub Trans()
    Dim ThisLookup, Prevlookup, Values(), index, TempValue
    Dim Lookup As Range, LookupRange As Range, lRow, iCol
    
    Set LookupRange = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
    
    Prevlookup = ""
    index = 0
    For Each Lookup In LookupRange
        With Lookup
            lRow = .Row
            iCol = .Column
            ThisLookup = .Value
            If ThisLookup = Prevlookup Then
                GoSub LoadArray
            Else
                If .Row > 2 Then
                    GoSub TransposeValues
                End If
                GoSub LoadArray
            End If
        End With
        Prevlookup = ThisLookup
    Next
    lRow = lRow + 1
    GoSub TransposeValues
ExitSub:
    Exit Sub
TransposeValues:
        Cells(lRow - 1, iCol + 3).Select
        For i = 0 To UBound(Values, 1) - 1
            ActiveCell = Values(i)
            ActiveCell.Offset(0, 1).Select
        Next i
        ReDim Values(0)
        index = 0
        Return
LoadArray:
        ReDim Preserve Values(index + 1)
        Values(index) = Cells(lRow, iCol + 1).Value
        index = index + 1
        Return
End Sub

Skip,
metzgsk@voughtaircraft.com

 

[Hasit]

I would consider ETID's answer first, although Skip's response is very full and detailed (gonna steal the code!).

 

[SkipVought]

He asked for a formula, not a step-by-step procedure. Skip,
metzgsk@voughtaircraft.com

 

[bryanbayfield]

Personally I feel that this code is a little more efficient, and would probably run faster (depending upon the amount of data you have):

Dim reqvals() As Integer, lkup As Integer
lkup = InputBox("For what value of column A do you want to copy vaues?")
num = 0
For i = 0 To Range("a1", Range("a1").End(xlDown)).Cells.Count - 1
    If Range("a1").Offset(i, 0).Value = lkup Then
        num = num + 1
        ReDim Preserve reqvals(num)
        reqvals(num) = Range("b1").Offset(i, 0).Value
        End If
    Next i
For j = 0 To num - 1
    Range("d1").Offset(0, j).Value = reqvals(j + 1)
    Next
End Sub

Bryan

 

[bryanbayfield]

PS: I don't think it's possible to have a formula which carries the job out - how could a formula fill across a row - not possible.

 

[Hasit]

Skip, sorry, no offence intended, I know a formula was asked for - yours is code, and if a transpose was required for a one shot deal, then the transpose function would work.

As I said, your answer is full and I will be using your code (which works well - thanks!).

 

[Waxaholic]

The VBA code works for me just fine. Thought maybe there was a way to do it with a formula but code is better. I appreciate the help everyone and thank you for the code SkipVought. I will put it to good use.

Much appreciated,

Brian