Convert Multiple rows and columns into individual rows for each col head with data

[spb]

Hello. I have large spreadsheets that have thousands of rows and in some cases almost 100 columns. At the intersections of some rows and columns are data. I am looking to flatten that big matrix out to make an individual row for each row & col intersection that contains data:

Take
model 1 model 2 model 3 model 4 model 5
name 1 2 7 4
name 2 3 5 3
name 3 2 6 9
name 4 1 5 7
name 5 6 8 12

and make
name 1 model 2 2
name 1 model 4 7
name 1 model 5 4
name 2 model 1 3
name 2 model 3 5
name 2 model 5 3
name 3 model 2 2
name 3 model 3 6
name 3 model 4 9
name 4 model 1 1
name 4 model 3 5
name 4 model 5 7
name 5 model 3 6
name 5 model 4 8
name 5 model 5 12

 

[Puertorekinsam]

You can do this with a nested do loop

Sub flattenWorkSheet()
Application.ScreenUpdating = False
RowCount = 2
PrintRow = 1

Do Until Sheet1.Cells(RowCount, 1) = ""
    ColumnCount = 2
    
    Do Until Sheet1.Cells(1, ColumnCount) = ""
    
        If Sheet1.Cells(RowCount, ColumnCount) <> "" Then
            Sheet2.Cells(PrintRow, 1) = Sheet1.Cells(RowCount, 1) & " " & Sheet1.Cells(RowCount, ColumnCount) & Sheet1.Cells(RowCount, ColumnCount)
            PrintRow = PrintRow + 1
        
        End If
        
        ColumnCount = ColumnCount + 1
    Loop


    RowCount = RowCount + 1
Loop

Application.ScreenUpdating = True
End Sub

 

[spb]

Thank you for the code. I created the macro but can't seem to get it to run. Do I need to select the range then run the macro or something like that?

 

[Puertorekinsam]

the macro should run when you select it from the run macro dialogue box.

I had to makse some assumptions :

the data is in Sheet1
The table headers are in column A and row 1
There is a sheet 2 to put the 'clean' data in

if these are not correct, some adjustment will have to be made.

 

[spb]

OK. So I got it to run but would like one modification. Could I get the output to insert the elements in different columns; A, B, & C?
name 1 in Col A, model 2 in Col B, and the data element (2) in column C?

 

[Puertorekinsam]

Sub flattenWorkSheet()
Application.ScreenUpdating = False
RowCount = 2
PrintRow = 1

Do Until Sheet1.Cells(RowCount, 1) = ""
    ColumnCount = 2
    
    Do Until Sheet1.Cells(1, ColumnCount) = ""
    
        If Sheet1.Cells(RowCount, ColumnCount) <> "" Then
            Sheet2.Cells(PrintRow, 1) = Sheet1.Cells(RowCount, 1)
            Sheet2.Cells(PrintRow, 2) = Sheet1.Cells(1, ColumnCount)
            Sheet2.Cells(PrintRow, 3) = Sheet1.Cells(RowCount, ColumnCount)
            PrintRow = PrintRow + 1
        
        End If
        
        ColumnCount = ColumnCount + 1
    Loop


    RowCount = RowCount + 1
Loop

Application.ScreenUpdating = True
End Sub

 

[spb]

I do believe that did the trick. That is awesome and will save me a lot of work. The magic of excel never ceases to amaze me. My VB knowledge needs some work.
Thank you very much for this. I truly appreciate it.