Extracting Unique data based on case id without UNIQUE
Our take
Hello. I am trying to automate a report that my coworkers have been filling out manually for 15 years. I am able to generate a html report from our web-based database which gets imported into excel, tweaked, and spat out to Word by way of Mail merge.
I made a super simplified version of what the data looks like in excel. Cases will show up on multiple rows if there are more than one of some the data points that I am pulling (for example, multiple pets or multiple visits). (There is nothing I can do about this. It is how reports are generated.) I need a way to check the range of cases with the same ID and extract a unique list of pet names for each case. This will then be called for in extra columns till all the unique information for a case is on 1 row.
I know UNIQUE and FILTER have this capacity, but I don't think they will work for my situation. I have one sheet that the raw data goes into, and another that tweaks it for Mail merge (marking first instance, turning "5 years" into "5", etc.). I need each cell in the "2nd pet" and "3rd pet" columns to have formulas so that no matter what data I drop into the raw data sheet, it can generate a list. As it stands, UNIQUE seems to require a truly empty cell under it. I was hoping and IF function with "" outputs would work, but they did not.
I'm happy to correct to a simpler path if I've taken a wrong turn somewhere. I hold the distinction of being the most computer literate person in the office, but I am far from being an expert.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Excel - Finding Unique Values for multiple criteriaHi 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! https://preview.redd.it/wjjt6fnp85ng1.png?width=1252&format=png&auto=webp&s=cd9047b0b5af25e0444531dbcdac64c55745ec68 submitted by /u/VisualDataNewbie [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]
- Having issues counting unique instances of pick up and drop off locations based on if money is in the same row.I have a contract I'm working on for work so I will give what I can to see if someone can help. We have roughly 62k rows of information. It is a combination of pickup and dropoff locations. There are 9 pickup locations with various dropoff locations. Each pickup and dropoff are repeated 3 times based on another column. What I am looking to do is on a summary tab I want it to reference the origin location go to the full data sheet and count each row that has that origin with money and count only the first time the pickup and drop off location appears. I'm not sure if this is possible or if I just need to manually counting. For reference I've tried a combination of ROW, UNIQUE, and FILTER with various COUNTIFS or IF statements but nothing has worked. Even resorted to asking AI but either my prompts aren't correct or it just doesn't understand. Thanks in advance for any advice or tips to try! submitted by /u/Reaper4779 [link] [comments]