Convert matrix data to a list

[eagleaye]

I have been using Excel to record the routine daily issue of items to different groups in a matrix layout, I use a different workbook for each month with worksheets for each group. The matrix takes the form of the item issued being the left hand column and the date issued the top row of the matrix, the quantity issued is recorded at the intersection. Each item can have a different quantity issued on different days.
I'm using Excel 2011 for Mac but could use PC Excel 2010. Is there a way to convert the data held in this way to a list?
What I'd like to achieve is a list showing the Item, the Quantities Issued and the the Issue dates, is this possible?

Any help appreciated

 

[eagleaye]

It would be all sheets in a workbook, the results could all go in one new sheet or in separate sheets as long as I could identify what sheet the data came from.
I have one workbook for each months records with an average of 30 worksheets in each.

 

[MickG]

You will need a sheet named "Summary" for the results
This code will look through all sheets in Workbook except sheet "Summary", where it will place all the results.
I have placed each sheet results in new columns. Can be altered if you wish.

[COLOR="Navy"]Sub[/COLOR] MG18Oct58
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] AcRng       [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ac          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Sht         [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Dim[/COLOR] col         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
col = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Sht [COLOR="Navy"]In[/COLOR] Worksheets
[COLOR="Navy"]If[/COLOR] Not Sht.Name = "Summary" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]With[/COLOR] Sht
        c = 2
        [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A3"), .Range("A" & Rows.Count).End(xlUp))
        [COLOR="Navy"]Set[/COLOR] AcRng = .Range(.Range("B2"), .Cells(2, Columns.Count).End(xlToLeft))
        ReDim Ray(1 To Rng.Count * AcRng.Count, 1 To 3)
        Ray(1, 1) = "Sht:= " & Sht.Name
        Ray(2, 1) = "Item": Ray(2, 2) = "Quantity": Ray(2, 3) = "Date"
        Temp = 0
        
        
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ac [COLOR="Navy"]In[/COLOR] Dn.Offset(, 1).Resize(, AcRng.Count)
                [COLOR="Navy"]If[/COLOR] Not IsEmpty(Ac) [COLOR="Navy"]Then[/COLOR]
                    c = c + 1
                    Ray(c, 1) = Dn
                    Ray(c, 2) = Ac
                    Ray(c, 3) = .Cells(2, Ac.Column)
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Ac
                    [COLOR="Navy"]If[/COLOR] Temp = c [COLOR="Navy"]Then[/COLOR]
                        c = c + 1
                        Ray(c, 1) = Dn
                        Ray(c, 2) = 0
                    [COLOR="Navy"]End[/COLOR] If
            Temp = c
        [COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]End[/COLOR] With


Sheets("Summary").Cells(1, col).Resize(c, 3) = Ray
col = col + 4
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Sht
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[eagleaye]

That's great Mick. It gave an error at first but once I removed a couple of sheets that weren't in the standard format it worked.

Is it possible to have the output as a continuous list with the worksheet name in a fourth column?
I'm getting some superfluous data and date format problems that could easily be edited by a column sort on a long list (or maybe by defining it to just run on columns A-Z)

Many Thanks

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG18Oct42
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] AcRng       [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ac          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Sht         [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Dim[/COLOR] Tempcount   [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
c = 1
[COLOR="Navy"]Dim[/COLOR] Ray()


[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Sht [COLOR="Navy"]In[/COLOR] Worksheets
[COLOR="Navy"]If[/COLOR] Not Sht.Name = "Summary" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]With[/COLOR] Sht
      
        [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A3"), .Range("A" & Rows.Count).End(xlUp))
        [COLOR="Navy"]Set[/COLOR] AcRng = .Range(.Range("B2"), .Cells(2, Columns.Count).End(xlToLeft))
        Tempcount = Tempcount + Rng.Count * AcRng.Count
        
        ReDim Preserve Ray(1 To 4, 1 To Tempcount)
        Ray(1, 1) = "Item": Ray(2, 1) = "Quantity": Ray(3, 1) = "Date": Ray(4, 1) = "Sht.Name"
        Temp = 0
        
        
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ac [COLOR="Navy"]In[/COLOR] Dn.Offset(, 1).Resize(, AcRng.Count)
                [COLOR="Navy"]If[/COLOR] Not IsEmpty(Ac) [COLOR="Navy"]Then[/COLOR]
                    c = c + 1
                    Ray(1, c) = Dn
                    Ray(2, c) = Ac
                    Ray(3, c) = CDbl(DateValue(.Cells(2, Ac.Column)))
                    Ray(4, c) = Sht.Name
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Ac
                    [COLOR="Navy"]If[/COLOR] Temp = c [COLOR="Navy"]Then[/COLOR]
                        c = c + 1
                        Ray(1, c) = Dn
                        Ray(2, c) = 0
                        Ray(4, c) = Sht.Name
                    [COLOR="Navy"]End[/COLOR] If
            Temp = c
        [COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Sht
[COLOR="Navy"]With[/COLOR] Sheets("Summary")
    .Range("A1").Resize(c, 4) = Application.Transpose(Ray)
    .Columns("C:C").NumberFormat = "dd/mm/yyyy"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[eagleaye]

Hello Mick
I'm getting runtime error 13: Type mismatch with this, if I click on debug it highlights this: Ray(3, c) = CDbl(DateValue(.Cells(2, Ac.Column)))

 

[eagleaye]

Thanks so much Mick, I deleted some differently arranged data from my workbooks and it now works brilliantly. It makes the date year 2017 but that's easily replaced.
I just wish I could find the time to learn Visual Basic, it would be really useful to me. As a matter of interest would VB be a better way to solve my problem on another thread I started?

http://www.mrexcel.com/forum/excel-...olumn-name-return-different-column-value.html