Home can I get row data to column data?

[HockeyDiablo]

Home can I get row data to column data? The information can only be whats listed on the top of the instance. Each instance is separated by a blank row (10,20)

I will provide 3 instances as a reference.

[/IMG]

 

[sheetspread]

Something like this?

Excel 2010
	A	B	C	D	E
1			Work Date:	4/1/2016	Total Hours:
2			Work Order	Customer name	Appointment Type
3	4/1/2016	Aaron Thompson	-	Aaron Thompson	BLANK
4	4/1/2016	Aaron Thompson	120015	Charles Frank	NC-Remote/Keyless/Prog.
5	4/1/2016	Aaron Thompson	120015	Charles Frank	NC-Remote/Keyless/Prog.
6	4/1/2016	Aaron Thompson	120015	Charles Frank	NC-Remote/Keyless/Prog.
7	4/1/2016	Aaron Thompson	119408	Tammy Bauer	NC-New_Door_Estimate
8	4/1/2016	Aaron Thompson	119408	Tammy Bauer	NC-New_Door_Estimate
9	4/1/2016	Aaron Thompson	119408	Tammy Bauer	NC-New_Door_Estimate
10	4/1/2016	Aaron Thompson	120087	Robin Ellingson	NC-Wont_Open/Need_to_Assist
11	4/1/2016	Aaron Thompson	120087	Robin Ellingson	NC-Wont_Open/Need_to_Assist
12	4/1/2016	Aaron Thompson	120087	Robin Ellingson	NC-Wont_Open/Need_to_Assist
13	4/1/2016	Aaron Thompson	120092	David Swenson	NC-Opener_Repair_(G&S_-_SS_-_Chain_-_ETC)
14	4/1/2016	Aaron Thompson	120092	David Swenson	NC-Opener_Repair_(G&S_-_SS_-_Chain_-_ETC)
15	4/1/2016	Aaron Thompson	120092	David Swenson	NC-Opener_Repair_(G&S_-_SS_-_Chain_-_ETC)
16	4/1/2016	Aaron Thompson	120074	Lourdas Nehring	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
17	4/1/2016	Aaron Thompson	120074	Lourdas Nehring	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
18	4/1/2016	Aaron Thompson	120074	Lourdas Nehring	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
19	4/1/2016	Aaron Thompson	120119	Vance Vang	RC-Door/Track_Hit
20	4/1/2016	Aaron Thompson	120119	Vance Vang	RC-Door/Track_Hit
21	4/1/2016	Aaron Thompson	120119	Vance Vang	RC-Door/Track_Hit
22	4/1/2016	Aaron Thompson	120036	Tabitha Barnes	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
23	4/1/2016	Aaron Thompson	120036	Tabitha Barnes	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
24	4/1/2016	Aaron Thompson	120036	Tabitha Barnes	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
25	4/1/2016	Aaron Thompson	120125	Mary Montury	*DI-WMA
Sheet0

Cell Formulas
Range	Formula
A3	=IF(AND(C3<>"-",ISNUMBER(C3)=FALSE),"",IF(D2="Customer name",D1,A2))
B3	=IF(AND(C3<>"-",ISNUMBER(C3)=FALSE),"",IF(D2="Customer name",D3,B2))

 

[HockeyDiablo]

Doesnt change the Date or The customer name when they change, thanks tho, I saw where you were going with that

think this would be more of a VBA vs Formula?

 

[sheetspread]

Note that if the dates in column D aren't interpreted by Excel as numbers, you can highlight the column, ctrl-h, replace / with /


I also think your file might be set to manual calculation (only changes when saved or F9 is pressed). Click on file-options-formulas-automatic.

 

[skywriter]

With your sample file I renamed the before sheet to example.

Here's the sheet before and after, with the code below.

Excel Workbook
	A	B	C
1	Work Date:	4/1/2016	Total Hours:
2	Work Order	Customer name	Appointment Type
3	-	Aaron Thompson	BLANK
4	120015	Charles Frank	NC-Remote/Keyless/Prog.
5	120015	Charles Frank	NC-Remote/Keyless/Prog.
6	120015	Charles Frank	NC-Remote/Keyless/Prog.
7	119408	Tammy Bauer	NC-New_Door_Estimate
8	119408	Tammy Bauer	NC-New_Door_Estimate
9	119408	Tammy Bauer	NC-New_Door_Estimate
10	120087	Robin Ellingson	NC-Wont_Open/Need_to_Assist
11	120087	Robin Ellingson	NC-Wont_Open/Need_to_Assist
12	120087	Robin Ellingson	NC-Wont_Open/Need_to_Assist
13	120092	David Swenson	NC-Opener_Repair_(G&S_-_SS_-_Chain_-_ETC)
14	120092	David Swenson	NC-Opener_Repair_(G&S_-_SS_-_Chain_-_ETC)
15	120092	David Swenson	NC-Opener_Repair_(G&S_-_SS_-_Chain_-_ETC)
16	120074	Lourdas Nehring	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
17	120074	Lourdas Nehring	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
18	120074	Lourdas Nehring	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
19	120119	Vance Vang	RC-Door/Track_Hit
20	120119	Vance Vang	RC-Door/Track_Hit
21	120119	Vance Vang	RC-Door/Track_Hit
22	120036	Tabitha Barnes	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
23	120036	Tabitha Barnes	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
24	120036	Tabitha Barnes	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
25	120125	Mary Montury	*DI-WMA
26	120125	Mary Montury	*DI-WMA
27	120125	Mary Montury	*DI-WMA
28	-	Aaron Thompson	BLANK
29	120036	Tabitha Barnes	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
30	120036	Tabitha Barnes	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
31
32	Work Date:	4/2/2016	Total Hours:
33	Work Order	Customer name	Appointment Type
34	-	Jon Doe	BLANK
35	120127	Tom Peterson	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
36	120127	Tom Peterson	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
37	120127	Tom Peterson	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
38	120062	Jenny Treuenfels	NC-CBL_Broken/Crooked/Off_Track/Roller_Out
39	120062	Jenny Treuenfels	NC-CBL_Broken/Crooked/Off_Track/Roller_Out
40	120062	Jenny Treuenfels	NC-CBL_Broken/Crooked/Off_Track/Roller_Out
41	120068	William Wood	*Special/Quoted_Pricing_(See_Notes)
42	120068	William Wood	*Special/Quoted_Pricing_(See_Notes)
43	120068	William Wood	*Special/Quoted_Pricing_(See_Notes)
44	120129	Bryce Carlson	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
45	120129	Bryce Carlson	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
46	120129	Bryce Carlson	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
47	120121	Paul Commers	RC-Opener_Repair_(G&S-SS-Chain-ETC)
48	120121	Paul Commers	RC-Opener_Repair_(G&S-SS-Chain-ETC)
49	120121	Paul Commers	RC-Opener_Repair_(G&S-SS-Chain-ETC)
50	120143	Blair Larson	NC-Opener_Repair_(G&S_-_SS_-_Chain_-_ETC)
51	120143	Blair Larson	NC-Opener_Repair_(G&S_-_SS_-_Chain_-_ETC)
52	120143	Blair Larson	NC-Opener_Repair_(G&S_-_SS_-_Chain_-_ETC)
53	120149	Deann Christensen	NC-Door/Track_Hit
54	120149	Deann Christensen	NC-Door/Track_Hit
55	120149	Deann Christensen	NC-Door/Track_Hit
56	-	Jon Doe	BLANK
57
58	Work Date:	4/4/2016	Total Hours:
59	Work Order	Customer name	Appointment Type
60	-	Emily Jean	BLANK
61	120177	John Christiansen	NC-Wont_Open/Need_to_Assist
62	120177	John Christiansen	NC-Wont_Open/Need_to_Assist
63	120177	John Christiansen	NC-Wont_Open/Need_to_Assist
64	120154	Marian Brundage	*Service_WMA
65	120154	Marian Brundage	*Service_WMA
66	120154	Marian Brundage	*Service_WMA
67	120122	Allyn Zelenka	NC-Tune_up/Adjustment
68	120122	Allyn Zelenka	NC-Tune_up/Adjustment
69	120122	Allyn Zelenka	NC-Tune_up/Adjustment
70	120181	Calvin Hall	*Service_WMA
71	120181	Calvin Hall	*Service_WMA
72	120181	Calvin Hall	*Service_WMA
73	120194	Burnsville Shop - Read Appointment Notes	*Instruction/Note
74	120194	Burnsville Shop - Read Appointment Notes	*Instruction/Note
75	120194	Burnsville Shop - Read Appointment Notes	*Instruction/Note
76	120265	Dave Schaefer	RC-Spring_Replacement_(SPNQ)
77	120265	Dave Schaefer	RC-Spring_Replacement_(SPNQ)
78	120265	Dave Schaefer	RC-Spring_Replacement_(SPNQ)
79	120272	Nick Larson	*Special/Quoted_Pricing_(See_Notes)
80	120272	Nick Larson	*Special/Quoted_Pricing_(See_Notes)
81	120272	Nick Larson	*Special/Quoted_Pricing_(See_Notes)
82	-	Emily Jean	BLANK
Example

Excel Workbook
	A	B	C	D	E
1			Work Date:	4/1/2016	Total Hours:
2			Work Order	Customer name	Appointment Type
3			-	Aaron Thompson	BLANK
4	Aaron Thompson	4/1/2016	120015	Charles Frank	NC-Remote/Keyless/Prog.
5	Aaron Thompson	4/1/2016	120015	Charles Frank	NC-Remote/Keyless/Prog.
6	Aaron Thompson	4/1/2016	120015	Charles Frank	NC-Remote/Keyless/Prog.
7	Aaron Thompson	4/1/2016	119408	Tammy Bauer	NC-New_Door_Estimate
8	Aaron Thompson	4/1/2016	119408	Tammy Bauer	NC-New_Door_Estimate
9	Aaron Thompson	4/1/2016	119408	Tammy Bauer	NC-New_Door_Estimate
10	Aaron Thompson	4/1/2016	120087	Robin Ellingson	NC-Wont_Open/Need_to_Assist
11	Aaron Thompson	4/1/2016	120087	Robin Ellingson	NC-Wont_Open/Need_to_Assist
12	Aaron Thompson	4/1/2016	120087	Robin Ellingson	NC-Wont_Open/Need_to_Assist
13	Aaron Thompson	4/1/2016	120092	David Swenson	NC-Opener_Repair_(G&S_-_SS_-_Chain_-_ETC)
14	Aaron Thompson	4/1/2016	120092	David Swenson	NC-Opener_Repair_(G&S_-_SS_-_Chain_-_ETC)
15	Aaron Thompson	4/1/2016	120092	David Swenson	NC-Opener_Repair_(G&S_-_SS_-_Chain_-_ETC)
16	Aaron Thompson	4/1/2016	120074	Lourdas Nehring	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
17	Aaron Thompson	4/1/2016	120074	Lourdas Nehring	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
18	Aaron Thompson	4/1/2016	120074	Lourdas Nehring	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
19	Aaron Thompson	4/1/2016	120119	Vance Vang	RC-Door/Track_Hit
20	Aaron Thompson	4/1/2016	120119	Vance Vang	RC-Door/Track_Hit
21	Aaron Thompson	4/1/2016	120119	Vance Vang	RC-Door/Track_Hit
22	Aaron Thompson	4/1/2016	120036	Tabitha Barnes	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
23	Aaron Thompson	4/1/2016	120036	Tabitha Barnes	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
24	Aaron Thompson	4/1/2016	120036	Tabitha Barnes	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
25	Aaron Thompson	4/1/2016	120125	Mary Montury	*DI-WMA
26	Aaron Thompson	4/1/2016	120125	Mary Montury	*DI-WMA
27	Aaron Thompson	4/1/2016	120125	Mary Montury	*DI-WMA
28	Aaron Thompson	4/1/2016	-	Aaron Thompson	BLANK
29	Aaron Thompson	4/1/2016	120036	Tabitha Barnes	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
30	Aaron Thompson	4/1/2016	120036	Tabitha Barnes	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
31
32			Work Date:	4/2/2016	Total Hours:
33			Work Order	Customer name	Appointment Type
34			-	Jon Doe	BLANK
35	Jon Doe	4/2/2016	120127	Tom Peterson	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
36	Jon Doe	4/2/2016	120127	Tom Peterson	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
37	Jon Doe	4/2/2016	120127	Tom Peterson	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
38	Jon Doe	4/2/2016	120062	Jenny Treuenfels	NC-CBL_Broken/Crooked/Off_Track/Roller_Out
39	Jon Doe	4/2/2016	120062	Jenny Treuenfels	NC-CBL_Broken/Crooked/Off_Track/Roller_Out
40	Jon Doe	4/2/2016	120062	Jenny Treuenfels	NC-CBL_Broken/Crooked/Off_Track/Roller_Out
41	Jon Doe	4/2/2016	120068	William Wood	*Special/Quoted_Pricing_(See_Notes)
42	Jon Doe	4/2/2016	120068	William Wood	*Special/Quoted_Pricing_(See_Notes)
43	Jon Doe	4/2/2016	120068	William Wood	*Special/Quoted_Pricing_(See_Notes)
44	Jon Doe	4/2/2016	120129	Bryce Carlson	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
45	Jon Doe	4/2/2016	120129	Bryce Carlson	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
46	Jon Doe	4/2/2016	120129	Bryce Carlson	NC-Misc/SE_Unsure_of_Issue_(See_Notes)
47	Jon Doe	4/2/2016	120121	Paul Commers	RC-Opener_Repair_(G&S-SS-Chain-ETC)
48	Jon Doe	4/2/2016	120121	Paul Commers	RC-Opener_Repair_(G&S-SS-Chain-ETC)
49	Jon Doe	4/2/2016	120121	Paul Commers	RC-Opener_Repair_(G&S-SS-Chain-ETC)
50	Jon Doe	4/2/2016	120143	Blair Larson	NC-Opener_Repair_(G&S_-_SS_-_Chain_-_ETC)
51	Jon Doe	4/2/2016	120143	Blair Larson	NC-Opener_Repair_(G&S_-_SS_-_Chain_-_ETC)
52	Jon Doe	4/2/2016	120143	Blair Larson	NC-Opener_Repair_(G&S_-_SS_-_Chain_-_ETC)
53	Jon Doe	4/2/2016	120149	Deann Christensen	NC-Door/Track_Hit
54	Jon Doe	4/2/2016	120149	Deann Christensen	NC-Door/Track_Hit
55	Jon Doe	4/2/2016	120149	Deann Christensen	NC-Door/Track_Hit
56	Jon Doe	4/2/2016	-	Jon Doe	BLANK
57
58			Work Date:	4/4/2016	Total Hours:
59			Work Order	Customer name	Appointment Type
60			-	Emily Jean	BLANK
61	Emily Jean	4/4/2016	120177	John Christiansen	NC-Wont_Open/Need_to_Assist
62	Emily Jean	4/4/2016	120177	John Christiansen	NC-Wont_Open/Need_to_Assist
63	Emily Jean	4/4/2016	120177	John Christiansen	NC-Wont_Open/Need_to_Assist
64	Emily Jean	4/4/2016	120154	Marian Brundage	*Service_WMA
65	Emily Jean	4/4/2016	120154	Marian Brundage	*Service_WMA
66	Emily Jean	4/4/2016	120154	Marian Brundage	*Service_WMA
67	Emily Jean	4/4/2016	120122	Allyn Zelenka	NC-Tune_up/Adjustment
68	Emily Jean	4/4/2016	120122	Allyn Zelenka	NC-Tune_up/Adjustment
69	Emily Jean	4/4/2016	120122	Allyn Zelenka	NC-Tune_up/Adjustment
70	Emily Jean	4/4/2016	120181	Calvin Hall	*Service_WMA
71	Emily Jean	4/4/2016	120181	Calvin Hall	*Service_WMA
72	Emily Jean	4/4/2016	120181	Calvin Hall	*Service_WMA
73	Emily Jean	4/4/2016	120194	Burnsville Shop - Read Appointment Notes	*Instruction/Note
74	Emily Jean	4/4/2016	120194	Burnsville Shop - Read Appointment Notes	*Instruction/Note
75	Emily Jean	4/4/2016	120194	Burnsville Shop - Read Appointment Notes	*Instruction/Note
76	Emily Jean	4/4/2016	120265	Dave Schaefer	RC-Spring_Replacement_(SPNQ)
77	Emily Jean	4/4/2016	120265	Dave Schaefer	RC-Spring_Replacement_(SPNQ)
78	Emily Jean	4/4/2016	120265	Dave Schaefer	RC-Spring_Replacement_(SPNQ)
79	Emily Jean	4/4/2016	120272	Nick Larson	*Special/Quoted_Pricing_(See_Notes)
80	Emily Jean	4/4/2016	120272	Nick Larson	*Special/Quoted_Pricing_(See_Notes)
81	Emily Jean	4/4/2016	120272	Nick Larson	*Special/Quoted_Pricing_(See_Notes)
82	Emily Jean	4/4/2016	-	Emily Jean	BLANK
Example

Sub HockeyDiablo()
Dim rngData As Range, r As Range, myDate As Date, strName As String
Application.ScreenUpdating = False
    With Worksheets("Example")
        .Columns("A:B").Insert
        Set rngData = .Columns("E").SpecialCells(2)
        For Each r In rngData.Areas
            myDate = r(1, 0).Value
            strName = r(3, 0).Value
            r.Offset(3, -4).Resize(r.Rows.Count - 3, 1).Value = strName
            r.Offset(3, -3).Resize(r.Rows.Count - 3, 1).Value = myDate
        Next r
         .Columns("A:B").AutoFit
    End With
Application.ScreenUpdating = True
End Sub

 

[HockeyDiablo]

I was on manual, when I open excel it does that. Not sure why. Still playing around with this

=IF(C7="Work Date:",D7,A6) on auto fill worked great. Thank you for the inspiration.

Still open to a VBA

 

[HockeyDiablo]

Runtime 1004 error on the second loop where [r.Offset(3, -3).Resize(r.Rows.Count - 3, 1).Value = myDate] begins

 

[skywriter]

There is no second loop, there's only one loop in the code.

I ran it against the sample data you posted, so if you are having an issue then the reason may be because there's some difference between the file you uploaded and the file you are trying to use it against.