Hy all
I need help with a formula I want result like textjoin but without zeros in empty cell, and to combine a column name with cell data. below table is example of results. with textjoin I get for eg. Jill = 0,0,2,0,0 i want to skip zeros and combine name from b2:F3 with cell data. Tnx
Using Excel's new Filter() function available in 365 versions.
=FILTER($B$2:$F$2,INDEX($B$3:$F$7,MATCH(A10,$A$3:$A$7,0),)>0)
&" "&FILTER(INDEX($B$3:$F$7,MATCH(A10,$A$3:$A$7,0),),INDEX($B$3:$F$7,MATCH(A10,$A$3:$A$7,0),)>0)
In the screenshot below, the formula has been entered into B10 and copied down to B12. It automatically spills across to the other columns where required.
Edit: This sample is based on your sample data, with positive numbers, hence the check is for number > 0. If you have negative numbers, you need to change the formula. If the cells can contain zeros, please let me know.
Formula for numbers above and below 0
=FILTER($B$2:$F$2,INDEX($B$3:$F$7,MATCH(A10,$A$3:$A$7,0),)<>0)&" "&FILTER(INDEX($B$3:$F$7,MATCH(A10,$A$3:$A$7,0),),INDEX($B$3:$F$7,MATCH(A10,$A$3:$A$7,0),)<>0)
