stretchr
asked on
extract usable data from a google docs spreadsheet
Hi folks,
I am collecting registration information for a conference using a Formstack form, which is dumping the collected data into a Google spreadsheet. People are registering for breakout sessions, of which there are five, with twelve different streams available for each session. For each session they are asked to list their preferred streams, using 1, 2, 3.
What I need help with is (and maybe I'm just too tired at the moment to process it clearly!) that I need to:
I'm open to any ideas on the best way to go about this, without having to pay for extra software. I have access to Excel, but not MS Access - happy to use the DB part of OpenOffice if that's the best way forward.
Thanks in advance for your suggestions.
I am collecting registration information for a conference using a Formstack form, which is dumping the collected data into a Google spreadsheet. People are registering for breakout sessions, of which there are five, with twelve different streams available for each session. For each session they are asked to list their preferred streams, using 1, 2, 3.
What I need help with is (and maybe I'm just too tired at the moment to process it clearly!) that I need to:
keep track of the total number of first preferences for each stream as registrations come in (I can't just use COUNTIF in a row in Google spreadsheets as the new registrations from Formstack are always added to the very bottom of the spreadsheet)
create lists of the registrants in each stream (that I can ultimately print out and check people off against as they arrive)
create a list of streams each registrant has registered for (again ideally printable by individual registrant, to be provided to each for reference)
I'm open to any ideas on the best way to go about this, without having to pay for extra software. I have access to Excel, but not MS Access - happy to use the DB part of OpenOffice if that's the best way forward.
Thanks in advance for your suggestions.
ASKER
Here's a screenshot of part of the spreadsheet, annotated. If it's not straightforward what you're seeing, let me know.
SessionsSpreadsheetStructure.png
SessionsSpreadsheetStructure.png
Solution
Please see attached example :)
Here is the code for the macros (hopefully it's easy enough to follow). The second is almost identical to the first, just looping row then col instead of col then row.
Explanation
I'm not sure why you say this, and perhaps I've misunderstood. Check the second sheet of the example where I just COUNTIF on the entire column (which should catch anything anywhere including the bottom).
On the second form hit the "Print per Stream" button. It will dump the list out to Word.
On the second form hit the "Print per Registrant" button. It will dump the list out to Word.
Streams.xlsm
Please see attached example :)
Here is the code for the macros (hopefully it's easy enough to follow). The second is almost identical to the first, just looping row then col instead of col then row.
Public Sub PrintPerStream()
' Find the data
Dim sh As Excel.Worksheet
Set sh = Worksheets("Raw Data")
Dim lastCol, lastRow As Integer
lastCol = sh.UsedRange.Columns.Count
lastRow = sh.UsedRange.Rows.Count
' Create word app and doc
Dim wordApp As Word.Application
Dim wordDoc As Word.Document
Set wordApp = CreateObject("Word.Application")
Set wordDoc = wordApp.Documents.Add
wordApp.Visible = True
' Write to word
Dim col, row, cCount As Integer, sel
Set sel = wordApp.Selection
For col = 5 To lastCol
' Stream name
sel.Style = wordDoc.Styles("Heading 3")
sel.TypeText sh.Cells(1, col).Value & vbCrLf
cCount = 0
For row = 2 To lastRow
' Registrant name
If sh.Cells(row, col).Value = 1 Then
sel.Style = wordDoc.Styles("Normal")
sel.TypeText sh.Cells(row, 3).Value & vbCrLf
cCount = cCount + 1
End If
Next
If cCount = 0 Then
sel.Style = wordDoc.Styles("Normal")
sel.TypeText "No registrants" & vbCrLf
End If
Next
End Sub
Public Sub PrintPerRegistrant()
' Find the data
Dim sh As Excel.Worksheet
Set sh = Worksheets("Raw Data")
Dim lastCol, lastRow As Integer
lastCol = sh.UsedRange.Columns.Count
lastRow = sh.UsedRange.Rows.Count
' Create word app and doc
Dim wordApp As Word.Application
Dim wordDoc As Word.Document
Set wordApp = CreateObject("Word.Application")
Set wordDoc = wordApp.Documents.Add
wordApp.Visible = True
' Write to word
Dim col, row, rCount As Integer, sel
Set sel = wordApp.Selection
For row = 2 To lastRow
' Registrant name
sel.Style = wordDoc.Styles("Heading 3")
sel.TypeText sh.Cells(row, 3).Value & vbCrLf
rCount = 0
For col = 5 To lastCol
' Stream name
If sh.Cells(row, col).Value = 1 Then
sel.Style = wordDoc.Styles("Normal")
sel.TypeText sh.Cells(1, col).Value & vbCrLf
rCount = rCount + 1
End If
Next
If rCount = 0 Then
sel.Style = wordDoc.Styles("Normal")
sel.TypeText "No registrations" & vbCrLf
End If
Next
End Sub
Explanation
keep track of the total number of first preferences for each stream as registrations come in (I can't just use COUNTIF in a row in Google spreadsheets as the new registrations from Formstack are always added to the very bottom of the spreadsheet)
I'm not sure why you say this, and perhaps I've misunderstood. Check the second sheet of the example where I just COUNTIF on the entire column (which should catch anything anywhere including the bottom).
create lists of the registrants in each stream (that I can ultimately print out and check people off against as they arrive)
On the second form hit the "Print per Stream" button. It will dump the list out to Word.
create a list of streams each registrant has registered for (again ideally printable by individual registrant, to be provided to each for reference)
On the second form hit the "Print per Registrant" button. It will dump the list out to Word.
Streams.xlsm
ASKER
You're right, I could put the COUNTIF in another sheet in the workbook.
Are these macros for Excel? That's ok but I will only be able to use them right at the end of the registration process when I dump the spreadsheet out of Google into Excel.
I'll be away for a few days now, so any more comments I'll respond to when I'm back.
Are these macros for Excel? That's ok but I will only be able to use them right at the end of the registration process when I dump the spreadsheet out of Google into Excel.
I'll be away for a few days now, so any more comments I'll respond to when I'm back.
ASKER
Hello,
I'm back on deck now, and have copied your code over into my own spreadsheet. I am having a problem where when I try to run the macro, I receive an error on the line "Dim wordApp as Word.application" which says "Compile error: User-defined type not defined". It's weird, because your sample sheet works fine.
I have one further thing, which if you could tell me how to modify your code, would be great. I've had to separate the registrant name field into first name and last name, so as I can sort by last name. I'm just not quite sure how to implement that additional step in your macro code.
I'm back on deck now, and have copied your code over into my own spreadsheet. I am having a problem where when I try to run the macro, I receive an error on the line "Dim wordApp as Word.application" which says "Compile error: User-defined type not defined". It's weird, because your sample sheet works fine.
I have one further thing, which if you could tell me how to modify your code, would be great. I've had to separate the registrant name field into first name and last name, so as I can sort by last name. I'm just not quite sure how to implement that additional step in your macro code.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks, that was just what I was looking for.
Now, just for my last point in my previous comment - I have had to split the name into two cells, first and last. What adjustments do I need to make in the macro to make it export the first and last names separately into Word, with a space between? I have figured out how to adjust the column references, but the rest escapes me. Can you show me the solution to that?
Now, just for my last point in my previous comment - I have had to split the name into two cells, first and last. What adjustments do I need to make in the macro to make it export the first and last names separately into Word, with a space between? I have figured out how to adjust the column references, but the rest escapes me. Can you show me the solution to that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ahh, thankyou very much, I follow all that now!
Could you post your spreadsheet or an example so we can see the structure?