SOLVED

Best way to populate 1 cell with a summary of several previous cells in the same row?

%3CLINGO-SUB%20id%3D%22lingo-sub-1568434%22%20slang%3D%22en-US%22%3EBest%20way%20to%20populate%201%20cell%20with%20a%20summary%20of%20several%20previous%20cells%20in%20the%20same%20row%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1568434%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20data%20in%20some%20columns%20but%20not%20others%2C%20%5Bdata%20from%20bagel%20shop%20customers%20selecting%20their%20cream%20cheese%20options%20for%20orders%5D.%20What%20is%20the%20best%20way%20to%20populate%201%20cell%20at%20the%20end%20of%20the%20row%20to%20summarize%20everything%20the%20user%20entered%20in%20the%20entire%20row%2C%20including%20the%20item%20name%20for%20each%20entry%20%5Bwhich%20is%20in%20the%20row%20above%5D%2C%20but%20also%20skip%2Fignore%20any%20blank%20entries%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20B%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20C%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20D%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BF%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20G%3C%2FP%3E%3CTABLE%20width%3D%22692%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2253%22%3E%3CP%3ECup%20of%20Plain%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2266%22%3E%3CP%3EPint%20of%20Plain%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2278%22%3E%3CP%3ECup%20of%20Cinnamon%20Sugar%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22126%22%3E%3CP%3EPint%20of%20Cinnamon%20Sugar%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2278%22%3E%3CP%3ECup%20of%20Garlic%20Cream%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2260%22%3E%3CP%3EPint%20of%20Garlic%20Cream%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22230%22%3E%3CP%3ESummary%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2253%22%3E%3CP%3E1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2266%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2278%22%3E%3CP%3E2%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22126%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2278%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2260%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22230%22%3E%3CP%3E1%20Cup%20of%20Plain%2C%202%20Cup%20of%20Cinnamon%20Sugar%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20using%20Google%20Sheets%20and%20had%20a%20formula%20that%20worked%20there%2C%20but%20I%20can%E2%80%99t%20replicate%20in%20Excel.%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3ELindsey%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%5BMy%20google%20sheets%20question%20that%20resulted%20in%20the%20formula%20I%20was%20using%20are%20here%3A%20%3CA%20href%3D%22https%3A%2F%2Fsupport.google.com%2Fdocs%2Fthread%2F40080675%3Fhl%3Den%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.google.com%2Fdocs%2Fthread%2F40080675%3Fhl%3Den%3C%2FA%3E%3C%2FP%3E%3CP%3EIdea%20of%20the%20formula%3A%3C%2FP%3E%3CP%3E%3DTEXTJOIN(%22%2C%20%22%2C%20TRUE%2C%20ARRAYFORMULA(IF(LEN(E2%3AM2)%2CCONCAT(E2%3AM2%2CCONCAT(%22%20%22%2C%20%24E%241%3A%24M%241))%2C%20)))%5D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1568434%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1568869%22%20slang%3D%22en-US%22%3ERe%3A%20Best%20way%20to%20populate%201%20cell%20with%20a%20summary%20of%20several%20previous%20cells%20in%20the%20same%20row%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1568869%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750523%22%20target%3D%22_blank%22%3E%40gindseylaudet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20an%20array%20formula%20confirmed%20with%20Command%2BReturn%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DTEXTJOIN(%22%2C%20%22%2CTRUE%2CIF(A2%3AF2%26gt%3B0%2CA2%3AF2%26amp%3B%22%20%22%26amp%3BA%241%3AF%241%2C%22%22))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdjust%20the%20ranges%20if%20needed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1569251%22%20slang%3D%22en-US%22%3ERe%3A%20Best%20way%20to%20populate%201%20cell%20with%20a%20summary%20of%20several%20previous%20cells%20in%20the%20same%20row%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1569251%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20is%20perfect%20-%20many%20thanks!!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello,

I have data in some columns but not others, [data from bagel shop customers selecting their cream cheese options for orders]. What is the best way to populate 1 cell at the end of the row to summarize everything the user entered in the entire row, including the item name for each entry [which is in the row above], but also skip/ignore any blank entries?

 

      A          B              C                      D                    E               F          G

Cup of Plain

Pint of Plain

Cup of Cinnamon Sugar

Pint of Cinnamon Sugar

Cup of Garlic Cream

Pint of Garlic Cream

Summary

1

 

2

   

1 Cup of Plain, 2 Cup of Cinnamon Sugar

 

I was using Google Sheets and had a formula that worked there, but I can’t replicate in Excel.

Thanks!

Lindsey

 

 

[My google sheets question that resulted in the formula I was using are here: https://support.google.com/docs/thread/40080675?hl=en

Idea of the formula:

=TEXTJOIN(", ", TRUE, ARRAYFORMULA(IF(LEN(E2:M2),CONCAT(E2:M2,CONCAT(" ", $E$1:$M$1)), )))]

2 Replies
Highlighted
Best Response confirmed by gindseylaudet (New Contributor)
Solution

@gindseylaudet 

As an array formula confirmed with Command+Return:

 

=TEXTJOIN(", ",TRUE,IF(A2:F2>0,A2:F2&" "&A$1:F$1,""))

 

Adjust the ranges if needed.

Highlighted