[Solved] Data In 2 Columns - Create a new row that has Row A and Row B in the same row

[Jherome]

Hello, it seems so simple in my head but I cannot seem to find a way to do this. I looked thru the existing threads and I cannot think of what the thread would even be called (only use Excel to automate when needed). I had a bunch of data in Cell A1 and a bunch of data in Cell A2. They had a delimeter using a | so I was successful in taking all the data in Cell A1 separated out into rows below it. Same goes for Cell B1 as well.

Now I have Column A with lets say value AA, BB
Then Column B with lets say values 11, 22

I am trying to have it automatically create separate rows that provide me with the different combinations still in different columns

So a row that has Column A and Column B as this output:

AA 11
BB 11
CC 11

AA 22
BB 22
CC 22

Note that I want it to loop automatically as there are not equal count of variables in Column A and Columb B

Thank you in advanced for the response.

 

[shamimarita]

Hello Jherome,

To create the combinations follow the steps given below:

Start by moving to a new column, Here I selected Column D, where we will generate the combinations.
Enter the following formula in the D2 cell to repeat the values from Column A:

=INDEX($A$2:$A$4,MOD(ROW()-2,COUNTA($A$2:$A$4))+1)

This formula will cycle through the values in Column A. You can drag this formula down to create a repeated list of Column A values.

Enter the following formula in the E2 cell to increment through the values from Column B:

=INDEX($B$2:$B$3,MOD(INT((ROW()-2)/COUNTA($A$2:$A$4)),COUNTA($B$2:$B$3))+1)


This formula will match each value in Column A with each value in Column B. This formula will ensure that the INDEX function always picks a valid row within B2:B3, by using the MOD function to cycle through the values properly.

Downlaod the Excel file: