•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Gather unique names to show multiple columns as well.
Our take
G'day team! It sounds like you're navigating a common challenge when working with CSV data. To extract unique IDs while displaying corresponding suburbs and postcodes, consider using a combination of functions tailored for this task. While XLOOKUP and UNIQUE are powerful, you might find success with the FILTER function alongside UNIQUE. This approach can streamline your data, ensuring that each unique ID is paired with its respective suburb and postcode, making it easier to integrate with your GIS report. Let's tackle this together!
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.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Need better way to organise spreadsheethttps://preview.redd.it/oiuewnp0pstg1.png?width=599&format=png&auto=webp&s=d34be7eaeb702d5ded087538a2292a57b295dd65 I am currently keeping track of visiting football stadiums (often called the 72 or the 92) as you can see on the attached image, the shear amount of anfield visits leads to many duplicate entries and am wondering if anyone has a suggestion on tidying this up, so that anfield appears once, but perhaps links through so that i can see all visits there will also likely be more stadiums in the future that will have multiple duplicates. *several columns to the right cropped off because of personal data. submitted by /u/EpilepticFlshbng [link] [comments]
- How to handle data from different sources when columns are in different orders?I regularly get CSV exports from multiple clients. Each client uses their own column order. One puts names in column A and dates in column B, another swaps them. Manually rearranging every time is driving me crazy. What's your go-to method for standardizing columns from different sources? Power Query seems powerful but I'm not sure where to start. I've tried INDEX/MATCH with header lookups, but it gets messy when column names vary slightly. Also open to VBA solutions if they're reusable. Any tips or templates you'd recommend? submitted by /u/biggy_boy17 [link] [comments]
- Merge cells dependent on a conditionteat me as a beginning beginner pre-beginner level user 4 columns - uniqueid, email, name, company I will export to CSV and can import only the first 3 into the program. Before exporting, within Excel, how would you attack this : Where name is missing, populate it with the company if it exists uniqueid,name,email,company 00011,CindyLou Who,cwho@company.tld, 00012,,grinch@company2.tld,Grinchdom 00013,,sally@company3.tld, 00014,Patrick,pat@spongebob.tld,Spongey 00015,,bob@spongebob.tld,Spongey desired: uniqueid,name,email 00011,CindyLou Who,cwho@company.tld 00012,Grinchdom,grinch@company2.tld 00013,,sally@company3.tld 00014,Patrick,pat@spongebob.tld 00015,Spongey,bob@spongebob.tld submitted by /u/GeekgirlOtt [link] [comments]
- I need to do a complex sort involving large amounts of data.Hello, I have posted before HERE. To reiterate, I work at a library and I essentially need to do a review of hundreds of thousands of lines of data compiling information about different periodical volumes into one line. They are technically all different volumes (and there is a column for that) but can be organized under a single periodical title. The raw output data will look something like this: Periodical A / Volume 1 / Last Loaned Date / Usage Number Periodical A / Volume 2 / Last Loaned Date / Usage Number Periodical A / Volume 3 / Last Loaned Date / Usage Number Periodical A / Volume 4 / Last Loaned Date / Usage Number Periodical B / Volume 1 / Last Loaned Date / Usage Number Periodical B / Volume 2 / Last Loaned Date / Usage Number etc. What I need to do is find some way of sorting or filtering or some algorithm that can first, take only the most recent date from the Last Loaned Date column among all volumes, and eliminate all others. Second, take the sum of all Usage Numbers for all volumes of that periodical and put it into a single number. Third, combine these two things into one line for the periodical in question. So the output should look something like: Periodical A / Last Loaned Date (most recent) / Usage Number (total among all) Periodical B / Last Loaned Date (most recent) / Usage Number (total among all) In this way I hope to get hundreds of thousands of lines of data down to about 50,000, representing the number of different periodicals we hold at the library. I have now moved to Excel 365 and am trying to implement the solution recommended by u/Downtown-Economics26 HERE: LINK. However, even the test case is not working for me. I am instead getting a NAME? error in the execution. This is what I see: LINK. I think I am using the exact same function as him, and I have Excel 365 so I can use DROP and GROUPBY functions, but I am still getting an error. Does anyone know why this might be the case? submitted by /u/-Ophidian- [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#rows.com#natural language processing for spreadsheets#big data management in spreadsheets#conversational data analysis#Excel compatibility#real-time data collaboration#financial modeling with spreadsheets#intelligent data visualization#no-code spreadsheet solutions#data visualization tools#enterprise data management#big data performance#Excel alternatives#data analysis tools#data cleaning solutions