LinkBack URL
About LinkBacks
Hi all,
i need a help to make some combinations. Are there any way to excel make automatically all combinations possibles into a range ?
For example, all possible combinations between columns test 1 and test 2.
Tks.
Hi all,
i need a help to make some combinations. Are there any way to excel make automatically all combinations possibles into a range ?
For example, all possible combinations between columns test 1 and test 2.
Tks.
Last edited by vidaLL; 10-25-2013 at 12:06 PM.
copy paste below in D4 then hold control and shift together and hit enter to make it as array formula once you enter formula as array you will find curly braces surrounding your formula {} which cannot be entered manually
=INDEX($A$2:$A$8,ROW($A1))&INDEX(TRANSPOSE($B$2:$B$8),,COLUMN(A$1))
drag down and left to right
Last edited by hemesh; 10-25-2013 at 01:27 PM.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
If you want to see the combinations in one column, then put this in C2:
=INDEX(A:A,INT((ROWS($1:1)-1)/7)+2)&INDEX(B:B,MOD(ROWS($1:1)-1,7)+2)
then copy down as far as you need to.
Hope this helps.
Pete
find attached
To reverse the order use
=INDEX(TRANSPOSE($A$2:$A$8),ROW(A1))&INDEX(TRANSPOSE($B$2:$B$8),,COLUMN(A1)) hold control and shift together and hit enter to make it array formula.
only pressing control and enter in array formula will lead to error
find the attached
Guys, thanks very much.
All solutions worked perfectly.
Tks very much again @hemesh and @Pete_UK
You are welcome Vidal
One way to get all combinations in the configuration that you gave is to create a matrix:
Enter the letters from A to G down column A from A2 to A8.
Enter the letters in row 1 from B1 to H1
Select the range B2:H8 and enter this formula AND enter with Ctrl +Shift + Enter
Formula:
Please Login or Register to view this content.
The selected range will be filled with all possible combinations.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
write at D2
=IFERROR(INDEX($A$2:$A$8,COLUMN(A1))&INDEX($B$2:$B$8,ROW(1:1)),"")
copy right and down
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks