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
Last edited by AliGW; 07-01-2017 at 04:32 AM. Reason: Title changed for clarification.
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.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi,
I have attached a document now with a table. Please check whether you are able to understand. I need to map all the values in Series 2 to all the values of Series 1.
Series 1 Series 2
A Cat
B Dog
C Fish
Senthil
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 give one more try.
I have 5 pants and 10 shirts. I want all the possible matching/mapping.
Shirt 1 = Pant 1
Shirt 1 = Pant 2
Shirt 1 = Pant 3
and so on
Similar way all the shirts should be matched to all the pants with a possible total combination of 10(shirts)*5(Pants) = 50. how can I get the list of all the mapping in the excel when I have only the list of Shirts and Pants.
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:
Please Login or Register to view this content.
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:Please Login or Register to view this content.
6) Double click the fill handle to copy down.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
hi Jete,
Very nice! this is what I want. If you don't mind, one more question. how the formula changes the pant column has additional columns related. Please refer the attachment.
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?
it's one-one mapping Pant1=Vanheusan=Grey
Formula for the Shirt column is the same as the first formula in post #6
Formula for Pants column is:Formula:Please Login or Register to view this content.
Formula for Pant Brand column is:Formula:Please Login or Register to view this content.
Formula for Pant Color column is:Formula:Please Login or Register to view this content.
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.
hi,
I am sorry if I didn't explain correctly. I meant, if the column G is Pant 1, Column H,I,J must be Vanhuesan|Grey|40. Likewise, if column G is Pant 2, column H,I,J must be Allen Solley|Blue|40. Column H,I,J is fixed value of column G.
It seems there should only be 250 combinations then? 10 shirts x 5 pant brands x 5 pant colors, is that correct?
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?
Dave
Hi,
I figured a way. Actually I want only 50 combinations of 10 Shirts and 5 pants but in the return value Brand, color and length to accompany the Pant. I concatenated the pant attributes to Pant column and achieved the results with your first formula and later converted to columns.
Thanks so much and appreciate your help.
Senthil
That's finally the conclusion I came to after I took a different approach.Originally Posted by JeteMc
In columns F:G return the combinations of Brand and Color.
Both these filled down until blank (25 combinations).Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Then in columns I:K fill down until blank to get 10 copies of the 25 combinations.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks