Transpose column data to individual rows, while repeating certain cells per row and skipping blanks

[NightmanCometh]

Usually I try to sort out my excel issues on my own, but this one is tough (for me at least).

I have a google form that I am using for a job site for people to turn information over between shifts. The form returns information on one line per submission, but each submission contains multiple responses from column to column depending on which "Work Area(s)" a person selects in the form. I want to input a code that automatically converts each response (Details1, 2, etc) into their own row on a new sheet, with their respective column titles (Work Area 1, 2, etc) and their other corresponding information (like the date, discipline, and extra info) copied into these specified rows as well, while skipping over cells where the "detail" is blank. This generates an easy to read report as the end product. I'm sure there is some sort of code that can do this, it's just way beyond my knowledge of excel. Below is a simplified visual for understanding of the problem, (tried to make it all line up sorry). For the detailed out version, please see the link at the end showing a before and after.

What I start with:
[TABLE="width: 600, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Discipline[/TD]
[TD]WorkArea1[/TD]
[TD]WorkArea2[/TD]
[TD]WorkArea3[/TD]
[TD]ExtraInfo[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]Disc1[/TD]
[TD]Details1[/TD]
[TD][/TD]
[TD]Details2[/TD]
[TD]Info1[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]Disc2[/TD]
[TD][/TD]
[TD]Details3[/TD]
[TD]Details4[/TD]
[TD]Info2[/TD]
[/TR]
</tbody>[/TABLE]





What I want to end up with:
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Discipline[/TD]
[TD]WorkArea#[/TD]
[TD]TurnDetails[/TD]
[TD]ExtraInfo[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]Disc1[/TD]
[TD]WorkArea1[/TD]
[TD]Details1[/TD]
[TD]Info1[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]Disc1[/TD]
[TD]WorkArea3[/TD]
[TD]Details2[/TD]
[TD]Info1[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]Disc2[/TD]
[TD]WorkArea2[/TD]
[TD]Details3[/TD]
[TD]Info2[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]Disc2[/TD]
[TD]WorkArea3[/TD]
[TD]Details4[/TD]
[TD]Info2[/TD]
[/TR]
</tbody>[/TABLE]








On the link, the tab "Form Response 1" is how the data looks when it comes out on the google form. The tab "Final Product" is how I want it to look. Hopefully I explained this well enough. Thank you for any help you are able to provide.

https://docs.google.com/spreadsheets/d/10O96oQH9XZM2Mp5bisaJpaHwpKwcuF9zShLSYnjSznA/edit#gid=0

 

[MickG]

Try this Based on "Data" sheet "Form Response 1" and "Results on "sheet "Final Product".

[COLOR="Navy"]Sub[/COLOR] MG19Oct24
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = Sheets("Form Response 1").Range("A1").CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 2), 1 To 9)
nray(1, 1) = "Timestamp": nray(1, 2) = "Turnover Date": nray(1, 3) = "Shift Handover": nray(1, 4) = "Discipline": nray(1, 5) = "Contact"
nray(1, 6) = "Work Area": nray(1, 7) = "Description of work": nray(1, 8) = "Extra Info Needed": nray(1, 9) = "Issues/Concerns"
c = 1
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]For[/COLOR] Ac = 7 To UBound(Ray, 2) - 2
        [COLOR="Navy"]If[/COLOR] Not IsEmpty(Ray(n, Ac)) [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            nray(c, 1) = Ray(n, 1): nray(c, 2) = Ray(n, 2):
            nray(c, 3) = Ray(n, 3): nray(c, 4) = Ray(n, 4)
            nray(c, 5) = Ray(n, 5)
            nray(c, 6) = Ray(1, Ac)
            nray(c, 7) = Ray(n, Ac)
            nray(c, 8) = Ray(n, UBound(Ray, 2) - 1)
            nray(c, 9) = Ray(n, UBound(Ray, 2))
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
Sheets("Final Product").Range("A1").Resize(c, 9) = nray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick