# Excel Formula

• Status: Pending
• Penyertaan diterima: 15
• Pemenang: ranashahed2000

e.g. I have 3 cells:

A1={&amp;quot;A&amp;quot;,&amp;quot;B&amp;quot;,&amp;quot;C&amp;quot;}

B1={&amp;quot;1&amp;quot;,&amp;quot;2&amp;quot;,&amp;quot;3&amp;quot;}

C1={&amp;quot;X&amp;quot;,&amp;quot;Y&amp;quot;,&amp;quot;Z&amp;quot;}

They are single-cell arrays no text, see: [login to view URL]

I need a formula to generate D1=&amp;quot;A 1 X , B 2 Y , C 3 Z&amp;quot;

The single-cell arrays (A1,B1,C1) are the same length but no just 3, can be N.
No VB allowed.

SEE ATTACHMENTS,

IN [login to view URL] A1, B1,C1 CELLS ARE ENTRIES, D1 IS WHERE YOUR FORMULA SHOULD BE,
In other words the data is in A1, B1, and C1, you need to do a formula which match with D1, and should works for bigger Arrays

Many answers works just for 3 element arrays, thats why now , In [login to view URL] you can test your formula in D4, (same formula should return D1 and D4 content)

Winner

## Papan Penjelasan Umum

• ###### imfarrukh47
• 1 minggu yang lalu

Dear CH, formula is working formula without any helper cell and can be expended up to as many columns as you want. if you want to test the file before awarding send me a message

• 1 minggu yang lalu
• ###### imfarrukh47
• 4 minggu yang lalu

did you checck

• 4 minggu yang lalu
• ###### douglimafanny
• 1 bulan yang lalu

My excel is in Portuguese, but follows formulas ..., you need to insert a parameter line, Column D =UNIRTEXTO(" , ";VERDADEIRO;F2:O2), Column E (NÃšM.CARACT(FÃ“RMULATEXTO(A2))-3)/4
"Qty" Column F SE(F\$1>\$E2;"";EXT.TEXTO(FÃ“RMULATEXTO(\$A2);1+4*F\$1;1)&" "&EXT.TEXTO(FÃ“RMULATEXTO(\$B2);1+4*F\$1;1)&" "&EXT.TEXTO(FÃ“RMULATEXTO(\$C2);1+4*F\$1;1)) As many fields as you need, the locks are already in place, just drag. With parameter at line one in field F1 = "1" G2 = "2": N

• 1 bulan yang lalu
1. ###### douglimafanny
• 1 bulan yang lalu

Column F:ZZ =IF(F\$3>\$E4;"";MID(FORMULATEXT(\$A4);1+4*F\$3;1)&" "&MID(FORMULATEXT(\$B4);1+4*F\$3;1)&" "&MID(FORMULATEXT(\$C4);1+4*F\$3;1))
My example was with 10 combinations

• 1 bulan yang lalu
2. ###### RonaldAlexi
• 4 minggu yang lalu

Genial!

• 4 minggu yang lalu
• ###### frlncr2
• 1 bulan yang lalu

May you please extend the date. I checked it only when about 5 minutes was left to paricipate in the contest!

• 1 bulan yang lalu
• ###### bartan29
• 1 bulan yang lalu

Hi sir, I hope you want to check entry #26 and let me know what you think.

• 1 bulan yang lalu
• ###### imfarrukh47
• 1 bulan yang lalu

hi check i uploaded my entry. can be extended as many columns as you want.
no helper cell no vba

• 1 bulan yang lalu
• ###### gellieann3
• 1 bulan yang lalu

I don't think what you'd like is possible without limiting the number of records on each cell. I suggest to approach it differently.

• 1 bulan yang lalu
• ###### ranashahed2000
• 1 bulan yang lalu

eg2 same as eg. for both solve see entry

• 1 bulan yang lalu
• ###### ranashahed2000
• 1 bulan yang lalu

see the entry. you will get your solution

• 1 bulan yang lalu
• ###### IgeS
• 1 bulan yang lalu

What version of Excel are you using?

• 1 bulan yang lalu