Results 1 to 8 of 8

Thread: Field Mapping

  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I use a company that provides online forms for my members to fill in. They host the forms and the data that people fill in online. It works great. Whenever I want, I can download a "report" from the online forms company. The "report" is an Excel worksheet with all the fields from the form, populated with the data that people have filled in, one row per form. There is no ability to put the fields in different order -- they come in exactly as they are arranged on the form.

    What I REALLY need is to map the data from the "report" into my own worksheet. Some report fields will be eliminated, others will be moved, etc.

    Is there some way to "map" fields from one worksheet into another? (I have decent VBA skills, etc., but I don't use them regularly enough to be aware of all the possibilities.)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Could you be a little more specific?
    I assume this is more complex than getting data from some of the fields by using lookup formulas?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Happy Birthday!

    You may be able to use Advanced Filter:
    In the target worksheet, set up the column headings that you want in the desired order.
    Enter at least one dummy value in the row below the column headings.
    Click somewhere in the target column headings.
    Select Data | Filter | Advanced Filter...
    Switch to the report worksheet.
    Select all columns of the report.
    Select "Copy to another location".
    Leave the Criteria Range box blank.
    Click in the Copy To box.
    Switch to the target sheet.
    Select the column headings for the target.
    Click OK.

    You can even record a macro of these actions. It will result in something like this:

    Code:
    Sub CopyReport()
      Workbooks("Report.xls").Sheets("Sheet1").Columns("A:Z").AdvancedFilter _
    	Action:=xlFilterCopy, CopyToRange:=Range("A1:H1"), Unique:=False
    End Sub
    (This is to be run from the target sheet)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Another option is to use Data | Import External Data | New Database Query...
    Select Excel Files as database type, then click OK.
    Browse to the report workbook, then click OK.
    Select the worksheet, click Add, then Close.
    The steps to take then depend on whether you ticked the check box "Use the Query Wizard to create/edit queries", but in both cases you can add the columns that you want in the order that you want.

  5. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I'll try these things right now -- I've got some new entries in the online form/report.

    Thanks for the birthday wishes. I'm 70 years old, but don't feel it all that much. Few of my contemporaries are as far into modern computers as I am. Those who were IT types were mainframe guys, for the most part. So all my computer advisors and confidants are younger people. Thank heaven for Woody's Lounge!
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  6. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    [quote name='HansV' post='784673' date='15-Jul-2009 07:38']Another option is to use Data | Import External Data | New Database Query...
    Select Excel Files as database type, then click OK.[/quote]
    When I click OK I get an error message "Data source name not found and no default driver specified."
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Hmm, I don't have an explanation for that.

    Have you tried the Advanced Filter method?

  8. #8
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Not yet. I did my update manually. I'll try the Advanced Filter the next time I need to do an update -- probably in a day or two. My updates are manageable manually now, but as time goes on there will be more and more of them.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •