Create new rows by concatenating existing excel rows
I'm pretty new here, so if it's against the rules, please tell me.
I have a problem that seems pretty simple, but I wanted to check to be sure. I was trying to see if I could create a new row by concatenating each variable from one column to another, for example:
Column 1 Column 2 Combined
A 1 A1
B 2 A2
3 A3
B1
B2
B3
But instead of entering combinations manually, I wanted the combo column to do this combination without user input, and automatically update whenever column 1 or 2 has a row added or removed. I was trying to figure out if there is a way to use the concatenate function in excel or the and sign, but none of the methods work. I was thinking while trying to code this in visual terms.
Basic question: can this be done in excel? If so, what function (s) could I use?
source to share
This assumes that your data has one header row (row 1), column 1 is column "A" and column 2 is column "B". Place the formula below in a blank cell and copy it as far as your data allows.
=INDEX(A:A,INT((ROW(A2)+1)/(COUNTA(B:B)-1))+1)&INDEX(B:B,MOD(ROW(A2)-2,3)+1+1)
now if you want to add a small flag to tell you that you have more rows than you need for your data, you can add the following:
=IF(ROW(A2)-1>(COUNTA(A:A)-1)*(COUNTA(B:B)-1),"Data Exceeded",INDEX(A:A,INT((ROW(A2)+1)/(COUNTA(B:B)-1))+1)&INDEX(B:B,MOD(ROW(A2)-2,3)+1+1))
source to share
According to: https://www.extendoffice.com/documents/excel/3097-excel-list-all-possible-combinations.html
You can use this formula:
=IF(ROW()-ROW(**$D$1**)+1>COUNTA(**$A$1:$A$4**)*COUNTA(**$B$1:$B$3**),"",INDEX(**$A$1:$A$4**,INT((ROW()-ROW(**$D$1**))/COUNTA(**$B$1:$B$3**)+1))&INDEX(**$B$1:$B$3**,MOD(ROW()-ROW($D$1),COUNTA(**$B$1:$B$3**))+1))
In the above formula, $ A $ 1: $ A $ 4 , are the first column values and $ B $ 1: $ B $ 3 are the following list values you want to list the possible combinations, $ D $ 1 is the cell you put into the formula. you can change the cell references to suit your needs.
In your case, you should use:
=IF(ROW()-ROW($C$2)+1>COUNTA($A$2:$A$3)*COUNTA($B$2:$B$4),"",INDEX($A$2:$A$3,INT((ROW()-ROW($C$2))/COUNTA($B$2:$B$4)+1))&INDEX($B$2:$B$4,MOD(ROW()-ROW($C$2),COUNTA($B$2:$B$4))+1))
source to share