Combination of two columns

Question

I need all combination of two columns.

First column:

+----+
|  4 |
|  8 |
+----+

Second column:

+----+
|  5 |
|  6 |
+----+

And the output should look like this:

+-----+-------+
|   4 |     5 |
|   4 |     6 |
|   8 |     5 |
|   8 |     6 |
+-----+-------+

Is it possible?

SOLUTION:

Third column:

=IF(ROW()-ROW($F$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($F$1))/COUNTA(B:B)+1)))

Fourth column:

=IF(ROW()-ROW($F$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(B:B,MOD(ROW()-ROW($F$1),COUNTA(B:B))+1))

Ok this is my real data. Can you help me with macro or function to create the output? — kevas, Nov 07 '15 at 17:47
If you want a "macro" you should tag your question as such. put in "vba" or "excel-vba" — Scott Craner, Nov 07 '15 at 17:54

agustin · Answer 1 · 2015-11-07T23:08:26.420

1

Third column:

=INDEX($A:$A,ROUNDUP(ROWS(C$1:C1)/COUNTA($B:$B),0))

Fill down until 0 appears

Fourth Column:

(Not the most elegant formula, but it does the job)

=INDIRECT("B"&IF(ROW()-COUNT(B:B)*ROUNDDOWN(ROW()/COUNTA(B:B),0)<>0,ROW()-COUNT(B:B)*ROUNDDOWN(ROW()/COUNTA(B:B),0),COUNT(B:B)))

Fill down until last row in Column C

This supposes your data is in columns A and B, there is no blank cells between values and your output will be in columns C and D.

FOR CZECH VERSION:

Third column:

=INDEX($A:$A;ROUNDUP(ŘÁDKY(C$1:C1)/POČET2($B:$B);0))

Fill down until 0 appears

Fourth Column:

=NEPŘÍMÝ.ODKAZ("B"&KDYŽ(ŘÁDEK()-POČET(B:B)*ROUNDDOWN(ŘÁDEK()/POČET2(B:B);0)<>0;ŘÁDEK()-POČET(B:B)*ROUNDDOWN(ŘÁDEK()/POČET2(B:B);0);POČET(B:B)))

Fill down until last row in Column C

You should expect all answers in Stackoverflow to be given in English and with non-Europe standards. Translation courtesy of Excel Translator.

I also don't know, if the different arguments are separated by ; in Czech Version as, for example, it's in another countries in Europe. If that doesn't work, just change them with , again.

edited Nov 07 '15 at 23:08

answered Nov 07 '15 at 17:34

agustin

1,311
20
42

Thank you, but in columns C and D is not output – kevas Nov 07 '15 at 17:49
https://docs.google.com/spreadsheets/d/1kHfTDkjhgEEdKxrrQti0b4zeP3qylM6Kt3bzMGEHY2g/edit?usp=sharing – agustin Nov 07 '15 at 18:14
Aha, but in my excel It does not work... – kevas Nov 07 '15 at 18:18
Which version and language are you using? Are you in Europe? – agustin Nov 07 '15 at 18:20
Czech republic in Europe – kevas Nov 07 '15 at 18:23
on docs.google.com it works beautifully – kevas Nov 07 '15 at 18:25
Edits done to answer for Czech – agustin Nov 07 '15 at 18:31
1

Thank you very much, it works, but now I noticed that the output is not what I need. Now, there are duplicate rows. I need output that shows above. – kevas Nov 07 '15 at 18:34
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/94498/discussion-between-agustin-and-kevas). – agustin Nov 07 '15 at 18:35

Combination of two columns

1 Answers1

FOR CZECH VERSION: