0

I am trying to extract unique values from multiple column. Below image demonstrate my sample data and desired output. My data will increase day by day and I want to get unique list to a separate column. I have tried below formula line feed character char(10) as separator which gives me result in a single and I then have to manually WRAP TEXT the cell. But I want result to separate cells not in same cell.

=TEXTJOIN(CHAR(10),TRUE,B4:D10)

enter image description here

I have also tried UNIQUE() formula. But this doesn't give me desired result.

=UNIQUE(B4:D10)

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • I have different approach of `FILTERXML()` formula to achieve desired result. – Harun24hr Feb 24 '21 at 03:21
  • @Harun24HR Can you please put? – Harun24hr Feb 24 '21 at 06:38
  • I can't as it is already closed. – Harun24hr Feb 24 '21 at 06:44
  • OP: does the linked duplicate approach using `FILTERXML`, `TEXTJOIN` and `UNIQUE` not solve your question? Or you can use the alternate approach in the same linked thread that overcomes `TEXTJOIN`s limitations. – BigBen Feb 24 '21 at 13:49
  • @Harun24HR - perhaps you can add your answer to the linked thread as it can definitely use more answers. – BigBen Feb 24 '21 at 13:54
  • @BigBen I will add. Basically we can directly get unique values from FILTERXML without UNIQUE function. So, answer can be used non 365 users having TEXTJOIN and FILTERXML only. – Harun24hr Feb 24 '21 at 13:58
  • @BigBen Yes it did solve my problem. But my collogues use Office2019 and they don't have FILTER function. So, It can't use to other computer of my company. – Harun24hr Feb 25 '21 at 01:49
  • @Harun24HR Can you post as you described in comment. It would be helpful for me if we can use to Office2019 excel users. – Harun24hr Feb 25 '21 at 01:50
  • 1
    @RainyDay - see [this answer](https://stackoverflow.com/a/61837697/9245853) and the part about returning unique values from `FILTERXML`. – BigBen Feb 25 '21 at 01:50
  • @BigBen Thanks for link. I will go through it. – Harun24hr Feb 25 '21 at 01:55
  • @RainyDay I have post as answer to [linked thread](https://stackoverflow.com/a/66361639/5514747). If you wish you can have a look. – Harun24hr Feb 25 '21 at 02:46
  • 1
    From link provided by BigBen I was trying accomplish to Excel2019 and facing few difficulties to adjust. Answer of Harun24HR make it clearer. Finally I adopt the solution to Excel2019. I had to use `Index()` function as `Excel2019` don't spill results automatically. Thanks to both of you. – Harun24hr Feb 25 '21 at 02:50

0 Answers0