-1

i have three columns in excel, one that holds the row id, the next one a product serial number, and the last one again some serial numbers. 

098 11079851   11079851
110 11089385   25853201
118 11089385   22412115
798 11079851   22412115

what i need to do, is to find if the value in column B, exists in column C. Then, i need to have a way, to have the values of column B, that were found in column C, grouped, along with their corresponding row ids.

example output of 11079851 that exists in column C: 11079851: 098, 798 perhaps one cell had the row id, and the next ones, each one having a corresponding number from column B, that may be found in column C.

in excel this is what i use so as to get the existance of each cell's value of column B in column C:

=NOT(ISERROR(MATCH(B2,$C$2:$C$1000,0)))

since i do not have excel version required (even 2016 will not have textjoin), i tried the suggested formula from below answer, 

=IF(COUNTIF($C$1:$C$4,B1),B1&": "&TEXTJOIN(",",TRUE,IF($B$1:$B$4=B1,$A$1:$A$4,""))"")

but i get a 509 error. What am i missing?

  • Run your formula in a helper columns, then either sort or filter on that column – cybernetic.nomad May 17 '19 at 16:13
  • could you please elaborate? i have the helper formula in a fourth column, which returns TRUE for the row ids i want. But how do i group them to get the expected output? in a cell, i need such output: 11079851: 098, 798 –  May 17 '19 at 16:28
  • What version of Excel are you using? – JvdV May 17 '19 at 17:01
  • i am using excel 2016, but still the TEXTJOIN function doesnot exist... –  May 20 '19 at 12:18

1 Answers1

2

Maybe in D1:

=IF(COUNTIF($C$1:$C$4,B1),B1&": "&TEXTJOIN(",",TRUE,IF($B$1:$B$4=B1,$A$1:$A$4,"")),"")

Enter through CtrlShiftEnter

Drag down...

Note that this would work in Excel2016 or later with TEXTJOIN() enabled.

P.S. I don't have TEXTJOIN() myself and can't test if this works for you.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    Just tested it and it looks like it works with the sample data. – cybernetic.nomad May 17 '19 at 17:14
  • 1
    Suggestion: Return null string instead of `FALSE` for the non-matches – Ron Rosenfeld May 17 '19 at 20:32
  • @JvdV i copy pasted the formula, in D1 cell, then pressed Ctrl+Shift+Enter. Then the cell is red, and nothing gets calculated, since 0ffice 365 reports that in D1 cell, there is an error –  May 20 '19 at 14:11
  • You probably also don't have TEXT JOIN enabled. See if this applies to you: https://support.office.com/en-us/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c – JvdV May 20 '19 at 15:26
  • i have tested with office 365, and i find the TEXTJOIN function available for use –  May 21 '19 at 09:02
  • @openref, I unfortunately can't test. I see there was a missing `,` so added that. Does it work now for you? – JvdV May 21 '19 at 11:28
  • @openref, if this has answered your question, you should mark it as answered, refer to this [link](https://stackoverflow.com/help/someone-answers) for more information. – JvdV May 28 '19 at 07:04