•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to handle data with uneven rows when you want to turn it to a pivot table?
Our take
Navigating data with uneven rows can feel daunting, especially when aiming to create a precise pivot table. If you have multiple sales entries for the same month, like Joe and Stacy both achieving $115.50 in January 2025, it’s crucial to structure your data effectively. To avoid combining their sales amounts, consider placing each salesperson’s name in a dedicated column rather than in separate rows. This approach not only preserves individual sales data but also enhances clarity in your pivot table.
Hi Everyone,
I have the highest sales amount for the month, and the salesperson who made that sale (e.g. $115.50 is the highest sale made in Jan 2025 - Joe made a $115.50 sale on some day in Jan 2025 and Stacy also did on some day in Jan 2025). What's the proper way/structure to turn this data into a pivot table with possible uneven row length for the salesperson? Should the sales person not be in rows but in one column - but I don't want them to add the 2 sales amount (made by 2 different salesperson together) in the pivot table?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Separating Monthly Results in Data for Same Item?Hey all, I’ve got a dataset that I’m having some trouble separating for calculating properly on a pivot table. It is a list of items in Column A, which all belong to a group in Column B, and then C D and E are results for Q1 so Jan, Feb, and March. the problem is that we have a budget figure, a forecast figure, two new forecast figures and the actual figure for each item, that are stacked in the column. so for the first item there are five rows of data that I would like to compare for each month. I just can’t figure out how to do this in a pivot table because the forecast/new forecast/budget/actuals aren’t column headers. I tried adding new columns that totaled up the rows for each category of forecast/actual etc but the results were far more than the real actuals so something failed there. Thanks! submitted by /u/Woodit [link] [comments]
- Showing a row where no value existsI have a list of 5 countries we sell products to. And we split it into 3 product categories: internet, consulting services, phone. I have used power query to and loaded the connection into the data model, but not as a table within a worksheet. These are my total sales amount pivot tables: Filter: USA phone | 500 mill Consulting | 20 mill Internet | 30 mill Filter: New Zealand Phone | 5 mill Internet | 20 mill Problem: I need New Zealand to show Consulting and a sales amount of 0. There is no “consulting” row in the source data itself for New Zealand so “show zero values” in pivot table settings doesn’t work I figure I’ll need to create my own tables listing: Phone, Consulting and Internet as row headings. But how should I get my sales sums? I’m guessing doing xlookups using my pivot table is the data source is not best practice Do I scrap my pivot tables and load the power query as a table for sumif? submitted by /u/Interesting-System [link] [comments]
- How can I create a pivot table with the PowerPivot Measure being used for the rows?Building an inventory valuation model. Basically, taking data from sales, warehouse inventory, and stores to find lower of costs/sales value. I'm also applying a obsolescence formula that basically just says "if we sold 12.5% of our all available units over the year, then mark it as current, otherwise mark it as the last date we received it in the warehouse". This measure is named "Slow moving date". I'd like to use "slow moving date" as a row in a pivot table, but Excel says that value can't be a row. How can I force it to allow the dates as a row without having to copy/paste numbers only from a different pivot to make it work? submitted by /u/scaredycat_z [link] [comments]
- Formula to re-arrange data set to be able to create a pivot table.I am looking for a formula to re-arrange the data I have: https://preview.redd.it/vkdes6wyxqrg1.png?width=345&format=png&auto=webp&s=a30397f809384603a701af85ad807c46b9bdacf4 This is the format I want: https://preview.redd.it/43zatlm0yqrg1.png?width=450&format=png&auto=webp&s=902ea4ac22cb499f567009801ba6def7766b929e I need to do this the pay data of approx. 100 employees, each employee has a different set categories so the number of times the name needs to repeat will vary. I want to do this so I can create a pivot table, any suggestions are welcome. Using Excel365 Desktop - Beginner to intermediate level knowledge Thank you in advance. submitted by /u/Ok_Chicken_590 [link] [comments]