Convert matrix data to a list

eagleaye

New Member
Joined
Oct 15, 2013
Messages
24
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

New Member
Joined
Oct 15, 2013
Messages
24
Here is a sample of the data

March 2013Group 4
1/34/35/36/37/38/311/312/313/314/315/318/319/320/321/322/325/326/327/328/329/3
Item 1
Item 2111
Item 3
Item 411
Item 513
Item 611
Item 722

<tbody>
</tbody>
 
Last edited:

eagleaye

New Member
Joined
Oct 15, 2013
Messages
24
And this is what I'm hoping to achieve

ItemQuantity IssuedDate Issued
Item 10
Item 2114/03/2013
Item 2120/03/2013
Item 2126/03/2013
Item 30
Item 4112/03/2013
Item 4125/03/2013
Item51 1/3/13
Item 53 15/3/13
etc

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

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Results Sheet2
Code:
[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

New Member
Joined
Oct 15, 2013
Messages
24
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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

New Member
Joined
Oct 15, 2013
Messages
24
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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.
Code:
[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

New Member
Joined
Oct 15, 2013
Messages
24
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Is that all sheets in the workbook, and which sheet and where in the sheet would you like the results in ???
 

Forum statistics

Threads
1,082,275
Messages
5,364,181
Members
400,785
Latest member
Mahar92

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top