Dismiss Notice
Guest, I have a big favor to ask you. We've been working very hard to establish ourselves on social media. If you like/follow our pages it would be a HUGE help to us. SoSH on Facebook and Inside the Pylon Thanks! Nip

Fun With Excel v14

Discussion in 'SoSH Support & FAQ' started by BroodsSexton, Jun 8, 2018.

  1. BroodsSexton

    BroodsSexton Member SoSH Member

    Messages:
    8,255
    Actually, Google Sheets.

    I have a Google Sheet generated through a Google Form, i.e., each row is a form submission.

    Each submission includes (among other data) the submitter's last name, first name, email address and then, let's call it "unique order". Some people submit more than once, depending on how many unique orders they have. So, for example, one day a person submits a unique order "hamburger," the next day they submit a unique order, "hot dog".

    What I would like to do is generate a secondary sheet, with a single row for each unique email address, and with the following columns:

    Last Name, First Name, Email, Unique Order 1, Unique Order 2, Unique Order 3, Unique Order 4, where each "Unique Order" comes from a different row (different submission) on the initial sheet.

    The problem is that I am an Excel novice, and this seems to be a sophisticated Excel problem.

    Any suggestions? (Hire someone who knows how to program Excel?)
     
  2. BroodsSexton

    BroodsSexton Member SoSH Member

    Messages:
    8,255
    As evidence of my technical acumen I posted this in the wrong forum.
     
    #2 BroodsSexton, Jun 8, 2018
    Last edited: Jun 8, 2018
  3. djbayko

    djbayko Member SoSH Member

    Messages:
    7,996
    Wait. Excel or Sheets? You flip flopped at the beginning and end of your post.

    Am I correct that there is no limit to the number of times an email can be submitted (and therefore how many "Unique Order N's" there may be)? If so, then you definitely have to write some VBA to do this. If the number of submissions is limited to - let's say 4 - then it's feasible to do this with formulas, but it would be rather complex. VBA is the cleanest solution either way but requires a modicum of coding experience.

    I don't use Sheets very often but I imagine it would be similarly sophisticated.

    Edit: Didn't notice it was the wrong forum because I clicked on your link from "Recent Threads".
     
  4. BroodsSexton

    BroodsSexton Member SoSH Member

    Messages:
    8,255
    It's Sheets, sorry. But I assume most of the functionality is the same as in Excel. In any event, you are correct there is no limit to the number of times an email can be submitted. So I guess I should have named it Fun with Visual Basic!

    Ahh well, it was worth it to see if anyone had any quick suggestions...
     
  5. grantb

    grantb Couldn't get into a real school Silver Supporter SoSH Member

    Messages:
    2,022
    Put it in a pivot table. You won't have it across, but it will list all the unique orders next to each user. To make it dynamic, first make a named range "Data" or whatever as =offset(A1,0,0,counta($A:$A),counta($1:$1)) where A is your first column of data and 1 is the row with the headers. Then make a pivot and set the data range used as the "Data" named range and you'll just have to refresh the pivot to get it.

    Let me know if this is beyond you and you'd like me to do it for you by sharing the table with me
     
  6. bowiac

    bowiac I've been living a lie. Lifetime Member SoSH Member

    Messages:
    12,068
    I think this may work for your problem:



    The first three rows of the "Input" tab are sample data. The 4th row is something you'll need to add as a formula and fill down.

    The first row of the output tab should include a list of unique email addresses (you'll need to de-dupe them yourself, but takes 10 seconds). The rest are formulas built to pull data from the "Input" tab. It's set up to handle 25 orders a person right now, but you can drag that over to handle an arbitrary number. It's also set up for 50,000 rows in the Input tab at the moment in the Index/Match formula, but that can be adjusted as well.
     
  7. djbayko

    djbayko Member SoSH Member

    Messages:
    7,996
    Oh if there can be manual steps, such as de-duping, and a fixed number of "Unique Orders", then the solution becomes a hell of a lot easier in Excel also. My bad.
     
  8. bowiac

    bowiac I've been living a lie. Lifetime Member SoSH Member

    Messages:
    12,068
    Maybe not? I don't know the exact requirements. The solution posted works for an arbitrary number of Unique Orders however. It does still require a manual de-dupe however.
     
  9. BroodsSexton

    BroodsSexton Member SoSH Member

    Messages:
    8,255
    This looks like it works (I don't think it matters, but every unique order is truly a unique order) and is fairly elegant. How do I export this sheet so I can play around with it? I can de-dupe. That's not a problem.
     
  10. djbayko

    djbayko Member SoSH Member

    Messages:
    7,996
    Yes, but you have to know the max value at some point. You could expand it out to 100 and then have some shithead submit 101 entires :) That's all I was saying. But if you're adding a manual step in there for de-dup, then having another isn't a big deal.
     
  11. BroodsSexton

    BroodsSexton Member SoSH Member

    Messages:
    8,255
    There won't be 101 entries. I have decreed it. Probably no more than 10. Definitely no more than 25. And 100% absolutely super duper no more than 50.
     
  12. bowiac

    bowiac I've been living a lie. Lifetime Member SoSH Member

    Messages:
    12,068
    File -> Make A Copy to create your own version to play around with this.
     
  13. BroodsSexton

    BroodsSexton Member SoSH Member

    Messages:
    8,255
    I think you misunderst
    You're misapprehending the depth of my incompetence. I don't know how to get to the file menu for an embedded sheet like that. lol.
     
  14. bowiac

    bowiac I've been living a lie. Lifetime Member SoSH Member

    Messages:
    12,068
    On - try clicking this link. It'll take you to the non-embedded version.
     

Share This Page