Excel Formula

  • Status: Pending
  • Hadiah: $35
  • Penyertaan diterima: 15
  • Pemenang: ranashahed2000

Ringkasan Peraduan

e.g. I have 3 cells:

A1={"A","B","C"}

B1={"1","2","3"}

C1={"X","Y","Z"}

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

I need a formula to generate D1="A 1 X , B 2 Y , C 3 Z"

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)

Kemahiran Disyorkan

Penyertaan teratas dari peraduan ini

Lihat Lagi Entri

Papan Penjelasan Umum

  • imfarrukh47
    imfarrukh47
    • 1 minggu yang lalu

    Dear CH, formula #25 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
    imfarrukh47
    • 4 minggu yang lalu

    did you checck #25

    • 4 minggu yang lalu
  • douglimafanny
    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
      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
      RonaldAlexi
      • 4 minggu yang lalu

      Genial!

      • 4 minggu yang lalu
  • frlncr2
    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
    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
    imfarrukh47
    • 1 bulan yang lalu

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

    • 1 bulan yang lalu
  • gellieann3
    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
    ranashahed2000
    • 1 bulan yang lalu

    eg2 same as eg. for both solve see entry #17

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

    see the entry #17 . you will get your solution

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

    What version of Excel are you using?

    • 1 bulan yang lalu

Tunjukkan lebih banyak komen

Bagaimana mula dengan peraduan

  • Paparkan peraduan anda

    Paparkan Peraduan Anda Cepat dan mudah

  • Dapatkan berjuta penyertaan

    Dapatkan Bertan-tan Penyertaan Dari serata dunia

  • Anugerahkan penyertaan terbaik

    Anugerahkan penyertaan terbaik Muat turun fail-fail - Mudah!

Paparkan Peraduan Sekarang atau Sertai kami Hari Ini!