• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combine 2 lists in all combinations

O

Oxidised

Member
Hi,
I have two lists, and I am trying to combine them so that each item of the first is matched with each of the second... so if I have on sheet1 (inputs):
Col A: Col B:
A X
B Y
C Z

I want the output to be on sheet2 (calcs):
Col A: Col B:
A X
A Y
A Z
B X
B Y
B Z
C X
C Y
C Z

I want it in two columns like that, as these values are then used in lookup() for more columns to the right... and I want to be able to copy the same formula the whole way down, not have to edit it each time Col A changes... and no VBA please!!
Thanks
Oxi
 
Thanks for the link, I did try searching and also google, but getting answers about comparing lists etc... obviously using the wrong search terms! I will try to use the examples to build an =index() statement.
 
Hi, Oxidised!
There should be only one INDEX formula that works for any number of lists, in my example file is this:
O2:S2 : =INDICE(A:A;RESIDUO(ENTERO((FILA()-1+G$3-1)/G$3);G$2)+(1-SIGNO(RESIDUO(ENTERO((FILA()-1+G$3-1)/G$3);G$2)))*G$2+1;1) -----> in english: =INDEX(A:A,MOD(INT((ROW()-1+G$3-1)/G$3),G$2)+(1-SIGN(MOD(INT((ROW()-1+G$3-1)/G$3),G$2)))*G$2+1,1)
It uses helper cells (2) for each list, in columns G:K.
Regards!
 
SirJB7, yeah, I saw that in your files, thanks for that. The basic premise (without helper cells) is
=index(list_array, (current_row_new_list -1 / total_row_of_base_list) +1). Using helper cells allows you to have the same formula for all columns of the new list as you show.
I managed to do it without helper cells, but the formula for the last column uses a mod() function instead of an int() function. I know it could be prettier with helper cells and 1 formula, or named ranges... but it works, so all good ;)

Anyway, my two formulas are with reference to OP example i.e. input on sheet 1, output on sheet 2:
ColA = index(sheet1!$A$1:$A$3,int((rows(sheet2!$A$1:$A3)-1)/rows(sheet1!$B$1:$B$3))+1)
ColB = index(sheet1!$B$1:$B$3,mod((rows(sheet2!$B$1:$B3)-1,rows(sheet1!$B$1:$B$3))+1)

Thanks again.
 
Hi, Oxidised!
For a fixed number of columns (more indeed for the trivial case of 2 lists) it could be done without any helper columns as you correctly stated and wrote formulas for.
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
You must log in or register to reply here.
Back
Top