Consolidate by category
I am trying the following practice challenge in an online course. I cannot for the life of me figure out the solution for step 7. Any help would be GREATLY appreciated "The next few instructions are a bit tricky. You should not need to add any columns to achieve these tasks. These tasks are similar to what was in the Consolidate by Category (Reference) video. In that video, we consolidated sheets that had different categories and in a different order by selecting Use labels in: Left column. Consolidate by Category can also condense multiple rows with the same category down to a single row per category. When you select the references, note that you will need to select at least 2 columns. The first column will be used for the labels and the other column(s) will be consolidated using the Function that you select in the Consolidate dialog. STEP 7: Use the Consolidate tool to generate a summary of the number of tickets raised per priority for May Week 4, June Week 1 and June Week 2 (combined). Sort the consolidated data by Priority. STEP 8: Use the Consolidate tool to generate a summary of the average number of days a ticket was open per priority for May Week 4, June Week 1 and June Week 2. Sort the consolidated data by Priority and change number of decimal places to 2 (change the format, do not use a rounding function). IMPORTANT: Before the next step, make sure you delete the other references in the Consolidate tool! STEP 9: Use the Consolidate tool to generate a summary of the number of tickets given each satisfaction rating for May Week 4, June Week 1 and June Week 2. Use a COUNT function. Sort the consolidated data by Satisfaction Rating." Links to excel files https://d3c33hcgiwev3.cloudfront.net/QsR8jRaoEeiV3A7PVRrSig_43591af016a811e8a3c03fd758b931b1_W1_AdvPracticeChallenge.xlsx?Expires=1764100925&Signature=ayov~GEp79FgN-SAkfGConpKqHsqzwlbG8M0jrn02RwCODRz9HsQXaHNI8IfYvEGy6I68FiyPqLJglNKFW3MBfk4AjEdeSv4dB2NiPrQTqwPFXQOvLGs3THeY7vSJiKj31--Y93AJkiyIfjQBZZyO640jN0cXLZpKl5DXLoBaDM_&Key-Pair-Id=APKAJLTNE6QMUY6HBC5A https://d3c33hcgiwev3.cloudfront.net/_f48c8a2f4c6065a2a708dc316cbcf999_Help-Desk-April-Summary.xlsx?Expires=1764100925&Signature=EnuRHABT4F-ZT925yZKt2cZzr-HYIKEod2gx1-kObdLIzueISMkjx79svRv8ZVWwVO~Y5uteVJNvS5KvdjFh2daaNvIX9OXsw5wXcKaN6zkNqVrjLe21drPYC8JIqW36Z2G3Rxpq-eHwRyKCHlBfxu85zSuTzybcjcP9hFnQ-XA_&Key-Pair-Id=APKAJLTNE6QMUY6HBC5A https://d3c33hcgiwev3.cloudfront.net/_3ffee287bc89bcdd309bfe7d8e5b00da_Help-Desk-May.xlsx?Expires=1764100925&Signature=axvArXcDo65he42Pk13z99VoJ4HS3kpzeTEMmHOEAQ9B0ou444oPGWb4Pp65MEKasrQoMQZ9A40lr2sqXqEYexJk~vENmc9Y2dGDwiEabfxQ4liQbf58sME1cUvrom26neAH40zScJbWh7rFwNZIfI7060YRYVDw4D736pH64zY_&Key-Pair-Id=APKAJLTNE6QMUY6HBC5A https://d3c33hcgiwev3.cloudfront.net/_f0e969b80e0ab5054d797a0644bd82eb_Help-Desk-June.xlsx?Expires=1764100925&Signature=LjcstfMamG2vqJqd2vcD9hiQciNCa48ngWqswE~Al7KgjCvu6M~RHzQ65G5W~Q0dAjfCmikPvIx7j4dZuCXnKWhfE3ULqy3JuJy7EExzsVCUVubmsftcP5tqTRo6No9rtPtBid1e4qkMpyE9Zwj0ny3rJn2LDSulWNYuJNXTIBw_&Key-Pair-Id=APKAJLTNE6QMUY6HBC5A
i need the data from all the sheets in the workbook to link to one data sheet.
A colleague of mine made a data spreadsheet (lets call it SHEET 1), he no longer works for the company and over time the worksheet has been amended, so doesn't work like it should. There are several sheets in the workbook and all data that is copied into these needs to go into SHEET 1 (doing it itself, not manually). My question is when I download my data and paste into SHEET 2, how do I get it to automatically go on to the SHEET 1, taking only certain parts of the data, in this case B and F. The attached is just a small example of the data I need from SHEET 2. On SHEET 1 There is a list of numbers on the left and more, how can I get this data to filter itself onto SHEET 1 in the right column then adding the numbers on the left together. So, on SHEET 1, 160 -T will show 7 because there is 7 1's below for that Org. Sorry if i have made this sound long winded, I am awful at explaining. I have basic knowledge of excel but I cannot get my head around formulas. It should look something like this. Any advice would be great. Thanksmulti Vehicle maintenance log record
i need to make a vehicle maintenance log that will have 7 different vehicles, track all the oil changes, inspections, registrations that will notify us when something is expiring, and be able to attach receipts and documents to it. what is the best way to do this?
Summing rows values while a specific column answers a specific condition.
Its much smaller than the sheet I work with but for this example I want to scan column 1 representative and if the value in a cell form A2:A6="D" I want to sum its rows. in this case I expect the answer 3+1+8+1=13. Tried the sumif but it works for 1 column at a time and not scanning multiplied arrays. Tried sumproduct and still couldnt get the cell to show the total amount. since in my actual worksheet there are 37X57 array, going manually and put formula in each specific row is less ideal soo I am looking for a specific command to solve it and calculate along the data that changes on the sheet.Double Thunking Works Wonders!
Given that most Excel users would not dream of employing one thunk, you might well ask why even consider nested thunks! The use case explored here is to return all the combinations by which one might choose m objects from n (not just a count of options =COMBIN(n, m), but the actual combinations) Knowing that sometimes allows one to deploy an exhaustive search of options to determine the best strategy for a task. Before considering the task further, one might ask 'what is a thunk; isn't it far too complicated to be useful?' All it is, is a LAMBDA function that evaluates a formula when used, the same as any other function. The formula could be an expensive calculation or, rather better, no more than a simple lookup of a term from a previously calculated array. The point is, that whilst 'arrays of arrays' are not currently supported in Excel, an array of functions is fine, after all, an unrun function is little more than a text string. Only when evaluated, does one recover an array. In the example challenge, each cell contains an list/array of binary numbers, which might itself run into the hundreds of terms. A '1' represents a selected object whilst a '0' is an omitted object. Rather like the counts of combinations obtained from Pascal's triangle, each cell is derived from the contents of the cell to the left and the cell above. This is SCAN on steroids, accumulating array results in two directions. Running down the sheet, the new combination contains those of the above cell, but all the objects are shifted left and an empty slot appears to the right. These values are appended to those from the left, in which the member objects are shifted left but the new object is added to the right. So the challenge is to build a 2D array, each member of which is itself an array. The contents of each cell is represented by a thunk; each row is therefore an array of thunks which, for REDUCE to treat it as a single entity, requires it to be securely tucked inside its own LAMBDA, to become a thunk containing thunks. Each pair of rows defined by REDUCE is itself SCANned left to right to evaluate the new row. By comparison the 2D SCAN required for the Levenshtein distance which measure the similarity of text strings was a pushover. I am not expecting a great amount of discussion to stem from this post but, if it encourages just a few to be a little more adventurous in the way they exploit Excel, its job will be done! p.s. The title of this discussion borrows from the Double Diamond advert for beer in the 1960s
max corresponding to a value which may be in 1 or multiple sets
Hi G6 is showing error because F6 is not found in D15:D50. what would be the simple process that F6 is searched in D15:D50 and D56:D91 and returns value from col K instead of an error whether or not that value exist in either of the sets.How to Be an Excel Detective: Finding and Highlighting Formulas
Hi everyone, I recently wrote a blog post on some simple, yet powerful, techniques for anyone who works with Excel spreadsheets, especially those with complex data. I wanted to share a summary of it with this community, as it might be helpful to others who are looking to understand and protect their work. The post covers two main things: Quickly Revealing All Formulas: A simple keyboard shortcut (Ctrl + `) or the "Show Formulas" option can instantly reveal all formulas in a worksheet. This is a great way to quickly see how a spreadsheet is structured. Permanently Highlighting Formulas: The article shows how to use the "Find & Select" > "Formulas" feature to select all cells containing formulas and then permanently highlight them with a fill color. This visual cue can help prevent accidental edits and protect your data. Watch This in Action: For a step-by-step guide on how to use these techniques, you can watch the video on my https://www.youtube.com/@BIGurus. 🔗 https://youtu.be/1x-1dbqlWXk You can also read the full article here: https://medium.com/@anandsharad/how-to-be-an-excel-detective-finding-and-highlighting-formulas-fe9d4fdbc1b1 I'd be happy to answer any questions you have or discuss other Excel tips and tricks in the comments!
EXCEL FORMULA HELP
I need help. I have attached the sheet I'm working on. I need to pull the data in column B (CLIENT) from the MAIN sheet to the respective individual sheet based on column E (PREPPER). For example, if the data in B4 says 'Jason Smith' and E4 says 'Jon'; I need it to go to the JON sheet in cell B4 but not show up on sheets JANE or MARLA. Help please!! Thank you! :)
