•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Combine data from multiple columns into one, separated by commas but without duplicates.
Our take
If you're managing a large email audience list and struggling with duplicate entries in your "Department" and "Major" columns, there's a way to streamline your data. By consolidating these fields into a single column without duplicates, you can enhance clarity and organization. The goal is to create a formula that merges entries while intelligently avoiding repetition, ensuring that each department or major is listed only once and separated by commas.
Hello! I currently have a very messy email audience list of nearly 14,000 that somehow has multiple duplicate fields. Basically, I need to consolidate the "Department" and "Major" columns into 1 of each. I've been toying with this issue for a while with some IF/THEN functions, but it gets tricky (for me, at least) when there are multiple departments/majors. I don't want duplicates in the Department/Major columns, but I do want non-duplicates pulled from the subsequent fields to be separated by commas. For example, in the 2nd row shown in the SS I would want the department cell to say "Library Studies, Interior Design" (rather than "Library Studies, Library Studies, Interior Design).
I essentially want Excel to do the following: if I is not blank and J+K are blank, don't mess with it. If I is not blank and does not contain the text in J+K, add the text from J/K to I, separated by a comma. If I is blank, fill with text from J (and from K, separated by a comma, if K does not contain the text in J). If I and J are blank, fill with column K.
Thanks in advance to anyone who can help out with this!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Cell merging / formatting formulasThis might be an odd one. I'm not that skilled with excel as my use of in within my job is pretty limited. However, I tend to use this template my predecessor made to summarize data from our program. Works well, just a simple ='SHEET 1'!A1 for all cells. The first two images give an example. After the data is ported, I have to get rid of the zeros between the data and write system names. When it comes to pasting it on letters, the names are bolded, upped a font size, and two of the cells are merged (3rd image) This gets a bit tedious as the lists can get pretty long so I've been trying to figure out how to streamline it on my own. My idea has so far has been to have a separate cell detect when I'm finished adding my data and then format the aforementioned cells (4th image). For the life of me, just can't figure out how to write a formula to do it. What I would need is for the formula to detect a 1 (could be anything) in cell G10. It would then check for any blank cells in columns A and B. Once found, it would merge & center, bold the text, increase the font size, and align right. Is this only possible with a macro? I've been unable to find any formulas that could accomplish this. https://preview.redd.it/853mpr4jspog1.png?width=788&format=png&auto=webp&s=943a34a57f1d7c7f88256dd89b81b9c6fc301e34 https://preview.redd.it/e3w6ms4jspog1.png?width=453&format=png&auto=webp&s=9b189fa77d6638b627f8676dad60bc148753617c https://preview.redd.it/msh6ys4jspog1.png?width=411&format=png&auto=webp&s=d5f5025b315f24fd5f3a64c6e07e019abd3a77ab https://preview.redd.it/7j66tt4jspog1.png?width=936&format=png&auto=webp&s=1faa4df12ee74892f5fa9dc27ac95621280cf3c5 submitted by /u/Extension_Train9093 [link] [comments]
- advice Excel cleanup approachNeed advice on whether my Excel cleanup approach was the best solution I was asked at work to modify an Excel table with 10 columns. Half of the columns contained company-related data, while the other half contained agent-related data. The requirement was a bit specific: Company rows could still repeat and needed to stay in the dataset. But the agent-side data should not be counted multiple times if it was duplicated, because it was affecting totals and making the agent calculations inaccurate. What I ended up doing was: Using the agent-related text columns to identify duplicate rows. If a row was considered a duplicate from the agent side, I set the quantity/numeric values for the duplicated agent data to 0. After that, I made those duplicate cells white in Excel so they wouldn’t stand out visually. It works for the totals/calculations now, but I’m wondering if this was actually a good approach or if there’s a cleaner/more professional way to handle this in Excel or Power Query? submitted by /u/Resident_Quantity827 [link] [comments]
- join content from cells in a column without losing content from the corresponding columns.basically how do i make the highlighted screenshot look like the unhighlighted one but with a function for a spreadsheet with like 170k rows. sorry that this is in sheets. im trying to figure out if what i need is to buy excel basically what i want is to condense duplicative info while listing/joining the different pieces, all controlled for case number. https://preview.redd.it/4rfjrucnjdzg1.png?width=1408&format=png&auto=webp&s=ff424cf0a8553fe30b769986169901c1ca25a3bd https://preview.redd.it/c8mtq58ojdzg1.png?width=1080&format=png&auto=webp&s=21ccbdfd8ae8d41eb2d69368e62163df3737088b submitted by /u/Abi-Ankeney-PMM [link] [comments]
- Consolidate data from different worksheets with final information coming from a combination of the worksheetsHoping for some help on a problem I can't wrap my head around. I need to consolidate some information from 12 different tabs (one data pull per month) into one worksheet with some of the data needing to be overridden and some needing to stay. With the most recent pull of the data not necessarily being the information I want showing, I'm not sure how to proceed. I'm trying to find a way to create this and pass it along to someone else to just load data and it automatically puts out the result I'm looking for. For instance, I pull information from my system in January for the entire year (broken into monthly buckets) and all of that is perfect. My second data pull of the year in February isn't so clean. I need to see the numbers from January on any of the January data (because it zeros out in February in my system) but everything related to February through December needs to be from the most recent data pull. Third pull of the year in March, I need to see the numbers from January and February for the January and February data and then need the new info for March through December. Basically, by the time December rolls around, I need data from all 12 sheets. Hopefully this makes sense. Any thoughts on how I can accomplish this in Excel? submitted by /u/megd726 [link] [comments]
Tagged with
#financial modeling with spreadsheets#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#Excel compatibility#Excel alternatives#rows.com#row zero#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#Excel#duplicate fields#Department