Fun With Excel v14

BroodsSexton

Member
SoSH Member
Feb 4, 2006
12,630
guam
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?)
 

djbayko

Member
SoSH Member
Jul 18, 2005
25,894
Los Angeles, CA
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".
 

BroodsSexton

Member
SoSH Member
Feb 4, 2006
12,630
guam
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".
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...
 

begranter

Couldn't get into a real school
Silver Supporter
SoSH Member
Jul 9, 2007
2,344
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
 

bowiac

Caveat: I know nothing about what I speak
Lifetime Member
SoSH Member
Dec 18, 2003
12,945
New York, NY
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.
 

djbayko

Member
SoSH Member
Jul 18, 2005
25,894
Los Angeles, CA
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.
 

bowiac

Caveat: I know nothing about what I speak
Lifetime Member
SoSH Member
Dec 18, 2003
12,945
New York, NY
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.
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.
 

BroodsSexton

Member
SoSH Member
Feb 4, 2006
12,630
guam
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.
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.
 

djbayko

Member
SoSH Member
Jul 18, 2005
25,894
Los Angeles, CA
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.
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.
 

BroodsSexton

Member
SoSH Member
Feb 4, 2006
12,630
guam
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.
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.
 

bowiac

Caveat: I know nothing about what I speak
Lifetime Member
SoSH Member
Dec 18, 2003
12,945
New York, NY
File -> Make A Copy to create your own version to play around with this.
 

BroodsSexton

Member
SoSH Member
Feb 4, 2006
12,630
guam
I think you misunderst
File -> Make A Copy to create your own version to play around with this.
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.