Extend formula to add 1 column.

[serge]

Hello,

I need help with this formula to be extended, that I can add 1 more column and it will give me a set of 5 numbers, also can each number be separated with 1 space in between ? Thank you.

=IF(ROWS($Q$5:$Q5)>COUNTA($K$6:$K$10)*COUNTA($L$6:$L$10)*COUNTA($M$6:$M$10)*COUNTA($N$6:$N$10),"",CONCATENATE(TEXT(INDEX($K$6:$K$10,MOD(INT((ROWS($Q$5:$Q5)-1)/(COUNTA($L$6:$L$10)*COUNTA($M$6:$M$10)*COUNTA($N$6:$N$10))),COUNTA($K$6:$K$10))+1),"00"),TEXT(INDEX($L$6:$L$10,MOD(INT((ROWS($Q$5:$Q5)-1)/(COUNTA($M$6:$M$10)*COUNTA($N$6:$N$10))),COUNTA($L$6:$L$10))+1),"00"),TEXT(INDEX($M$6:$M$10,MOD(INT((ROWS($Q$5:$Q5)-1)/COUNTA($N$6:$N$10)),COUNTA($M$6:$M$10))+1),"00"),TEXT(INDEX($N$6:$N$10,MOD(ROWS($Q$5:$Q5)-1,COUNTA($N$6:$N$10))+1),"00")))

 

[Z51]

This macro may work for you instead of that long formula...

Sub serge()
Dim k As Long, l As Long, m As Long, n As Long, o As Long, q As Long
Dim lastk As Long, lastl As Long, lastm As Long, lastn As Long, lasto As Long
lastk = Range("K" & Rows.Count).End(xlUp).Row
lastl = Range("L" & Rows.Count).End(xlUp).Row
lastm = Range("M" & Rows.Count).End(xlUp).Row
lastn = Range("N" & Rows.Count).End(xlUp).Row
lasto = Range("O" & Rows.Count).End(xlUp).Row
q = 5

For k = 6 To lastk
    For l = 6 To lastl
        For m = 6 To lastm
            For n = 6 To lastn
                For o = 6 To lasto
                    Cells(q, "Q").Value = Cells(k, "K").Value & Cells(l, "L").Value & Cells(m, "M").Value & Cells(n, "N").Value & Cells(o, "O").Value
                    q = q + 1
                Next o
            Next n
        Next m
    Next l
Next k
End Sub

 

[serge]

Thank you pjoaquin, but I rather have the formula extended if possible, I'm not good with code at all.
Thank you.

 

[Fluff]

How about

=IF(ROWS($P$5:$P5)>COUNTA($K$5:$K$9)*COUNTA($L$5:$L$9)*COUNTA($M$5:$M$9)*COUNTA($N$5:$N$9)*COUNTA($O$5:$O$9),"",CONCATENATE(TEXT(INDEX($K$5:$K$9,MOD(INT((ROWS($P$5:$P5)-1)/(COUNTA($L$5:$L$9)*COUNTA($M$5:$M$9)*COUNTA($N$5:$N$9)*COUNTA($O$5:$O$9))),COUNTA($K$5:$K$9))+1),"00")," ",TEXT(INDEX($L$5:$L$9,MOD(INT((ROWS($P$5:$P5)-1)/(COUNTA($M$5:$M$9)*COUNTA($N$5:$N$9)*COUNTA($O$5:$O$9))),COUNTA($L$5:$L$9))+1),"00")," ",TEXT(INDEX($M$5:$M$9,MOD(INT((ROWS($P$5:$P5)-1)/(COUNTA($N$5:$N$9)*COUNTA($O$5:$O$9))),COUNTA($M$5:$M$9))+1),"00")," ",TEXT(INDEX($N$5:$N$9,MOD(INT((ROWS($P$5:$P5)-1)/COUNTA($O$5:$O$9)),COUNTA($N$5:$N$9))+1),"00")," ",TEXT(INDEX($O$5:$O$9,MOD(ROWS($P$5:$P5)-1,COUNTA($O$5:$O$9))+1),"00")))

 

[serge]

Thank you so much Fluff you are a Genius, it works very well you saving me so much time.
Thank you.
Serge.

 

[Fluff]

You're welcome & thanks for the feedback.