Excel formula to show comma separate result

Question

I need a formula to write back a sequence of numbers in a comma separated value in a single cell. In the example below the formula would look at column A and find the reference 1, then search columns B to F and store the value. Then it would need to look back at column A and if it found the reference of 1 again and search column B to F again and add that value to the previous answer - all in one cell. Any ideas!?

score 2 · Answer 1 · answered Nov 15 '19 at 20:38

2

You can use TEXTJOIN as an array formula, Put this in G2:

=TEXTJOIN(",",TRUE,IF(($A$2:$A$12=A2)*($B$2:$F$12<>""),$B$2:$F$12,""))

Confirm with Ctrl-Shift-Enter when exiting edit mode. then copy down.

TEXTJOIN was introduced in Office 365 and Office 19, if one does not have TEXTJOIN there is code Here that mimics the TEXTJOIN

answered Nov 15 '19 at 20:38

Scott Craner

148,073
10
49
81

You are on a roll with these formula questions :) – BigBen Nov 15 '19 at 20:43
1

@BigBen a bunch of softball questions. i should have found the duplicates and closed them, but that would take longer. – Scott Craner Nov 15 '19 at 20:44
Hi, any idea why I am getting this error@ https://imgur.com/cjgpha0 – user114790 Nov 18 '19 at 09:18
@user114790 see last paragraph. You do not have TEXTJOIN as a native formula. You will need to put the code that is linked in a module then you can use the formula. – Scott Craner Nov 18 '19 at 14:41

Excel formula to show comma separate result

1 Answers1