Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

extract usable data from a google docs spreadsheet

Posted on 2013-12-12
9
Medium Priority
?
463 Views
Last Modified: 2013-12-31
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:
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.
0
Comment
Question by:stretchr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39715966
Hi stretchr

Could you post your spreadsheet or an example so we can see the structure?
0
 

Author Comment

by:stretchr
ID: 39716046
Here's a screenshot of part of the spreadsheet, annotated. If it's not straightforward what you're seeing, let me know.
SessionsSpreadsheetStructure.png
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39716521
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.
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

Open in new window

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

Open in new window


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
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:stretchr
ID: 39718017
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.
0
 

Author Comment

by:stretchr
ID: 39743389
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.
0
 
LVL 11

Assisted Solution

by:Angelp1ay
Angelp1ay earned 1800 total points
ID: 39743543
Sorry, should have mentioned that. You need to add Word to the references!
Adding a ReferenceWord Object Library
0
 

Author Comment

by:stretchr
ID: 39744527
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?
0
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 1800 total points
ID: 39747845
You just need to update the block of code marked "registrant name" in each macro.

Assuming you now have first name in col 3 and last name in col 4:
            ' Registrant name
            If sh.Cells(row, col).Value = 1 Then
                sel.Style = wordDoc.Styles("Normal")
                sel.TypeText sh.Cells(row, 3).Value & " " & sh.Cells(row, 4).Value & vbCrLf
                cCount = cCount + 1
            End If

Open in new window

The important bit is the selection type text command (sel.TypeText) which dumps out to word. The updated example above means write out:
- value at current row, 3rd column (first name)
- space
- value at current row, 4th column (second name)
- line break

Note: If you've also shifted the rest of the columns you'll have to update the loop counter for the column too e.g.
For col = 6 To lastCol

Open in new window


Updated macros in full:
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 = 6 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 & " " & sh.Cells(row, 4).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

Open in new window


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 & " " & sh.Cells(row, 4).Value & vbCrLf
    
        rCount = 0
        For col = 6 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

Open in new window

0
 

Author Comment

by:stretchr
ID: 39748004
Ahh, thankyou very much, I follow all that now!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Viewers will learn how to share Excel data with others from desktop Excel, as well as Excel Online via OneDrive, and embed an Excel file on a website.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question