•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Excel - Finding Unique Values for multiple criteria
Our take
Hi folks! If you're looking to analyze a large dataset of services provided by your organization, you may find it challenging to extract unique values based on multiple criteria in Excel. Specifically, you want to determine how many unique families requested each type of produce and how many sought multiple types. While pivot tables can offer some insights, they may not be sufficient for your needs. Let’s explore effective formulas and approaches to help you achieve accurate results for your analysis.
Hi folks, I can't figure out the formulas or terms I need to search to make this happen. I'm trying to analyze a large data set of services my org provided (400+ rows). The image is a simplified version of the data and pivot table I could figure out so far.
I'm trying to determine two things:
1) Unique count of families who sought each type of produce? In the pivot table I can see 3 families requested apples, 3 requested bananas, 2 requested carrots but this isn't a viable approach for the actual data set.
2) How many families sought 2+ types of produce? In the pivot table, 3 families sought 2+ types of produce.
Thank you for your help!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How to organize a sheet based on how many times a certain value in a column is duplicated, and have all other columns follow?Hope the title is descriptive enough... i feel like i always struggle to describe excel stuff efficiently. I am a complete excel beginner! So i have a dataset that is 3000+ rows long. For the sake of ease (and also to not share PHI), I made a shortened 27-row long example; this is what is shown in the screenshots. The data I'm working with is downloaded from a website we use to give people questionnaires. Sometimes, people do not have very straightforward answers to each question, so we type in "comments" in those cases to help clarify the exact answer participants gave. The data I'm working with lists the ID number we gave to each participant; the "variable" AKA the name of the question (tells me exactly which question it is in our questionnaire); which "session" AKA appointment the question is from (we repeat the questionnaire multiple times per participant throughout a year, some in-person, some over the phone); which coworker left the comment (commenter); and finally the actual comment itself. We are trying to see which questions/variables were given comments most often. This is a replica of what I have: https://preview.redd.it/9lvlnwplfgng1.png?width=1025&format=png&auto=webp&s=4716f993d500383af6307b8fb1724fef5b8626e1 This is what i WANT it to sort of look like: https://preview.redd.it/3d4w5a6ofgng1.png?width=980&format=png&auto=webp&s=dd32c658aceadd0afe5c427f0ec19177226d5081 In other words, I want: A count of how many times each type of variable repeats in the excel file. the dataset to be organized from which variable appears the MOST often at the top, and the ones that show up the LEAST at the bottom. to get rid of all rows that were for a "test" participant (notice the "test" rows 8, 18, and 26 in the first screenshot are gone in the second). to be able to find an exact question from the questionnaire website based on this sheet. for example, if I wanted to look more into the context behind row 5's comment, I would know to go to our questionnaire website, go to participant 111's questionnaires from the Lab 1 appointment and specifically look at the alcohol_amount1 question. In other words it is important to keep the participant number and session information. It doesn't need to look exactly like the second picture, that was just the first way to organize it that came to mind. As long as it fills the above requirements that all I need that's what matters. I was trying out pivot tables but I couldn't really get it to look in a way that made sense to me. I really don't know what else to do besides comb through all 3000+ rows one by one... sorry if any of this doesnt fit the exact posting rules. I tried. thanks for any help in advance🥹 submitted by /u/soupysyrup [link] [comments]
- Gather unique names to show multiple columns as well.G'day team. I am stuck at what should be a simple issue but my brain has gone away on holidays it would seem. I have a csv of events of the previous month which has multiple ID's (col A)that match Suburbs (col B) and postcodes (zip codes) (col C). I am attempting to sort the unique ID numbers to show the matching Suburb and Post Code. I have tried XLOOKUP, UNQUIE, GROUPBY and other Youtube hints but nothing working. I know it will be something simple but my brain is just not working the problem today. The data will be joined with Lat & Long coordinates for inclusion into a GIS report. Any help greatly appreciated. https://preview.redd.it/f67jzgbh5itg1.png?width=316&format=png&auto=webp&s=7c4f7839f66bcb0418762ccce5efeffd0b41f96e submitted by /u/Aussiediver [link] [comments]
- Made a pivot table to count unique values in a list of data - is there a way to combine unique values to count together in one row?I have a list of names, and made a pivot table to count how often the names appear in the list. That I can do no problem. However, some names are entered for formatted in different ways, for example John Doe vs John D. vs John -- will all count separately in different rows. Is there a way to maybe manually combine the rows into a single count? Without having to manually edit the data itself? submitted by /u/rs37982 [link] [comments]
- Sum unique values in Pivot Table without Data ModelI have a spreadsheet that tracks the surgical cases our practice sees. In this spreadsheet, each case is its own row. Column A is the date. Column B is the primary doctor assigned to the case. Column C lists which doctors are present at the practice that day (ex: "Dr.X" or ""Dr.Y" or "Dr.X/Dr.Y"). The first case of the day gets a "1" in Column D. I am trying to use pivot tables to create a summary of some metrics for the surgeries. Part of the stats we look at are how many surgeries a doctor performed when there are two working that day vs just one. The issue I'm running into is when I try to run statistics by the month, filtered by Column B and Column C, I cannot get an accurate count of the number of days a doctor worked that month. If I want to run stats for Dr.X on days both doctors worked, Column D only gets added if Dr.X was assigned to the first case of that day. I know that you can add unique values by using the Data Model feature, but this isn't available on Mac. Is there any other way I can calculate this? submitted by /u/anarchybats [link] [comments]