Convert matrix data to a list

E

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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Here is a sample of the data

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]March 2013[/TD]
[TD="colspan: 21, align: center"]Group 4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/3[/TD]
[TD]4/3[/TD]
[TD]5/3[/TD]
[TD]6/3[/TD]
[TD]7/3[/TD]
[TD]8/3[/TD]
[TD]11/3[/TD]
[TD]12/3[/TD]
[TD]13/3[/TD]
[TD]14/3[/TD]
[TD]15/3[/TD]
[TD]18/3[/TD]
[TD]19/3[/TD]
[TD]20/3[/TD]
[TD]21/3[/TD]
[TD]22/3[/TD]
[TD]25/3[/TD]
[TD]26/3[/TD]
[TD]27/3[/TD]
[TD]28/3[/TD]
[TD]29/3[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 5[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
And this is what I'm hoping to achieve

[TABLE="class: grid, width: 195"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Quantity Issued[/TD]
[TD]Date Issued[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14/03/2013[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20/03/2013[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]26/03/2013[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Item 4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12/03/2013[/TD]
[/TR]
[TR]
[TD]Item 4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25/03/2013[/TD]
[/TR]
[TR]
[TD]Item5[/TD]
[TD]1[/TD]
[TD] 1/3/13[/TD]
[/TR]
[TR]
[TD]Item 5[/TD]
[TD]3[/TD]
[TD] 15/3/13[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Is that all sheets in the workbook, and which sheet and where in the sheet would you like the results in ???
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Matching an ID from 2 columns in 2 worksheets to then produce the figure from a 3rd column.
Replies
4
Views
316
Pavesib
P
FrumpyJones
  • Question Question
Is there a way to stop COPY/PASTE but still allow manual entries and use of drop down lists.
Replies
5
Views
253
FrumpyJones
FrumpyJones
H
  • Question Question
List out duplicate items in a list with different costs/values
Replies
6
Views
149
H3MSY
H
S
  • Question Question
VBA code to determine InDesign Layout
Replies
6
Views
442
Akuini
Akuini
wizau
  • Solved
Nested IF statements, or a better way?
Replies
4
Views
328
wizau
wizau

Forum statistics

Threads
1,223,886
Messages
6,175,194
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top