2

I have a Google Spreadsheets with three columns.

I need to populate the Column C with all the possible combinations of the values in Columns A and B. Please take a look a the capture to see what I mean.

I found this following formula, but it output result not same.but i need same as image.

i am using this code:

=ArrayFormula(transpose(split(rept(concatenate(A2:A&char(9)),counta(B2:B)),char(9)))
&" "&transpose(split(concatenate(rept(B2:B&char(9),counta(A2:A))),char(9))))
Md. Foysal
  • 103
  • 8

1 Answers1

1

Your goal:

  • You want to achieve the image in your question.

Modification points:

  • When your formula is used, how about the following modification?
    1. Replace transpose(split(rept(concatenate(A2:A&char(9)),counta(B2:B)),char(9))) and transpose(split(concatenate(rept(B2:B&char(9),counta(A2:A))),char(9))).
    2. Replace A and B.

Modified formula:

=ArrayFormula(transpose(split(concatenate(rept(A2:A&char(9),counta(B2:B))),char(9)))&" "&transpose(split(rept(concatenate(B2:B&char(9)),counta(A2:A)),char(9))))

Result:

In this sample, above formula is put to the cell C2.

enter image description here

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Yes it's working. Thanks. But one question if i want to combination of 3 column then what modification need to do? – Md. Foysal Apr 27 '20 at 03:38
  • @Md. Foysal Thank you for replying. I'm glad your issue was resolved. About your new question, unfortunately, from your replying, I cannot understand about the detail of your goal, and that is different from your this question. So can you post it as new question by including the detail input and output samples you expect? By this, it will help users think of the solution, and I can also think of it. If you can cooperate to resolve your issue, I'm glad. – Tanaike Apr 27 '20 at 05:45
  • Thanks for your advice. i am going to post a new question. – Md. Foysal Apr 27 '20 at 07:53
  • 1
    Hi, This is my new question. Please see this.. https://stackoverflow.com/questions/61455054/generate-all-possible-combinations-for-3-columns-in-google-sheets. – Md. Foysal Apr 27 '20 at 08:54
  • @Md. Foysal Thank you for your response. When I saw it, I noticed that an answer has already been posted. I think that it will resolve your issue. – Tanaike Apr 27 '20 at 12:17