1

Apologies if this is something really obvious but egads it's driving me crazy and I can't find a non-array/helper box answer.

Excel table

I want to search cells D2 and E2 for matching numbers, and then concatenate these (using TEXTJOIN?) for each row in cell F2 (and then copy the formula down the column). So for eg I can see that D2 and E2 share unit 2, and cells D4 and E4 both have units 1 and 2 - so I'd like F4 to show "1,2". I want it to search for any and all numbers that match across both columns and return this in F as a comma-delimited concatenated string. Any help gratefully received.

Emma_P
  • 35
  • 5
  • 3
    You can use [`FILTERXML()`](https://stackoverflow.com/q/61837696/9758194) tricks to retrieve duplicates. For example: `=TEXTJOIN(",",,FILTERXML(""&SUBSTITUTE(D2&","&E2,",","")&"","//s[following::*=.]"))`. – JvdV Jan 17 '22 at 19:08
  • Do you have Windows and Office 365? (A combination of TEXTJOIN, FILTER, FILTERXML, and COUNTIF would accomplish that) – P.b Jan 17 '22 at 19:10
  • hyu~.. really need to know what the `//s[following::*=.]` does there.. – p._phidot_ Jan 17 '22 at 19:10
  • Cool didn't know you could filter out duplicates that way in `FILTERXML` – P.b Jan 17 '22 at 21:25
  • Thanks so much for your replies. I tried copying and pasting but am getting a #VALUE! error and no idea how to resolve it - I'd not encountered FILTERXML before. Would it matter that cells D2 and E2 contain formulae, i.e don't simply contain the text 1,2,17 etc etc? – Emma_P Jan 18 '22 at 09:05
  • I also found this page on extracting substrings from Excel https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml in case it's helpful for anyone who ends up at this question - off to scour it now. – Emma_P Jan 18 '22 at 09:09
  • What is your version of Excel? 2019? 365? Depending on your version you'd need to array-enter the formula. Also, if no duplicates are found, the formula will return an `#VALUE` error. Note that it doesn't matter that the values are the result of formulae. – JvdV Jan 18 '22 at 10:11
  • Thank you - I'm using Office 365 on Windows 10; it's returning an error when I paste it into F2, but should be picking up unit 2 as a duplicate across D2 and E2. I'll try to add an updated screengrab in case there's something I'm missing (highly likely). – Emma_P Jan 18 '22 at 12:08
  • It works fine for me. Can you confirm you actually need to use the comma to seperate parameters in a formula? Some countries locale setting use the semi-colon. E.g.: `=TEXTJOIN(",";;FILTERXML(".......` – JvdV Jan 18 '22 at 17:27
  • I've got TEXTJOIN working with comma delimiters elsewhere in the workbook so I guess it's not that. Thank you for trying though! – Emma_P Jan 19 '22 at 16:10
  • It's hard to troubleshoot something that works on my end without the data, but are there any characters other than space, comma or numbers in those cells? Invisible characters like tab, newline or non-break spaces? – JvdV Jan 20 '22 at 08:48
  • Weirdly it has now started working - maybe the old switch it off and switch it off again did the trick! One more question - where it doesn't find a match it's returning #Value! - is there a way to change this to "None" or just have the cell blank? – Emma_P Jan 20 '22 at 10:50
  • 1
    Yes, wrap the whole thin in `IFERROR()` – JvdV Jan 20 '22 at 13:38

1 Answers1

0

As per the comments you can use FILTERXML() to retrieve the correct data:

=IFERROR(TEXTJOIN(",",,FILTERXML("<t><s>"&SUBSTITUTE(D2&","&E2,",","</s><s>")&"</s></t>","//s[following::*=.]")),"None")

For tips & tricks on how to use FILTERXML(), please see this post here on SO.

JvdV
  • 70,606
  • 8
  • 39
  • 70