Transpose Data to List

Pestomania · Sep 15, 2023

Below is a table that I am working with. Column A:X is the standard table I am working with. Cells B2:H2 are the "day" of the month. Cells A4:U39 are the machines and hours that I need to review. I need to transpose all fields that are not 0 between B4:H39 and O4:U39 over to a running table as you can see in AB2:AD82. I am hoping I am explaining this well enough.




9/14/2023	8	9	10	11	12	13	14					9/14/2023	8	9	10	11	12	13	14				Machine 4	9/8/2023	24
Tool Name	F	S	S	M	T	W	T	Total Up	Total Dn	% Up	Notes	Tool Name	F	S	S	M	T	W	T	Total Up	Total Dn	% Up	Machine 4	9/9/2023	24
Machine 3	0	0	0	0	0	0	0	168	0	100.0%		Machine 39	0	0	0	0	0	0	0	168	0	100.0%	Machine 4	9/10/2023	24
Machine 4	24	24	24	24	24	24	24	0	168	0.0%		Machine 40	0	0	0	0	0	0	0	168	0	100.0%	Machine 4	9/11/2023	24
Machine 5	24	24	24	24	24	24	24	0	168	0.0%		Machine 41	0	0	0	0	0	0	0	168	0	100.0%	Machine 4	9/12/2023	24
Machine 6	0	0	0	0	0	0	0	168	0	100.0%		Machine 42	0	0	0	0	0	0	0	168	0	100.0%	Machine 4	9/13/2023	24
Machine 7	0	0	0	0	0	0	0	168	0	100.0%		Machine 43	0	0	0	0	0	0	0	168	0	100.0%	Machine 4	9/14/2023	24
Machine 8	0	0	0	0	0	0	0	168	0	100.0%		Machine 44	24	24	24	24	24	24	0	24	144	14.3%	Machine 5	9/8/2023	24
Machine 9	0	0	0	0	0	0	0	168	0	100.0%		Machine 45	24	24	24	24	24	24	24	0	168	0.0%	Machine 5	9/9/2023	24
Machine 10	0	0	0	0	0	0	0	168	0	100.0%		Machine 46	0	0	0	0	0	0	0	168	0	100.0%	Machine 5	9/10/2023	24
Machine 11	0	0	0	0	0	0	0	168	0	100.0%		Machine 47	0	0	0	0	0	0	0	168	0	100.0%	Machine 5	9/11/2023	24
Machine 12	0	0	0	0	0	0	0	168	0	100.0%		Machine 48	0	0	0	0	0	0	0	168	0	100.0%	Machine 5	9/12/2023	24
Machine 13	0	0	0	0	0	0	0	168	0	100.0%		Machine 49	0	0	0	0	0	0	0	168	0	100.0%	Machine 5	9/13/2023	24
Machine 14	0	0	0	0	0	0	0	168	0	100.0%		Machine 50	0	0	0	0	0	0	0	168	0	100.0%	Machine 5	9/14/2023	24
Machine 15	0	0	0	0	0	0	0	168	0	100.0%		Machine 51	0	0	0	0	0	0	0	168	0	100.0%	Machine 16	9/8/2023	24
Machine 16	24	24	24	24	24	24	24	168	0	0.0%		Machine 52	0	0	0	0	0	0	0	168	0	100.0%	Machine 16	9/9/2023	24
Machine 17	24	24	24	24	24	24	24	168	0	0.0%		Machine 53	0	0	0	0	0	0	0	168	0	100.0%	Machine 16	9/10/2023	24
Machine 18	24	24	24	24	24	24	24	168	0	0.0%		Machine 54	0	0	0	0	0	0	0	168	0	100.0%	Machine 16	9/11/2023	24
Machine 19	24	24	24	24	24	24	24	0	168	0.0%		Machine 55	0	0	0	0	0	0	0	168	0	100.0%	Machine 16	9/12/2023	24
Machine 20	0	0	0	0	0	0	0	168	0	100.0%		Machine 56	0	0	0	0	0	0	0	168	0	100.0%	Machine 16	9/13/2023	24
Machine 21	0	0	0	0	0	0	0	168	0	100.0%		Machine 57	0	0	0	0	0	0	0	168	0	100.0%	Machine 16	9/14/2023	24
Machine 22	24	24	24	24	24	24	24	168	0	100.0%		Machine 58	0	0	0	0	0	0	0	168	0	100.0%	Machine 17	9/8/2023	24
Machine 23	5	0	0	0	16	0	0	147	21	87.5%		Machine 59	0	0	0	0	0	0	0	168	0	100.0%	Machine 17	9/9/2023	24
Machine 24	0	0	0	0	0	0	0	168	0	100.0%		Machine 60	0	0	0	0	0	0	0	168	0	100.0%	Machine 17	9/10/2023	24
Machine 25	0	11	8	0	0	0	0	149	19	88.7%		Machine 61	0	0	0	0	0	0	0	168	0	100.0%	Machine 17	9/11/2023	24
Machine 26	5	6	0	0	0	0	0	157	11	93.5%		Machine 62	0	0	0	0	0	0	0	168	0	100.0%	Machine 17	9/12/2023	24
Machine 27	24	8	0	0	0	0	0	136	32	81.0%		Machine 63	0	0	0	0	0	0	0	168	0	100.0%	Machine 17	9/13/2023	24
Machine 28												Machine 64	24	24	24	24	24	24	24	168	0	100.0%	Machine 17	9/14/2023	24
Machine 29												Machine 65	0	0	0	0	0	0	0	168	0	100.0%	Machine 18	9/8/2023	24
Machine 30												Machine 66	0	0	0	0	0	0	0	168	0	100.0%	Machine 18	9/9/2023	24
Machine 31												Machine 67	0	0	0	0	0	0	0	168	0	100.0%	Machine 18	9/10/2023	24
Machine 32	0	0	0	0	0	0	0	168	0	100.0%													Machine 18	9/11/2023	24
Machine 33	0	0	0	0	0	0	0	168	0	100.0%													Machine 18	9/12/2023	24
Machine 34	5	24	24	8	0	0	0	107	61	63.7%													Machine 18	9/13/2023	24
Machine 35	0	0	0	0	0	0	0	168	0	100.0%			0	0	0	0	0	0	0	168	0	100.0%	Machine 18	9/14/2023	24
Machine 36	0	0	0	0	0	0	0	168	0	100.0%			0	0	0	0	0	0	0	168	0	100.0%	Machine 19	9/8/2023	24
Machine 37	0	0	0	0	0	0	0	168	0	100.0%			0	0	0	0	0	0	0	168	0	100.0%	Machine 19	9/9/2023	24
Machine 38	0	0	0	0	0	0	0	168	0	100.0%			0	0	0	0	0	0	0	168	0	100.0%	Machine 19	9/10/2023	24
																							Machine 19	9/11/2023	24
																							Machine 19	9/12/2023	24
																							Machine 19	9/13/2023	24
																							Machine 19	9/14/2023	24
																							Machine 22	9/8/2023	24
																							Machine 22	9/9/2023	24
																							Machine 22	9/10/2023	24
																							Machine 22	9/11/2023	24
																							Machine 22	9/12/2023	24
																							Machine 22	9/13/2023	24
																							Machine 22	9/14/2023	24
																							Machine 23	9/8/2023	5
																							Machine 23	9/12/2023	16
																							Machine 25	9/9/2023	11
																							Machine 25	9/10/2023	8
																							Machine 26	9/8/2023	5
																							Machine 26	9/9/2023	6
																							Machine 27	9/8/2023	24
																							Machine 27	9/9/2023	8
																							Machine 34	9/8/2023	5
																							Machine 34	9/9/2023	24
																							Machine 34	9/10/2023	24
																							Machine 34	9/11/2023	8
																							Machine 44	9/8/2023	24
																							Machine 44	9/9/2023	24
																							Machine 44	9/10/2023	24
																							Machine 44	9/11/2023	24
																							Machine 44	9/12/2023	24
																							Machine 44	9/13/2023	24
																							Machine 45	9/8/2023	24
																							Machine 45	9/9/2023	24
																							Machine 45	9/10/2023	24
																							Machine 45	9/11/2023	24
																							Machine 45	9/12/2023	24
																							Machine 45	9/13/2023	24
																							Machine 45	9/14/2023	24
																							Machine 64	9/8/2023	24
																							Machine 64	9/9/2023	24
																							Machine 64	9/10/2023	24
																							Machine 64	9/11/2023	24
																							Machine 64	9/12/2023	24
																							Machine 64	9/13/2023	24
																							Machine 64	9/14/2023	24

Fluff · Sep 15, 2023

How about

Excel Formula:

=LET(a,VSTACK(A4:H39,N4:U39),b,TAKE(a,,1),c,DROP(a,,1),HSTACK(TOCOL(IF(c<>0,b,1/0),2),TOCOL(IF(c<>0,DATE(YEAR(A2),MONTH(A2),B2:H2),1/0),2),TOCOL(IF(c<>0,c,1/0),2)))

Pestomania · Sep 18, 2023

Fluff said:

How about

Excel Formula:

=LET(a,VSTACK(A4:H39,N4:U39),b,TAKE(a,,1),c,DROP(a,,1),HSTACK(TOCOL(IF(c<>0,b,1/0),2),TOCOL(IF(c<>0,DATE(YEAR(A2),MONTH(A2),B2:H2),1/0),2),TOCOL(IF(c<>0,c,1/0),2)))

This is awesome! This workbook has maybe 100 sheets with the saem exact format. Is there possibly a way to use this to create one large table for ALL sheets?

Fluff · Sep 18, 2023

Are the all the sheets consecutive & are the dates in A2 & the numbers in B2:H2 the same on all sheets?

Pestomania · Sep 18, 2023

Fluff said:
Are the all the sheets consecutive & are the dates in A2 & the numbers in B2:H2 the same on all sheets?

Hi Fluff,

There were a few modifications I ended up doing, but I was able to get it to run using dummy code seen below:

Excel Formula:

=LET(a,VSTACK(A12:H59,N12:U55),b,TAKE(a,,1),c,DROP(a,,1),HSTACK(TOCOL(IF(c<>0,b,1/0),2),TOCOL(IF(c<>0,DATE(YEAR(A10),MONTH(A10),B10:H10),1/0),2),TOCOL(IF(c<>0,c,1/0),2)))

Then I ensure all worksheets were named with a date and placed that date in A10:

VBA Code:

Sub Redate()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    
If ws.Name = "Data Table" Then

Exit Sub

Else
    
    ws.Select
   Range("A10").Value = (ActiveSheet.Name)

End If

Next ws
End Sub

And then after that I added the original formula you provided to AC2 for all sheets by selecting all insert. Then I ran a code to find all of those values and add them to a "Data Tables" tab.

VBA Code:

Sub Copy_All_Date()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    
    If ActiveSheet.Name = "Data Table" Then
    
    Exit Sub
    
    Else
    
        lastrow = Range("AC" & Rows.Count).End(xlUp).Row
        Range("AC2:AE" & lastrow).Copy
        
        'Sheets("Data Table").Select
        
        lastrow2 = Sheets("Data Table").Range("A" & Rows.Count).End(xlUp).Row
        Sheets("Data Table").Range("A" & lastrow2 + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
    Sheets(ActiveSheet.Index + 1).Activate
    
    End If
    
    
Next ws


End Sub

Last thing I had to do was remove duplicates that I don't know why or where they came from. But it got me 18000 lines of data I needed.

Thank you!

Fluff · Sep 19, 2023

Glad you sorted it & thanks for the feedback.

Transpose Data to List

Pestomania

Active Member

Excel Facts

Fluff

MrExcel MVP, Moderator

Pestomania

Active Member

Fluff

MrExcel MVP, Moderator

Pestomania

Active Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Transpose Data to List

Pestomania

Active Member

Excel Facts

Fluff

MrExcel MVP, Moderator

Pestomania

Active Member

Fluff

MrExcel MVP, Moderator

Pestomania

Active Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock