Series mapping for all possible combinations

I have a series A,B,C and another series Cat, Dog, Fish. I want A should be mapped to all member of other series or likewise. Need help as I have a series of 500 items and another series for 300 items. so totally 150,000 matching should be done.

A Cat
B Dog
C Fish

A Dog
B Fish
C Cat
A Fish
B Cat
C Dog

Senthil

Hi, welcome to the forum

Im sorry, I dont understand what you want here

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Sorry, but I dont see how that explains things any better than the (identical) example you gave in post 1?
Walk me though what you are doing please

I used your second example, pants and shirts, to show you a way to do this relatively quickly.
1) The following formula should be indexed to your longest list, in this case shirts should be in column A:

Formula:

=INDEX(A$2:A$11,IF(MOD(ROW(1:1),COUNTA(A$2:A$11))=0,COUNTA(A$2:A$11),MOD(ROW(1:1),COUNTA(A$2:A$11))))

2) Select the cell at the bottom of the range. Since you know there will be 50 matches, this would be the cell in row 51 (taking that header into account) of the column.
3) Simultaneously press Ctrl, Shfit and the up arrow,
4) Simultaneously press Ctrl and the d key,
5) At the top the adjacent column paste the following formula indexed to pants in column B:

Formula:

=INDEX(B$2:B$6,ROUNDUP(ROW(1:1)/COUNTA(A$2:A$11),0))

6) Double click the fill handle to copy down.
Let us know if you have any questions.

Are there five styles of pants for every brand, or should the five brands of pants be used instead of Pants 1, Pants 2 etc?

Formula for the Shirt column is the same as the first formula in post #6
Formula for Pants column is:

Formula:

=INDEX(B$2:B$6,COUNTIFS(H$1:H1,C$6,H$2:H2,C$2)+1)

Formula for Pant Brand column is:

Formula:

=INDEX(C$2:C$6,IF(MOD(COUNTIFS(I$1:I1,D$6,I$2:I2,D$2)+1,COUNTA(B$2:B$6))=0,COUNTA(B$2:B$6),MOD(COUNTIFS(I$1:I1,D$6,I$2:I2,D$2)+1,COUNTA(B$2:B$6))))

Formula for Pant Color column is:

Formula:

=INDEX(D$2:D$6,IF(MOD(ROUNDUP(ROW(1:1)/COUNTA(A$2:A$11),0),COUNTA(B$2:B$6))=0,COUNTA(C$2:C$6),MOD(ROUNDUP(ROW(1:1)/COUNTA(A$2:A$11),0),COUNTA(C$2:C$6))))

Since all pants are length 40 put 40 in J2.
Goto cell F1251 and press Ctrl, Shift and the up arrow,
Press Ctrl + d,
select G2:J2 and double click the fill handle for J2.
Let us know if you have any questions.

It seems there should only be 250 combinations then? 10 shirts x 5 pant brands x 5 pant colors, is that correct?

Originally Posted by JeteMc

It seems there should only be 250 combinations then? 10 shirts x 5 pant brands x 5 pant colors, is that correct?

That's finally the conclusion I came to after I took a different approach.

In columns F:G return the combinations of Brand and Color.

Both these filled down until blank (25 combinations).

Formula:

=IF(G2="","",INDEX($C$2:$C$6,MOD(ROWS($2:2)-1,5)+1))

Formula:

=IFERROR(INDEX($D$2:$D$6,CEILING(ROWS($2:2)/5,1)),"")

Then in columns I:K fill down until blank to get 10 copies of the 25 combinations.

Formula:

=IF($K2="","",INDEX(F$2:F$26,MOD(ROWS($2:2)-1,25)+1))

Formula:

=IF($K2="","",INDEX(G$2:G$26,MOD(ROWS($2:2)-1,25)+1))

Formula:

=IFERROR(INDEX($A$2:$A$11,CEILING(ROWS($2:2)/25,1)),"")

What is pant#? If I understand correctly I believe combinations are 1250. 5 pant brands x 5 pant colors x 5 pant#(?) x 10 shirt#.

Am I missing something?

Originally Posted by sradjend

I figured a way...

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.