CODEWITHSUNDEEP
excelexcel-formula
user114790
user114790

Reputation: 21

Excel formula to show comma separate result

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!?

Image1

Upvotes: 0

Views: 349

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

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.

enter image description here

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

Upvotes: 2

Related Questions