LinkBack URL
About LinkBacks
Hi,
I have text data in A1:A10 in Sheet1 and also in the same range in Sheet2.
I would like in Sheet3 to extract a unique list from the data in these two ranges.
Can this be done with the UNIQUE function or is there another way to do it?
Thanks!
Hi,
I have text data in A1:A10 in Sheet1 and also in the same range in Sheet2.
I would like in Sheet3 to extract a unique list from the data in these two ranges.
Can this be done with the UNIQUE function or is there another way to do it?
Thanks!
Last edited by andrewc; 01-06-2022 at 05:51 PM.
There are instructions at the top of the page explaining how to attach your sample workbook.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thanks Ali, I've added a sample workbook to the original post.
Sheet3 contains the required outcome.
With O365:
=UNIQUE(FILTERXML("<A><B>"&TEXTJOIN("</B><B>",TRUE,Sheet1!A1:A10,Sheet2!A1:A10)&"</B></A>","//B"))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
You can extend the range into blank cells to make it more future-proof, if needed:
=UNIQUE(FILTERXML("<A><B>"&TEXTJOIN("</B><B>",TRUE,Sheet1!A1:A100,Sheet2!A1:A100)&"</B></A>","//B"))
Similar option, but without using UniqueFormula:
Please Login or Register to view this content.
Fluff, interesting. I'd not have considered that structure... imagining (incorrectly) that isolated/interspersed instances of (say) AAA could be returned >1 time. However, I see they can't... Not sure your approach is better/worse... but one to (try to) remember.
No better or worse IMO.
However when using Xpath to remove the duplicates it's case sensitive, which Unique isn't. So depends on what the OP requires.
Yes, I did notice the case sensitivity when playing with it a few moments ago.
Thank you both very much!
Glad to help & thanks for the feedback.
Likewise. You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks