0

In a spreadsheet with about 20k rows (shown in the image below), I need to find all the codes in Column A that are duplicates, then merge their values in Column G. These merged value need to go in the top row of the duplicated values. The values need to be separated by:

", Size Range - "

For example:

I need a formula or some function that will achieve this for all 20,000 rows.

JvdV
  • 70,606
  • 8
  • 39
  • 70
JHair
  • 194
  • 2
  • 11
  • Have you tried some formula yet? If so, please include that. – JvdV Jun 11 '19 at 09:27
  • @JvdV I'm afraid I don't even know where to start with this issue. This is the closest I could find to my issue, but it deletes the duplicates which I don't want - https://www.extendoffice.com/documents/excel/3309-excel-vlookup-multiple-values-concatenated.html – JHair Jun 11 '19 at 09:31

1 Answers1

2

I recently answered a somewhat similar question here

So implementing the same options on your case (with own sample data, so change ranges accordingly), it would be:

Option 1: Excel TEXTJOIN

=IF(COUNTIF($A$1:A2,A2)=1,TEXTJOIN(", ",TRUE,IF(A3:$A$13=A2,"Size range - "&C3:$C$13,"")),"")

Entered through CtrlShiftEnter and drag down.

You'll have to edit ranges accordingly.

Option 2: Google Spreadsheet TEXTJOIN

=ARRAYFORMULA(IF(COUNTIF($A$1:A2,A2)=1,TEXTJOIN(", ",TRUE,IF(A3:$A$13=A2,"Size range - "&C3:$C$13;"")),""))

enter image description here

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • This is great thank you very much, this is almost perfect. I'm testing it now, it's very close, comes up with some cells like "Size range - , Size range - S" , notice the first one. See this http://prntscr.com/o0d4rl – JHair Jun 11 '19 at 09:52
  • 1
    @JHair, that's because you haven't changed ranges ;). Do you see where it says `$A$13` and `$C$13`? You'll need to change that to your last row. – JvdV Jun 11 '19 at 09:58
  • 1
    Doh! That's got it. Thank you so much for your help. You have saved me on helluva headache! – JHair Jun 11 '19 at 10:06