converting matrix data into a table

Maybe it's usefull to other members.

Now they can see, which option you already been given.

http://www.excelforum.com/excel-gene...html?p=2836995

Hi Red fuji,

Why can't you use Sheet1 only and use your autofilters. You could just click the dropdown and uncheck the blank. That would give you what you have on sheet2. It seems to me that sheet 2 is redundant and useless data because it is simply a repeat of sheet1.

The topic of Pivot Tables has been suggested but your data on sheet1 won't accomplish a pivot. Your data on sheet1 is more of a Cross Tab type of structure. To create a Pivot table from it you would need to move a bunch of stuff around.

I wrote some code a while back to convert Cross Tab to tables and run it on your sheet1. I've converted your data on Sheet3 and have done a Pivot Table on it to show you what others have been suggesting. I hope this helps you see why they like Pivot tables. See the attached with the code to convert your Cross Tab to a Table and the results of using a Pivot on Sheet3.

Hi,

Your sheet1 has the data arranged in a Cross Table format. Excel works best with a Table format like I've changed your data on my Sheet3 example. Your Cross Table data is not easily changed to what you desire. It would take VBA to get from what you have to what you want on sheet2. OR you could simply work with your data on sheet1 using the AutoFilter feature built into Excel.

I know you don't like my answer and want a simple formula to make your sheet1 data view like sheet2. You want a formula on sheet2 that displays the data like you have it. Sometimes there isn't a formula to do what you want. My best guess is to change your sheet1 data structure like I've shown you on sheet3 and do a Pivot Table from that data structure. If you don't like Pivots (like you've said in previous posts) then use sheet1 and the AutoFilter.

Another point that us "gurus" have is some of the "features" in excel don't work very well. The first is Merged Cells. These just make us work harder to do things but are there for beginners to make stuff look pretty. A second feature is Groups, like you use on your sheet2. They are very hard to work with using normal excel furnctions and are there for people who have to have "outline" type needs. Excel works best with TABLES of data, similar to your sheet1 but more exactly like my sheet3. Tables (not cross tables) give the real power to excel. When a user needs to have merged cells or outlines on their sheets it decreases the use of the data instead of enhancing it.

Sorry we couldn't get to where you wanted to go, using your sheet1 data structure. (now if you were to change it to my sheet3 structure....)

you are right about Merged cells causing problems even for simple tasks as auto filtering. that's why i dont use them and i dont have any in this file. as for grouping; i can bypass.

The formula would look at column G in sheet 1 to find populated cell; once it finds one it would then read adjacent cell in column A- that process would be repeated for the range of rows specified in the formula. Example: G51 has a value. therefore the formula will read A51. Once i have that basic formula i can manuly place it in sheet two and change it to read various cells in sheet one. I just need the basic formula. I am not looking for formulal that will set up the table structure- i will do that manually. I just need it to find populated cells in a specified column and report the adjacent cell.

OK Red fuji,

Find the attached where I've written some VBA. Click on the Run button and Sheet3 will be what you want. You will need to give it a title row and sort by Box numbers. There is no EASY Excel formula to do what you want. To do what you want using Excel simple formulas would need many helper formulas. This VBA should do what you want.

Marvin,
Oh boy- I know less about Macros; but I think it will do. Every time i rerun it I lose the column headings ?? Can you add headings to the code below and I can add it back to excel.

Sub RedFuji()
Dim LastRow As Double
Dim RowCtr As Double
Dim ColCtr As Double
Dim LastRow2 As Double

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
ActiveWorkbook.Worksheets("Sheet3").Cells.ClearContents

For RowCtr = 2 To LastRow
    For ColCtr = 7 To 31
        If Cells(RowCtr, ColCtr) <> "" Then
            With Worksheets("Sheet3")
                LastRow2 = .Cells(Rows.Count, "A").End(xlUp).Row + 1
                .Cells(LastRow2, "A") = Cells(1, ColCtr)
                .Cells(LastRow2, "B") = Cells(RowCtr, "A")
                .Cells(LastRow2, "C") = Cells(RowCtr, "B")
                .Cells(LastRow2, "D") = Cells(RowCtr, ColCtr)
                .Cells(LastRow2, "E") = Cells(RowCtr, "AI")
            End With
        End If
    Next ColCtr
Next RowCtr

End Sub

Here you go.

Please edit your above post and delete the VBA code or put it in Code Tags as per the forum rules.

Code tags will make it look better, like this:

Option Explicit

Sub RedFuji()
Dim LastRow As Double
Dim RowCtr As Double
Dim ColCtr As Double
Dim LastRow2 As Double

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

ActiveWorkbook.Worksheets("Sheet3").Cells.ClearContents
    With Worksheets("Sheet3")
        .Cells(1, "A") = "Box #"
        .Cells(1, "B") = "Part Name"
        .Cells(1, "C") = "Part #"
        .Cells(1, "D") = "Qty"
        .Cells(1, "E") = "Status"
    End With

For RowCtr = 2 To LastRow
    For ColCtr = 7 To 31
        If Cells(RowCtr, ColCtr) <> "" Then
            With Worksheets("Sheet3")
                LastRow2 = .Cells(Rows.Count, "A").End(xlUp).Row + 1
                .Cells(LastRow2, "A") = Cells(1, ColCtr)
                .Cells(LastRow2, "B") = Cells(RowCtr, "A")
                .Cells(LastRow2, "C") = Cells(RowCtr, "B")
                .Cells(LastRow2, "D") = Cells(RowCtr, ColCtr)
                .Cells(LastRow2, "E") = Cells(RowCtr, "AI")
            End With
        End If
    Next ColCtr
Next RowCtr

End Sub

Did I say that my help is free? "Don't go away" makes it sound like I work for you. You have no idea.....

I hope you can solve your problem now.

Hi,

You will need to attach the new sheet1 so I can see which columns you inserted. Then I'll step through the code and see what letter or cell address is wrong.

Thanks MarvinP

By many trials I managed to work that one out. I have additional data in sheet 1 that i want to incorporate but not having much success working into the code.

I added a description for each of the 25 Boxes as Shown in range C1:D26. The Box #s in M27:AK27 would be duplicate of C2:C26. i.e. C2=L27 and so on. can you please help me work that into the Macro?

Ok -

See if this works for you.

I added a VLookup to the VBA code to add your Box Description to the Sheet3. We'll make a programmer out of you in a while.