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]

Here is a sample of the data

March 2013

Group 4

1/3

4/3

5/3

6/3

7/3

8/3

11/3

12/3

13/3

14/3

15/3

18/3

19/3

20/3

21/3

22/3

25/3

26/3

27/3

28/3

29/3

Item 1

Item 2

1

1

1

Item 3

Item 4

1

1

Item 5

1

3

Item 6

1

1

Item 7

2

2

<tbody>
</tbody>

 

[eagleaye]

And this is what I'm hoping to achieve

Item	Quantity Issued	Date Issued
Item 1	0
Item 2	1	14/03/2013
Item 2	1	20/03/2013
Item 2	1	26/03/2013
Item 3	0
Item 4	1	12/03/2013
Item 4	1	25/03/2013
Item5	1	1/3/13
Item 5	3	15/3/13
etc

<colgroup><col span="3"></colgroup><tbody>
</tbody>

 

[MickG]

Try this:-
Results Sheet2

[COLOR="Navy"]Sub[/COLOR] MG16Oct00
[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]
c = 1


[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A4"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] AcRng = Range(Range("B3"), Cells(3, Columns.Count).End(xlToLeft))
ReDim Ray(1 To Rng.Count * AcRng.Count, 1 To 3)
Ray(c, 1) = "Item": Ray(c, 2) = "Quantity": Ray(c, 3) = "Date"


[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(3, 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
Sheets("Sheet2").Range("A1").Resize(c, 3) = Ray


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[eagleaye]

Thanks for the reply Mick
I've never used VB before and have no idea how to attempt to make it work.
I copied the above a tried to run it as a macro but getting a runtime error 9, subscript out of range message. How do I go about getting it to work and is it besy run from Windows Excel rather than Mac?

 

[MickG]

Try this:-
Make sure you have a sheet2 that is not your data sheet i.e sheet2 should be empty
To Save and Run code:-
Copy code from Thread
In your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the Vb window toolbar, Click "Insert" , " Module":- New Vb window appears .
Paste Code into this window.
Close VB Window
On active sheet select "Developer" tab.
From Ribbon , select "Macro":- Macro dialog box Appears.
Select Code Name from list.
On right of Dialog Box , Select "Run".
Sheet2 should now be Updated.
Regrds Mick

 

[eagleaye]

Progress made but it's only populating the item and quantity, not the date. It also appears that it's only finding a single entry of each item in the first column only.
If I run it in a different sheet with the same general format I get multiple instances of items but not the date or quantity information. I don't know enough about VB to work out what's happening.

 

[MickG]

I think the problem is the position of the sheet layout, I took the first line to be row 2, whereas your data showed it in row 1.
Try this:-
The code now expects the first line to be row 1 . Cell "A1" Has "March 2013" in and the first "Item" in column "A" "Item 1" is in row 3. Just as you originally showed.

[COLOR=navy]Sub[/COLOR] MG18Oct19
[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]
c = 1
[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(c, 1) = "Item": Ray(c, 2) = "Quantity": Ray(c, 3) = "Date"
[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
Sheets("Sheet2").Range("A1").Resize(c, 3) = Ray
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[eagleaye]

That worked a treat Mick, Thanks.
Is there any way to get it to run across an entire workbook with several worksheets recording the name of each sheet the data comes from?

 

[MickG]

Is that all sheets in the workbook, and which sheet and where in the sheet would you like the results in ???