Excel question (1 Viewer)

[Drifter]

I have a matrix of data. Let's say Numbers down the left side 1-100 and letters across the top A-Z.

How do I take that and easily create a list of all combinations along with the value from the matrix?

Example of what I want returned:

1A Data

1B Data

1C Data

etc...

2A Data

2B Data

2C Data

etc...

Assume the dataset is too large to create any of this manually

 

[Urlacher54]

I have a matrix of data. Let's say Numbers down the left side 1-100 and letters across the top A-Z.How do I take that and easily create a list of all combinations along with the value from the matrix?Example of what I want returned:1A Data1B Data1C Dataetc...2A Data2B Data2C Dataetc...Assume the dataset is too large to create any of this manually

I assume you just want to convert a table to a listublic Sub TableToList() 'variables Dim tabela As Range Dim cabColunas As Range Dim cabLinhas As Range Dim nColunas As Integer Dim nLinhas As Integer Dim nCabColunas As Integer Dim nCabLinhas As Integer Dim i As Integer Dim j As Integer Set tabela = Application.InputBox("Select Data only - No headers", Type:=8) Set cabColunas = Application.InputBox("Select Column Labels)", Type:=8) Set cabLinhas = Application.InputBox("Select Row Labels", Type:=8) nColunas = cabColunas.Columns.Count nLinhas = cabLinhas.Rows.Count nCabColunas = cabColunas.Rows.Count nCabLinhas = cabLinhas.Columns.Count 'size check If cabColunas.Columns.Count <> tabela.Columns.Count Then MsgBox ("Column Header should have the same # of columns the table has") Exit Sub End If If cabLinhas.Rows.Count <> tabela.Rows.Count Then MsgBox ("Row Header should have the same # of rows the table has") Exit Sub End If 'creates sheet Worksheets.Add 'fills the list For i = 1 To nColunas * nLinhas Range("A" & i).Value = tabela(i).Value For j = 1 To nCabColunas If i Mod nColunas = 0 Then Cells(i, j + 1).Value = cabColunas(nColunas * j) Else: Cells(i, j + 1).Value = cabColunas(i Mod nColunas + nColunas * (j - 1)) End If Next For j = 1 To nCabLinhas Cells(i, j + 1 + nCabColunas).Value = cabLinhas(Int((i - 1) / nColunas) * nCabLinhas + j) Next Next End Sub

 

[Bottomfeeder Sports]

I assume you just want to convert a table to a list:

Wasn't that long ago you were asking, not answering these types of questions.

 

[bushdocda]

Wow I have no idea what any of that is and I tell people in interviews I am strong in excel.

 

[Drifter]

Wow I have no idea what any of that is and I tell people in interviews I am strong in excel.

VBA Macro

 

[PolishNorbi]

I have a matrix of data. Let's say Numbers down the left side 1-100 and letters across the top A-Z.How do I take that and easily create a list of all combinations along with the value from the matrix?Example of what I want returned:1A Data1B Data1C Dataetc...2A Data2B Data2C Dataetc...Assume the dataset is too large to create any of this manually

I assume you just want to convert a table to a listublic Sub TableToList() 'variables Dim tabela As Range Dim cabColunas As Range Dim cabLinhas As Range Dim nColunas As Integer Dim nLinhas As Integer Dim nCabColunas As Integer Dim nCabLinhas As Integer Dim i As Integer Dim j As Integer Set tabela = Application.InputBox("Select Data only - No headers", Type:=8) Set cabColunas = Application.InputBox("Select Column Labels)", Type:=8) Set cabLinhas = Application.InputBox("Select Row Labels", Type:=8) nColunas = cabColunas.Columns.Count nLinhas = cabLinhas.Rows.Count nCabColunas = cabColunas.Rows.Count nCabLinhas = cabLinhas.Columns.Count 'size check If cabColunas.Columns.Count <> tabela.Columns.Count Then MsgBox ("Column Header should have the same # of columns the table has") Exit Sub End If If cabLinhas.Rows.Count <> tabela.Rows.Count Then MsgBox ("Row Header should have the same # of rows the table has") Exit Sub End If 'creates sheet Worksheets.Add 'fills the list For i = 1 To nColunas * nLinhas Range("A" & i).Value = tabela(i).Value For j = 1 To nCabColunas If i Mod nColunas = 0 Then Cells(i, j + 1).Value = cabColunas(nColunas * j) Else: Cells(i, j + 1).Value = cabColunas(i Mod nColunas + nColunas * (j - 1)) End If Next For j = 1 To nCabLinhas Cells(i, j + 1 + nCabColunas).Value = cabLinhas(Int((i - 1) / nColunas) * nCabLinhas + j) Next Next End Sub

Just looking at it, without working on it, that is way over complicated.Give me a few minutes, and I'll create something.

 

[PolishNorbi]

Sub MatrixList()

Dim aData

Dim cList As New Collection

Dim i As Integer

Dim j As Long

Dim c As Range

aData = Range("a3").CurrentRegion

For j = 2 To UBound(aData, 1)

For i = 2 To UBound(aData, 2)

If Not IsEmpty(aData(j, i)) Then _

cList.Add aData(j, 1) & " " & aData(1, i) & " " & aData(j, i)

Next i

Next j

j = 0

For Each c In Range("a3").End(xlDown).Offset(, i).Resize(cList.Count, 1)

j = j + 1

c = cList(j)

Next c

End Sub

From ozgrid.com, not my work. Change A3 in "aData = Range("a3).CurrentRegion to a cell that is anywhere in the matrix.

 

[Urlacher54]

I assume you just want to convert a table to a list:

Wasn't that long ago you were asking, not answering these types of questions.

Still asking. I already had a file like this created. I got the basics off a Google search, and modified it to fit my spreadsheet. I still bow to your wisdom.

 

[hongdida]

I have a matrix of data. Let's say Numbers down the left side 1-100 and letters across the top A-Z. How do I take that and easily create a list of all combinations along with the value from the matrix? Example of what I want returned: 1A Data 1B Data 1C Data etc... 2A Data 2B Data 2C Data etc... Assume the dataset is too large to create any of this manually

No verified answer?

 

[Drifter]

IIRC Polish's VBA script will work

 

[Gawain]

Anyone got a good online tutorial to learn more about Excel?

 

[Ned]

Anyone got a good online tutorial to learn more about Excel?

mrexcel or ozgrid excel forums is more than most people will ever need.