•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Sorting Data from Highest to Lowest
Our take
To effectively sort and summarize your data from Sheet 1 into the blank table on Sheet 2, you'll leverage formulas to capture key information. Begin by calculating the total VPs for each supplier, focusing on the top 15 entries sorted from highest to lowest. For the 1, 2, and 3 columns, extract the top three VP codes for each supplier, ensuring that if only one code exists, it occupies the first position.
Data Table on Sheet 1:
Relevant data range between D:L; Cells are from 5-2000 only
Blank Table on Sheet 2:
Pre-made data tracker I am trying to figure out
- Essentially, I am trying to filling the table in Sheet 2 only using formula's
What I am trying to achieve (from left to right based on Blank Table in Sheet 2):
- For the Supplier and Total VPs columns, I want to have the sum of VP codes (there are multiple codes and I want the sum of any of these codes per supplier) used for each specific Supplier in Sheet 1; BUT only capturing the top 15 from highest to lowest
- For the 1, 2 and 3 columns, I want to have the top 3 codes used for that supplier and if only one code is used, only the top spot will be filled
- For the Main Issue column, I want this to be the highest occurrence issue which is pre-filled when entering codes and this data will be in the VP description section of the data table in Sheet 1
I apologise if my clarification is unclear and am happy to elaborate!
Thank you
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How to autofill data from a row to a column on a different sheet in the same folder?I've been struggling with some solutions I've found on the forum but after 1.5hrs I'm close to giving up and manually entering data - which is bound to cost me another 28 hrs. Hoping someone has the solution I'm looking for and is willing to share.. I've exported questionnaire results from Mentimeter to Excel. The document output is formatted automatically in a way that uses columns for unique respondents, followed by their answers in the same column but along individual cells on that column's row, meaning the first entry is A2 and the last entry is in cell CQ2 or something. I would like to make this more user-friendly by: 1) putting each respondent's answers in their own sheet in the folder, and 2) by listing the questions in the first column and the answers in the next columns pretty much 'the other way around'. Currently it looks like this; the answers I need are listed in !VotersF3 to !VotersCQ3. The next respondent's answers are in !VotersF4 through to !VotersCQ4 and so on. What I'm looking for would ideally display answers in !AnswerA3 through to A80. When I manually select !AnswerA3 and click on !VotersF3, logically it does what I want. When I then drag down to autofill, equally logically the sheet enters !VotersF4 instead of !VotersB3 as it's a row vs column problem. I've tried different version of INDEX and TRANSPOSE but I can't get a working formula from that. Would anyone be able to provide me with the correct solution for doing this? I've got another 20+ respondents answers that need to be 'easy to view' instead of scrolling 500 screens horizontally.... Thank you Excel wizards! :) submitted by /u/Lost_Mud2097 [link] [comments]
- Data Automation - Pulling data from a sheet but also blocking cellsMy main purpose for this is to be able to create a trackable booking spreadsheet, the main input page is where the kids are booked onto sessions this is then checked against when they enrol and leave. I have Data in my 'Input' sheet this is split into 5 sessions per day 5 days. Column A is the name Column B:F is Monday 5 sessions Column G:K is Tuesday 5 sessions and so on I then have a sheet 'Apr 26' which then pulls this data into the monthly sheet The data i have in my input sheet can have anywhere between 1 cell to 12 cells per session, my formula works if there is 2 cells or more per session but doesnt work if only 1 cell it puts that cells data over multiple cell. Excel Formula Code (PasteBin) Excel Doc Link - ignore sheets past May 26 as they dont have the updated code or conditional formatting in. Excel Doc Link (Non Macro) This is my main input data sheet This is the sheet and cell (highlighted) that the formula relates to As you can see in the above images on Monday on the ASC 5pm column it multiples the data (child 1) If I have 2 lots of data in the ASC 5pm Monday column on the input sheet the formula works properly. Input sheet is how sessions per child are booked. sessions can only have 12 in however on certain days due to staffing can only have a certain number in (say 9) the formula im having trouble with is on sheet Apr 26 on column F it repeats the 'child 1' cell multiple times rather than just doing It once but as you can see on column K the formula works but just not when its got 1 cell of data Version Excel 2024 on Mac but needs to work on windows as well submitted by /u/BarrowBluebird [link] [comments]
- Monthly tracking workbook I use to track employee sales metrics; Trying to find a way to make the process less labour intensiveTruly having a hard to describing my issue effectively but hoping someone can help. First time posting here and I'm by no means an expert with excel, so please be kind! I have a monthly workbook where I track each employees revenue and other metrics. Every 2 weeks for payroll, I provide a print out of these numbers, and the payroll sheet pulls data from multiple sheets in the workbook. For example, every workbook has a separate sheet for each day of the month, titled "1" through "31". I have pay period sheets, so I'll use one titled "04.02.26 - 04.15.26". Then I'll have the data for each employee pulled from multiple sheets. For example, I use the formula =SUM('2:15'!E3) to pull the sales data from each day of that period for the specific employee. This works quite well. However, when I create a new month's spreadsheet, I have to manually alter this formula for each employee and for each data point (more than just revenue, at least 6 different data points for 7 employees). Is there a way to automate this? For example, a cell or two where I'm able to enter the date range and all of the formulas update to that date range for the corresponding pages? I'm sorry of this post is confusing. Truly it's confusing even typing it! submitted by /u/Ok_Smile9222 [link] [comments]
- Helping a coworker- How to return a row with three criteria in an existing sheet?https://preview.redd.it/qpc1n7v6hnrg1.png?width=454&format=png&auto=webp&s=b418c6890f9257bc9e55d32d2d5fee93a0a02a31 Hey all , I KNOW there's an answer staring me in the face. I played around with PQ but didn't see what I liked. My coworker has data that looks like this In a new workbook, she wants to take this above data and return the number in C1 (and D1, and E1, etc.) in column A. But not just pasting vertically, returning it for every line of item#s. So Order A, Order A, Order A, etc. She also wants the item# returned in column B of the new book. Short of copying and pasting, I don't know a good way to do that. Finally, once she has those we can do an old fashioned lookup to get the qty to return in Column C, we're good there. Any thoughts? And please don't just say "PQ" and leave. If your answer is PQ, please give detailed instructions and advice. Editing to add output required: https://preview.redd.it/q3hcvmf1jnrg1.png?width=274&format=png&auto=webp&s=99dbd555d964a0f2aa3a09676efa27ae1af887f9 submitted by /u/Knitchick82 [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#natural language processing for spreadsheets#rows.com#AI formula generation techniques#Excel compatibility#cloud-based spreadsheet applications#no-code spreadsheet solutions#formula generator#Excel alternatives#Data Table