Mix 2 columns on Google spreadsheet

user_13097808 0
Viewed 22

I need to be able to combine columns A and B

In column A are all the dates of the year

In column B are the names of some countries

What I need is a formula that allows me to combine each country for 365 days a year (column B)

For example.

F        G

Thailand 01/01/2020

Thailand 02/01/2020

Thailand 03/01/2020

Thailand 04/01/2020

Thailand 05/01/2020

Thailand 06/01/2020

Thailand 07/01/2020

Thailand 08/01/2020

.
.
.
etc

Can somebody help me? I leave you an editable spreadsheet https://docs.google.com/spreadsheets/d/1EhdOxomWYg47Ek3GO-C7jVNe0blHcBDzxPlSsrNf-VE/edit?usp=sharing

array-formulas google-sheets-formula transpose google-sheets arrays
1 Answers

example for 500 row combo:

=ARRAYFORMULA(IFERROR(SORT({
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A2:A50;A2:A50<>"")&CHAR(9));COUNTA(B2:B50));CHAR(9)))\
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B2:B50;B2:B50<>"")&CHAR(9);COUNTA(A2:A50)));CHAR(9)));
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A51:A100;A51:A100<>"")&CHAR(9));COUNTA(B51:B100));CHAR(9)))\
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B51:B100;B51:B100<>"")&CHAR(9);COUNTA(A51:A100)));CHAR(9)));
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A101:A150;A101:A150<>"")&CHAR(9));COUNTA(B101:B150));CHAR(9)))\
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B101:B150;B101:B150<>"")&CHAR(9);COUNTA(A101:A150)));CHAR(9)));
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A151:A200;A151:A200<>"")&CHAR(9));COUNTA(B151:B200));CHAR(9)))\
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B151:B200;B151:B200<>"")&CHAR(9);COUNTA(A151:A200)));CHAR(9)));
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A201:A250;A201:A250<>"")&CHAR(9));COUNTA(B201:B250));CHAR(9)))\
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B201:B250;B201:B250<>"")&CHAR(9);COUNTA(A201:A250)));CHAR(9)));
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A251:A300;A251:A300<>"")&CHAR(9));COUNTA(B251:B300));CHAR(9)))\
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B251:B300;B251:B300<>"")&CHAR(9);COUNTA(A251:A300)));CHAR(9)));
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A301:A350;A301:A350<>"")&CHAR(9));COUNTA(B301:B350));CHAR(9)))\
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B301:B350;B301:B350<>"")&CHAR(9);COUNTA(A301:A350)));CHAR(9)));
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A351:A400;A351:A400<>"")&CHAR(9));COUNTA(B351:B400));CHAR(9)))\
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B351:B400;B351:B400<>"")&CHAR(9);COUNTA(A351:A400)));CHAR(9)));
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A401:A450;A401:A450<>"")&CHAR(9));COUNTA(B401:B450));CHAR(9)))\
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B401:B450;B401:B450<>"")&CHAR(9);COUNTA(A150:A401)));CHAR(9)));
 TRANSPOSE(SPLIT(REPT(CONCATENATE(FILTER(A451:A500;A451:A500<>"")&CHAR(9));COUNTA(B451:B500));CHAR(9)))\
 TRANSPOSE(SPLIT(CONCATENATE(REPT(FILTER(B451:B500;B451:B500<>"")&CHAR(9);COUNTA(A451:A500)));CHAR(9)))}; 1; 1)))
user_56326290
Related