Don't have a row, Excel!
October 28, 2010 12:37 PM Subscribe
The short of it: avoid duplicates, show multiple sales in one row.
I am trying to aggregate sales data on an Excel workbook, and it really isn't as complicated as I'm making it out to be. Maybe MeFites have ideas?
Drop-down menus? Check.
List validation? Positive.
I'm compiling a year-long Excel sheet of sales data for multiple sales items. My goals are:
* to keep each unique buyer on one single line
* within each line, account for three separate potential sale items and keep quantities unique within that one line
* avoid duplicate entries for the same buyer
Is this possible when there are three (3) unique sale items and I want to keep it all on one line?
Before, I was creating three lines per buyer, to keep the option open for the three sale item options, and then just selecting "0" for any titles not bought. But, this creates a lot of gaps between rows, and I'd like to use only one row for each buyer. Is this possible? Again, this is probably really obvious, but I am in the trees and can't see the forest, or something.
Thanks, AskMeFi!
Drop-down menus? Check.
List validation? Positive.
I'm compiling a year-long Excel sheet of sales data for multiple sales items. My goals are:
* to keep each unique buyer on one single line
* within each line, account for three separate potential sale items and keep quantities unique within that one line
* avoid duplicate entries for the same buyer
Is this possible when there are three (3) unique sale items and I want to keep it all on one line?
Before, I was creating three lines per buyer, to keep the option open for the three sale item options, and then just selecting "0" for any titles not bought. But, this creates a lot of gaps between rows, and I'd like to use only one row for each buyer. Is this possible? Again, this is probably really obvious, but I am in the trees and can't see the forest, or something.
Thanks, AskMeFi!
Yeah, it would help if you posted a sample of your data in Google docs for people to look at.
Obviously scrub it for anything private/confidential.
posted by dfriedman at 12:43 PM on October 28, 2010
Obviously scrub it for anything private/confidential.
posted by dfriedman at 12:43 PM on October 28, 2010
So it would basically be like this:
[Column title here]: [row entry for buyer Madamina]
Buyer: Madamina
# of keyboards purchased during 2009: 10
# of mice purchased during 2009: 0
# of monitors purchased during 2009: 4
where I may have purchased 3 keyboards on a visit last week and 7 on a visit this week, but you don't necessarily need to know that here.
Is that correct, or am I missing something?
posted by Madamina at 12:48 PM on October 28, 2010
[Column title here]: [row entry for buyer Madamina]
Buyer: Madamina
# of keyboards purchased during 2009: 10
# of mice purchased during 2009: 0
# of monitors purchased during 2009: 4
where I may have purchased 3 keyboards on a visit last week and 7 on a visit this week, but you don't necessarily need to know that here.
Is that correct, or am I missing something?
posted by Madamina at 12:48 PM on October 28, 2010
It would be a bit more helpful if you posted it in spreadsheet layout format... sorry, not trying to be a bug, just trying to make sure we're all on the same page re: your data before we start flinging suggestions at ya. :-)
posted by julthumbscrew at 12:57 PM on October 28, 2010
posted by julthumbscrew at 12:57 PM on October 28, 2010
Response by poster: Yes! Here is a fake Excel worksheet:
# | BUYER | PRODUCT NAME | QTY | SALE DATE | ETC.
--------------------------------------------------------------------
1 | Buyer1 | PRODUCT1 XXX | XXX1 | MM.DD.YY | XYZ
--------------------------------------------------------------------
2 | Buyer2 | PRODUCT2 XXX | XXX1 | MM.DD.YY | XYZ
--------------------------------------------------------------------
3 | Buyer3 | PRODUCT2 XXX | XXX1 | MM.DD.YY | XYZ
--------------------------------------------------------------------
4 | Buyer4 | PRODUCT1 XXX | XXX1 | MM.DD.YY | ABC
--------------------------------------------------------------------
5 | Buyer5 | PRODUCT3 XXX | XXX1 | MM.DD.YY | XYZ
--------------------------------------------------------------------
6 | Buyer6 | PRODUCT1 XXX | XXX1 | MM.DD.YY | XYZ
--------------------------------------------------------------------
The issue with this format is that there are three (3) potential sale items, and I have drop-down menus, but this will skew quantity sales, since if I keep the Excel worksheet with the above format, while the rows are kept unique to one buyer and there are no duplicates, I can't account for other products (of which there are three options) within that row. My work-around was the following:
# | BUYER | PRODUCT NAME | QTY | SALE DATE | ETC.
--------------------------------------------------------------------
1 | Buyer1 | PRODUCT1 XXX | XXX0 | MM.DD.YY | XYZ
--------------------------------------------------------------------
X | XXXXX | PRODUCT2 XXX | XXX0 | MM.DD.YY | XY
--------------------------------------------------------------------
X | XXXXX | PRODUCT3 XXX | XXX1 | MM.DD.YY | XY
--------------------------------------------------------------------
2 | Buyer2 | PRODUCT1 XXX | XXX1 | MM.DD.YY | XYZ
--------------------------------------------------------------------
X | XXXXX | PRODUCT2 XXX | XXX0 | MM.DD.YY | XY
--------------------------------------------------------------------
X | XXXXX | PRODUCT3 XXX | XXX0 | MM.DD.YY | XY
--------------------------------------------------------------------
3 | Buyer3 | PRODUCT1 XXX | XXX1 | MM.DD.YY | XY
--------------------------------------------------------------------
X | XXXXX | PRODUCT2 XXX | XXX1 | MM.DD.YY | XYZ
--------------------------------------------------------------------
X | XXXXX | PRODUCT3 XXX | XXX1 | MM.DD.YY | XY
--------------------------------------------------------------------
4 | Buyer4 | PRODUCT1 XXX | XXX1 | MM.DD.YY | XY
--------------------------------------------------------------------
" "
But, the above creates a situation where there are multiple rows for the same buyer and the same order, since there are three possible items to buy. In total, there are over 300 unique buyers, and ideally I don't have to format this Excel sheet to skip and keep certain rows blank just because I can't figure out a format to account for three different sale products.
I want to keep these sales reports consistent so that only one row is used for each buyer, and to figure out a system within Excel (using lists and data validation) to do this. Is it possible?
Current lists:
* Sale Item name (e.g. Product1, Product2, and Product3)
* Quantity (e.g. 1, 2, 3, 4, 5, etc.)
The above are currently drop-down menu items.
Am I making this more difficult than it is?
_________________
Note: The "X"s in Excel example part two are just for formatting placeholders within this post, but would be blank fields in the Excel worksheet itself.
Explaining this by text makes this a little more tedious than it would be to explain in person, promise!
posted by simulacra at 1:15 PM on October 28, 2010
# | BUYER | PRODUCT NAME | QTY | SALE DATE | ETC.
--------------------------------------------------------------------
1 | Buyer1 | PRODUCT1 XXX | XXX1 | MM.DD.YY | XYZ
--------------------------------------------------------------------
2 | Buyer2 | PRODUCT2 XXX | XXX1 | MM.DD.YY | XYZ
--------------------------------------------------------------------
3 | Buyer3 | PRODUCT2 XXX | XXX1 | MM.DD.YY | XYZ
--------------------------------------------------------------------
4 | Buyer4 | PRODUCT1 XXX | XXX1 | MM.DD.YY | ABC
--------------------------------------------------------------------
5 | Buyer5 | PRODUCT3 XXX | XXX1 | MM.DD.YY | XYZ
--------------------------------------------------------------------
6 | Buyer6 | PRODUCT1 XXX | XXX1 | MM.DD.YY | XYZ
--------------------------------------------------------------------
The issue with this format is that there are three (3) potential sale items, and I have drop-down menus, but this will skew quantity sales, since if I keep the Excel worksheet with the above format, while the rows are kept unique to one buyer and there are no duplicates, I can't account for other products (of which there are three options) within that row. My work-around was the following:
# | BUYER | PRODUCT NAME | QTY | SALE DATE | ETC.
--------------------------------------------------------------------
1 | Buyer1 | PRODUCT1 XXX | XXX0 | MM.DD.YY | XYZ
--------------------------------------------------------------------
X | XXXXX | PRODUCT2 XXX | XXX0 | MM.DD.YY | XY
--------------------------------------------------------------------
X | XXXXX | PRODUCT3 XXX | XXX1 | MM.DD.YY | XY
--------------------------------------------------------------------
2 | Buyer2 | PRODUCT1 XXX | XXX1 | MM.DD.YY | XYZ
--------------------------------------------------------------------
X | XXXXX | PRODUCT2 XXX | XXX0 | MM.DD.YY | XY
--------------------------------------------------------------------
X | XXXXX | PRODUCT3 XXX | XXX0 | MM.DD.YY | XY
--------------------------------------------------------------------
3 | Buyer3 | PRODUCT1 XXX | XXX1 | MM.DD.YY | XY
--------------------------------------------------------------------
X | XXXXX | PRODUCT2 XXX | XXX1 | MM.DD.YY | XYZ
--------------------------------------------------------------------
X | XXXXX | PRODUCT3 XXX | XXX1 | MM.DD.YY | XY
--------------------------------------------------------------------
4 | Buyer4 | PRODUCT1 XXX | XXX1 | MM.DD.YY | XY
--------------------------------------------------------------------
" "
But, the above creates a situation where there are multiple rows for the same buyer and the same order, since there are three possible items to buy. In total, there are over 300 unique buyers, and ideally I don't have to format this Excel sheet to skip and keep certain rows blank just because I can't figure out a format to account for three different sale products.
I want to keep these sales reports consistent so that only one row is used for each buyer, and to figure out a system within Excel (using lists and data validation) to do this. Is it possible?
Current lists:
* Sale Item name (e.g. Product1, Product2, and Product3)
* Quantity (e.g. 1, 2, 3, 4, 5, etc.)
The above are currently drop-down menu items.
Am I making this more difficult than it is?
_________________
Note: The "X"s in Excel example part two are just for formatting placeholders within this post, but would be blank fields in the Excel worksheet itself.
Explaining this by text makes this a little more tedious than it would be to explain in person, promise!
posted by simulacra at 1:15 PM on October 28, 2010
Easy with a Pivot Table.
In this example Buyer "a" always bought 1/10/1000 items, Buyer "b" always 2/20/2000, etc just for the sake of easily verifying the results.
posted by spr at 1:19 PM on October 28, 2010
In this example Buyer "a" always bought 1/10/1000 items, Buyer "b" always 2/20/2000, etc just for the sake of easily verifying the results.
posted by spr at 1:19 PM on October 28, 2010
Each buyer has only one purchase instance? Buyer 1 comes one day, buys some number of products 1, 2 & 3, and never comes again? There are only 3 items, and all buyers buy those 3 items?
It seems that if each sale is on a line with the buyer name, you could get this information in a pivot table.
posted by jeather at 1:20 PM on October 28, 2010
It seems that if each sale is on a line with the buyer name, you could get this information in a pivot table.
posted by jeather at 1:20 PM on October 28, 2010
Response by poster: To be a little more clear: Within one order, a buyer may purchase three different sale items. I'm trying to account both for these three items within one row (although some orders only include one unique sale item), and also to be able to keep the quantities of each item easily tallied up with a SUM function within the Excel sheet.
Variables: a) buyer; b) sale item [Product1, Product2, Product3]; c) quantities [1, 2, 3, 4, 5]
Maybe one row just can't handle multiple sale items and track unique quantities.
What I seek to avoid is creating a SUM function for for "Product" quantities that does not account for the three unique items. Helpful? I hope so.
posted by simulacra at 1:21 PM on October 28, 2010
Variables: a) buyer; b) sale item [Product1, Product2, Product3]; c) quantities [1, 2, 3, 4, 5]
Maybe one row just can't handle multiple sale items and track unique quantities.
What I seek to avoid is creating a SUM function for for "Product" quantities that does not account for the three unique items. Helpful? I hope so.
posted by simulacra at 1:21 PM on October 28, 2010
Response by poster: Pivot tables look fun, but I must keep each row because this list must also be uploaded to Constant Contact.
At some point, I'll create Pie Chart data pulls for creating visuals like the Pivot table function.
posted by simulacra at 1:27 PM on October 28, 2010
At some point, I'll create Pie Chart data pulls for creating visuals like the Pivot table function.
posted by simulacra at 1:27 PM on October 28, 2010
Do you only have 3 items, from which each buyer chooses some selection, or are buyers limited to 3 items out of a list of many more?
posted by jeather at 1:29 PM on October 28, 2010
posted by jeather at 1:29 PM on October 28, 2010
Response by poster: jeather: Yes, the former!
Longer answer:
We get reports.
For each month in 2010 (and 2009), we received Excel sales reports from a distributor. My recently tasked job is to aggregate these disparate reports into one master Excel sheet for the year, with the added bonus that it be uploaded into Constant Contact.
We sell three items.
Buyers, within a given order, could buy either multiple copies of a single title; buy two copies of one product, none of the third, and one of the second; or buy one copy each of three potential sale items. I'm trying to figure out how to show this in one row within Excel.
Some order examples:
ORDER 1:
Product 1 = 2
Product 2 = 1
Product 3 = 0
ORDER 2:
Product 1 = 1
Product 2 = 1
Product 3 = 1
ORDER 3:
Product 1 = 0
Product 2 = 1
Product 3 = 1
Goal: to show the above scenarios on one row.
posted by simulacra at 1:43 PM on October 28, 2010
Longer answer:
We get reports.
For each month in 2010 (and 2009), we received Excel sales reports from a distributor. My recently tasked job is to aggregate these disparate reports into one master Excel sheet for the year, with the added bonus that it be uploaded into Constant Contact.
We sell three items.
Buyers, within a given order, could buy either multiple copies of a single title; buy two copies of one product, none of the third, and one of the second; or buy one copy each of three potential sale items. I'm trying to figure out how to show this in one row within Excel.
Some order examples:
ORDER 1:
Product 1 = 2
Product 2 = 1
Product 3 = 0
ORDER 2:
Product 1 = 1
Product 2 = 1
Product 3 = 1
ORDER 3:
Product 1 = 0
Product 2 = 1
Product 3 = 1
Goal: to show the above scenarios on one row.
posted by simulacra at 1:43 PM on October 28, 2010
I have no idea what Constant Contact is.
But, why not column headings like the following:
BUYER | ORDER NO | ORDER DATE | PROD 1 PURCH | PROD 2 PURCH | PROD 3 PURCH
If you need product names or item numbers, just add in those columns, pre-populated.
posted by jeather at 1:46 PM on October 28, 2010
But, why not column headings like the following:
BUYER | ORDER NO | ORDER DATE | PROD 1 PURCH | PROD 2 PURCH | PROD 3 PURCH
If you need product names or item numbers, just add in those columns, pre-populated.
posted by jeather at 1:46 PM on October 28, 2010
And although you might not want to futz around with pivot tables, they can also put all that information on one row.
posted by jeather at 1:46 PM on October 28, 2010
posted by jeather at 1:46 PM on October 28, 2010
Response by poster: jeather: Thanks for the suggestions. I don't mind futzing. The idea of just creating header fields for each sale item was bypassed due to my attachment to personally appealing drop-down menus, which prevent excessive Excel worksheet longitudinal drift.
Are pivot tables easy to use/implement? I've never heard of them before.
posted by simulacra at 2:00 PM on October 28, 2010
Are pivot tables easy to use/implement? I've never heard of them before.
posted by simulacra at 2:00 PM on October 28, 2010
Pivot tables are easy.
In general, you will want column headers like so:
Cust name | Product name | Quantity purchased
Client 1 | Product 1 | 10
Client 1 | Product 2 | 5
Client 2 | Product 3 | 8
Client 3 | Product 1 | 1
etc
Product name can have a list, quantity purchased should have data validation.
Okay, so now, select the area that this list covers, then create a pivot table from that data. You will want to have Cust name in the row fields, Product name in the column fields, and Sum of Quantity purchased in the data items.
I *think* this is what you want it to look like; you can send me the excel file if you want help.
posted by jeather at 2:07 PM on October 28, 2010
In general, you will want column headers like so:
Cust name | Product name | Quantity purchased
Client 1 | Product 1 | 10
Client 1 | Product 2 | 5
Client 2 | Product 3 | 8
Client 3 | Product 1 | 1
etc
Product name can have a list, quantity purchased should have data validation.
Okay, so now, select the area that this list covers, then create a pivot table from that data. You will want to have Cust name in the row fields, Product name in the column fields, and Sum of Quantity purchased in the data items.
I *think* this is what you want it to look like; you can send me the excel file if you want help.
posted by jeather at 2:07 PM on October 28, 2010
« Older Is it kosher to work for two companies at once? | Help me remember...german publisher for language... Newer »
This thread is closed to new comments.
posted by julthumbscrew at 12:40 PM on October 28, 2010