Home

Excel formula similiar to texjoin

%3CLINGO-SUB%20id%3D%22lingo-sub-1176530%22%20slang%3D%22en-US%22%3EExcel%20formula%20similiar%20to%20texjoin%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1176530%22%20slang%3D%22en-US%22%3E%3CP%3Ehy%20all%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20if%20is%20possible.%3C%2FP%3E%3CP%3EI%20need%20formula%20to%20read%20a%20complete%20row%20and%20when%20find%20data%20combine%20it%20with%20cell%20name%20where%20data%20is%20found%2C%20and%20to%20skip%20blanks%20and%20zeroes%2C%20and%20move%20on%20to%20next%3C%2FP%3E%3CP%3Etnx%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1176530%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1176812%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20similiar%20to%20texjoin%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1176812%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F559836%22%20target%3D%22_blank%22%3E%40Carlo74%3C%2FA%3E%26nbsp%3B%20are%20you%20use%20the%20%22%26amp%3B%20%22or%20not%20because%20this%20is%20use%20as%26nbsp%3B%20jointed%20before%20that%20%26amp%3B%20also%20good%20from%26nbsp%3BConcatenate_Range%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177433%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20similiar%20to%20texjoin%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177433%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F559836%22%20target%3D%22_blank%22%3E%40Carlo74%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20didn't%20catch%20you'd%20like%20to%20join%20result%20in%20one%20string%20or%20keep%20in%20separate%20cells%20as%20in%20your%20screenshot.%20If%20the%26nbsp%3B%20latest%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20445px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171478i4B12AC623C3EEAA1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EI'd%20add%20helper%20column%20as%20here%20in%20Column%20G%20(could%20be%20in%20any%20other%20place).%20In%20A10%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(%24A%243%3A%24A%247%2CAGGREGATE(15%2C6%2C1%2F(%24G%243%3A%24G%247%26gt%3B0)*(ROW(%24A%243%3A%24A%247)-ROW(%24A%242))%2CROW()-ROW(%24A%249)))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down%20till%20empty%20cells%20appear.%3C%2FP%3E%0A%3CP%3EIn%20B10%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(%0A%20%20%20INDEX(%24B%242%3A%24F%242%2C%0A%20%20%20%20%20%20AGGREGATE(15%2C6%2C1%2F(INDEX(%24B%243%3A%24F%247%2CMATCH(%24A10%2C%24A%243%3A%24A%247%2C0)%2C0)%26gt%3B0)*%0A%20%20%20%20%20%20%20%20%20(COLUMN(INDEX(%24B%243%3A%24F%247%2CMATCH(%24A10%2C%24A%243%3A%24A%247%2C0)%2C0))-COLUMN(%24A%242))%2C%0A%20%20%20%20%20%20%20%20%20COLUMN()-COLUMN(%24A%242)%0A%20%20%20%20%20%20)%0A%20%20%20)%20%26amp%3B%20%22%20%22%20%26amp%3B%0A%20%20%20INDEX(INDEX(%24B%243%3A%24F%247%2CMATCH(%24A10%2C%24A%243%3A%24A%247%2C0)%2C0)%2C%0A%20%20%20%20%20%20AGGREGATE(15%2C6%2C1%2F(INDEX(%24B%243%3A%24F%247%2CMATCH(%24A10%2C%24A%243%3A%24A%247%2C0)%2C0)%26gt%3B0)*%0A%20%20%20%20%20%20%20%20%20(COLUMN(INDEX(%24B%243%3A%24F%247%2CMATCH(%24A10%2C%24A%243%3A%24A%247%2C0)%2C0))-COLUMN(%24A%242))%2C%0A%20%20%20%20%20%20%20%20%20COLUMN()-COLUMN(%24A%242)%0A%20%20%20%20%20%20)%0A%20%20%20)%2C%0A%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down%20and%20to%20the%20right.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177446%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20similiar%20to%20texjoin%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177446%22%20slang%3D%22en-US%22%3EThank%20you%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177575%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20similiar%20to%20texjoin%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177575%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bthank%20you%2C%3C%2FP%3E%3CP%3Edoen't%20work%20for%20me%20yet%2C%20but%20I'll%20figure%20it%20out.%3C%2FP%3E%3CP%3Ecould%20you%20just%20explain%20what%20is%20in%20formula%20last%20part%2C%20two%20empty%20rows%20for%20A2%20and%20A9%3F%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(%24A%243%3A%24A%247%2CAGGREGATE(15%2C6%2C1%2F(%24G%243%3A%24G%247%26gt%3B0)*(ROW(%24A%243%3A%24A%247)-ROW(%24A%242))%2CROW()-ROW(%24A%249)))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177588%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20similiar%20to%20texjoin%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177588%22%20slang%3D%22en-US%22%3E%3CP%3EHere%20is%20my%20original%20table%20file%20on%20last%20sheet%20%22table%22%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177607%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20similiar%20to%20texjoin%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177607%22%20slang%3D%22en-US%22%3E%3CP%3Ehere's%20my%20original%20file%20on%20second%20sheet%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Etnx%20again%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177706%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20similiar%20to%20texjoin%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177706%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F559836%22%20target%3D%22_blank%22%3E%40Carlo74%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20adjusted%20to%20your%20ranges%20formulas%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177838%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20similiar%20to%20texjoin%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177838%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%3CSTRONG%3EThank%20you%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3Ethis%20is%20great%20realy%20everything%20is%20working%20super%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177918%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20similiar%20to%20texjoin%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177918%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F559836%22%20target%3D%22_blank%22%3E%40Carlo74%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%2C%20glad%20to%20help.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20such%20result%20could%20be%20generated%20directly%20from%20your%20source%20table%2C%20but%20that's%20only%20the%20guess%2C%20I%20didn't%20check%20the%20logic.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1177934%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20similiar%20to%20texjoin%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1177934%22%20slang%3D%22en-US%22%3EYes%2C%3CBR%20%2F%3ENow%20with%20your%20formula%2C%20I%20think%20it%20could%20be%20possible%20by%20combine%20those%20formulas.%3CBR%20%2F%3EAs%20idea%20it%20was%20hard%20for%20me%20already%20to%20get%20result%20what%20I%20want.%20And%20I%20was%20looking%20too%20complicated%20solutiins.%3CBR%20%2F%3EThis%20makes%20averything%20more%20simple%20now.%3CBR%20%2F%3EThank%20you%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1178225%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20similiar%20to%20texjoin%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1178225%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F559836%22%20target%3D%22_blank%22%3E%40Carlo74%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDynamic%20array%20solution%3A%3C%2FP%3E%3CP%3E%3DIFERROR(FILTER(FILTER(%24C%243%3A%24Z%243%26amp%3B%24C%243%3A%24Z%2427%2C%24B%243%3A%24B%2427%3D%24B30)%2CLEFT(FILTER(%24C%243%3A%24Z%2427%26amp%3B%24C%243%3A%24Z%243%2C%24B%243%3A%24B%2427%3D%24B30))%26lt%3B%26gt%3B%220%22)%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1178240%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20similiar%20to%20texjoin%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1178240%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3Ethis%20solution%20also%20works%20great%2C%20but%20I%20can't%20use%20dynamic%20formulas%20because%20it%20cant%20be%20inserted%20into%20a%20table.%3C%2FP%3E%3CP%3EI%20need%20table%20because%20flow%20cannot%20rad%20data%20outside%20a%20table.%3C%2FP%3E%3CP%3Ebut%20tnx%2C%20this%20is%20great%20solution%2C%20I%20hope%20I%20will%20learn%20to%20write%20similiar%20formulas%20one%20day%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

hy all

 

I need help if is possible.

I need formula to read a complete row and when find data combine it with cell name where data is found, and to skip blanks and zeroes, and move on to next

tnx

11 Replies
Highlighted

@Carlo74  are you use the "& "or not because this is use as  jointed before that & also good from Concatenate_Range

Highlighted

@Carlo74 

I didn't catch you'd like to join result in one string or keep in separate cells as in your screenshot. If the  latest like

image.png

I'd add helper column as here in Column G (could be in any other place). In A10

=IFERROR(INDEX($A$3:$A$7,AGGREGATE(15,6,1/($G$3:$G$7>0)*(ROW($A$3:$A$7)-ROW($A$2)),ROW()-ROW($A$9))),"")

and drag it down till empty cells appear.

In B10

=IFERROR(
   INDEX($B$2:$F$2,
      AGGREGATE(15,6,1/(INDEX($B$3:$F$7,MATCH($A10,$A$3:$A$7,0),0)>0)*
         (COLUMN(INDEX($B$3:$F$7,MATCH($A10,$A$3:$A$7,0),0))-COLUMN($A$2)),
         COLUMN()-COLUMN($A$2)
      )
   ) & " " &
   INDEX(INDEX($B$3:$F$7,MATCH($A10,$A$3:$A$7,0),0),
      AGGREGATE(15,6,1/(INDEX($B$3:$F$7,MATCH($A10,$A$3:$A$7,0),0)>0)*
         (COLUMN(INDEX($B$3:$F$7,MATCH($A10,$A$3:$A$7,0),0))-COLUMN($A$2)),
         COLUMN()-COLUMN($A$2)
      )
   ),
"")

and drag it down and to the right.

Highlighted
Thank you
Highlighted

@Sergei Baklan thank you,

doen't work for me yet, but I'll figure it out.

could you just explain what is in formula last part, two empty rows for A2 and A9?

=IFERROR(INDEX($A$3:$A$7,AGGREGATE(15,6,1/($G$3:$G$7>0)*(ROW($A$3:$A$7)-ROW($A$2)),ROW()-ROW($A$9))),"")

 

Highlighted

here's my original file on second sheet @Sergei Baklan 

tnx again

Highlighted

@Carlo74 

Please check adjusted to your ranges formulas in attached file.

Highlighted

@Sergei Baklan ,

Thank you,

 

this is great realy everything is working super

 

Highlighted

@Carlo74 , you are welcome, glad to help.

 

Perhaps such result could be generated directly from your source table, but that's only the guess, I didn't check the logic.

 

Highlighted
Yes,
Now with your formula, I think it could be possible by combine those formulas.
As idea it was hard for me already to get result what I want. And I was looking too complicated solutiins.
This makes averything more simple now.
Thank you
Highlighted

@Carlo74 

Dynamic array solution:

=IFERROR(FILTER(FILTER($C$3:$Z$3&$C$3:$Z$27,$B$3:$B$27=$B30),LEFT(FILTER($C$3:$Z$27&$C$3:$Z$3,$B$3:$B$27=$B30))<>"0"),"")

Highlighted

@Patrick2788 ,

Thanks

this solution also works great, but I can't use dynamic formulas because it cant be inserted into a table.

I need table because flow cannot rad data outside a table.

but tnx, this is great solution, I hope I will learn to write similiar formulas one day

 

Related Conversations
How to filter a text column in excel to more than 2 items
fplopes in Excel on
0 Replies
Conditional formatting using tolerances
MicLima in Excel on
1 Replies
SI.CONDITION
dubkri in Excel on
0 Replies
Help with function
Vicky13 in Excel on
1 Replies
help with exchel
mat81 in Excel on
2 Replies
Excel number format - doesn't read as a number
Hilde250 in Excel on
1 Replies