CODEWITHSUNDEEP
excelgoogle-sheetsspreadsheet
mmseng
mmseng

Reputation: 850

Spreadsheet formula to dynamically transpose 1st column to 1st row?

Hi I'm trying to make an Excel 2013 spreadsheet that lists possible combinations of items. I'm not sure if there's a name for this type of table, besides a matrix, but it looks like this (like a Mendelian genetics diagram):

     A       B       C        D       E
1          Apple   Banana   Cherry
2  Apple   Blue    Orange   Red
3  Banana  Red     Purple   Brown
4  Cherry  Green   Red      Maroon
5

I will be adding items to it as necessary and manually filling in the combination results. I'm looking for a way to simply enter a new item at the bottom of the 1st column and have it automatically show up at the end of the 1st row. For example I would enter "Date" in cell A5, and "Date" would also show up automatically in cell E1.

I've tried simply entering

=A2

in cell B1, and then dragging that formula to the right in row 1, but instead of filling in =A3, =A4, =A5, etc., it fills in =B2, =C2, =D2, etc. This behavior makes sense, by default, but I don't know how to change it. I thought maybe the TRANSPOSE function would help me somehow, but I haven't had any luck.

I'm also willing to use a Google Docs spreadsheet, if it happens to have a better method.

Edit: It's also occurred to me that perhaps there is a better way to layout this kind of table. I was really hoping there's would be some sort of default template built into Excel for this sort of thing, with options to gray out duplicate combinations etc.

Thanks for any suggestions.

Upvotes: 0

Views: 4927

Answers (3)

Eduardo Silva
Eduardo Silva

Reputation: 1

Google Spreadsheets has a neat function that does this much easier: On cell B1, enter: =transpose(A2:A100)

That is it! (if you are planning to have more than 99 rows, adjust accordingly)

Upvotes: 0

aebailey
aebailey

Reputation: 195

Try this formula in B1 and then drag to the right: 

=OFFSET(B1,COLUMN(B1)-1,-(COLUMN(B1)-1))

Upvotes: 3

Stepan1010
Stepan1010

Reputation: 3136

Google docs is not likely to offer better functionality than Excel - it often has much worse functionality in it's current state. It's great if you are on the run and don't have access to Excel or are working on a real time collaborative spreadsheet.

If you are looking to get combinations that include blank as a value and have more than 2 possible values, you are better off using VBA. This is from http://www.mrexcel.com/forum/excel-questions/435865-excel-visual-basic-applications-combinations-permutations.html:

Sub PowerSetRept()
Dim vElements As Variant, vresult As Variant
Dim lRow As Long, i As Long

vElements = Application.Transpose(Range("A1", Range("A1").End(xlDown)))
Columns("C:Z").Clear

lRow = 1
For i = 1 To UBound(vElements)
    ReDim vresult(1 To i)
    Call CombinationsNP(vElements, i, vresult, lRow, 1, 1)
Next i
End Sub

Sub CombinationsNP(vElements As Variant, p As Long, vresult As Variant, lRow As Long, iElement As Long, iIndex As Long)
Dim i As Long

For i = iElement To UBound(vElements)
    vresult(iIndex) = vElements(i)
    If iIndex = p Then
        lRow = lRow + 1
        Range("C" & lRow).Resize(, p) = vresult
    Else
        Call CombinationsNP(vElements, p, vresult, lRow, i, iIndex + 1)
    End If
Next i
End Sub

You will get something like this:

Example2

Upvotes: 0

Related Questions