I am trying to place a unique formula where it captures data from multiple columns. For example, if column A had values "AA" "AA" AB" "AC" "AC" the result, using the unique formula, would spill "AA" AB" "AC" in one column. What if the values also lived in columns A through D and I need to spill them into one column? Any ideas?
It is best to upload the attachments and examples in your attachments
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Hi,
Can you update your profile? I assume you're referring to the UNIQUE function, which is not available in Office 2011.
Assuming a range of A1:D5:
=SORT(UNIQUE(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",1,A1:D5)&"</b></a>","//b")))
Note that FILTERXML only works in the desktop version. Also note that this set-up would need modifying if one or more of the cells within your range contained any of the three special characters &, <, >.
Regards
Hi - I've attached an example file of what I'm trying to do. I'm using Office 365. Thanks in advance!
Please update your forum profile - it's out-of-date (Excel 2011). Are you using Office 365 for Mac or PC? If the former, please add Mac to the profile entry. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
So how far did you get adapting the solution I posted to your workbook?
=SORT(UNIQUE(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",1,A2:A74,C2:C74,E2:E74)&"</b></a>","//b")))
Regards
Wait, if those entries are all numeric then we can do much better:
=UNIQUE(SMALL((A2:A74,C2:C74,E2:E74),SEQUENCE(COUNT(A2:A74,C2:C74,E2:E74))))
Regards
I think your solution did the trick. Thank you!
Ok, you're welcome. Definitely go with the second one I posted if we're talking about numerics, though.
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks