•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Formula to re-arrange data set to be able to create a pivot table.
Our take
To create a pivot table from your existing dataset, you'll need to rearrange your data into a structured format. This involves transforming your current layout so that each employee's name appears consistently with their corresponding categories, even if the number of entries varies. Using Excel 365, you can achieve this by employing formulas like `UNPIVOT` or combining `INDEX` and `MATCH` functions to streamline the data. By organizing your data effectively, you'll empower your analysis and make the pivot table creation process much smoother.
I am looking for a formula to re-arrange the data I have:
This is the format I want:
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.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Reorganizing a table by making the entries in original column as the columns of the new tableI am trying to automatically, or with minimal effort as possible since it will be a routine task, reorganize a data table by making the entries of one column as the columns in the new table. I appreciate any help! Here is an example of what I mean So in the above example, each run becomes a column with the respective compound results. The amount of compounds in each run can vary, and new data sets would need to be processed. Ideally, you could copy and paste a new set below the original data and have it reorganized as well. I've made some attempts with this in the past, such as using xlookup in a separate table for each compound, but its always been clunky. Thanks in advance for the help! Edit: On Office 365 submitted by /u/ConnorF42 [link] [comments]
- Pivot Table top bar date filter doesn't update in chronological orderI have a an excel file which I had been building from scratch from a downloaded report every month and am trying to better automate since I do the same type of data filtering each month. I decided to create a table with the data that is linked to a pivot table which sorts and filters it into the format that I need as long as I manually adjust a couple things. However, whenever I add new data to the table, the date filter at the top of the pivot table does not show in chronological order. (Accountant working on April data and need to remove anything that was posted in May.) Theoretically I could use the timeline feature, but I'd rather not. I have resorted the source table in chronological order; I have removed the filter and re-added it in various formats. I've changed the data source, played with various settings. Is there a quick "fix" for this without manually creating the pivot table each time? I have several of these pivot tables on a sheet extracting different data sets sourced from the same table. In one of them, I need to see ONLY the items with a May date, so having to scroll through to check or uncheck the correct dates if they are out of order could lead to errors. Additionally, assuming that this can be corrected. Is there a way for an excel sheet to automatically add or remove row (by hiding or deleting, don't care which) as a pivot table gets longer or shorter so I don't have to guess how many rows I need to add before it cuts into other data below it? Sometimes I need 70+ rows and others only 30. I'm sure there's an overall better way to do what I'm doing. Thanks in advance. https://preview.redd.it/56optogx0qzg1.png?width=406&format=png&auto=webp&s=551180d29effbcfedb7038f60612e33f4e897d3d submitted by /u/inez6453 [link] [comments]
- Sorting and rearranging data using Pivot TableI was given a task where I need to rearrange the Drawing ID in ascending order and in each drawing ID, there has its own no. of pieces (qty). I used chatgpt and it said to use Pivot Table. It was my first time using Pivot Table and I dont get the result I wanted. It went well when summing up all the pieces for each drawing ID from different group no. But the drawing ID is not in its ascending order. Data given Pivot Table From the Pivot Table, the Drawing ID is not in ascending order. Is Pivot Table the only way? submitted by /u/One_Wishbone_4439 [link] [comments]
- How organize data to January - December instead of A to Z?How to organize data from January to December instead of A to Z? Right now, I am trying to create a pivot table, and it is showing April as the first month. When I want January instead. https://preview.redd.it/be7oi5uldeyg1.png?width=576&format=png&auto=webp&s=a0dbc6ae30bd324512bfe94e764fb3545c669881 submitted by /u/heyitsmereddit [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#big data management in spreadsheets#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#AI formula generation techniques#rows.com#formula generator#natural language processing for spreadsheets#enterprise-level spreadsheet solutions#Excel compatibility#Excel alternatives#pivot table#Excel365