combining two sets of rows

[juares castro]

Hi everyone!
I'd like to combine/distribute 2 sets of rows. I mean, as a result we have to see all rows of 1st set combined with all rows of 2nd.
In columns B and C are the 1st set and columns F to H the 2nd. The result, columns K to O.
Both sets can be increased. And I'd like to use functions and/or formulas.
Please, see the attachment file.

Thanks in advance.

[Pete_UK]

See attached file. I've used these formulae in the cells stated:

K2: =IF(ROWS($1:1)>COUNT(B:B)*COUNT(F:F),"",INDEX(B:B,INT((ROWS($1:1)-1)/COUNT($F:$F))+2))
L2: =IF(K2="","",INDEX(C:C,INT((ROWS($1:1)-1)/COUNT($F:$F))+2))
M2: =IF(ROWS($1:1)>COUNT(B:B)*COUNT(F:F),"",INDEX(F:F,MOD(ROWS($1:1)-1,COUNT($F:$F))+2))
N2: =IF(M2="","",INDEX(G:G,MOD(ROWS($1:1)-1,COUNT($F:$F))+2))
O2: =IF(M2="","",INDEX(H:H,MOD(ROWS($1:1)-1,COUNT($F:$F))+2))

These are then copied down further than you need them to accommodate changes in the tables (I've copied to row 25).

Hope this helps.

Pete

[juares castro]

Thanks Pete!
It's working fine.

[Pete_UK]

You're welcome - thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete

[kvsrinivasamurthy]

Try this
In Q2, then dragged to R13

=INDEX($B$2:$C$4,INT((ROW($K2)-ROW($K$2))/ROWS($F$2:$F$5))+1,COLUMN(K$2)-COLUMN($K$2)+1)

In S2, then dragged to U13

=INDEX($F$2:$H$5,MOD((ROW($K2)-ROW($K$2)),ROWS($F$2:$H$5))+1,COLUMN(S$2)-COLUMN($S$2)+1)