Creating Combinations from Two Lists

I have data in two lists on two different worksheets. The data is laid out as:

Worksheet A
A
B
C

Worksheeet B
X
Y
Z

I need to define the relationship between each element in a vertical 
setting, In the past I have used cut and paste but am hoping to write a macro 
that would create the relationships for me. For example:

Col A         Col B        Col C
A                                X
A                                Y
A                                Z
X                                A
Y                                A
Z                                A

Does anyone have any thoughts if this is feasible or not?

Thanks!      
0
jlburak (2)
12/14/2004 9:11:11 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
313 Views

Similar Articles

[PageSpeed] 40

I think you'll have to share how you came up with the relationships in your
sample data.

I don't see anything that could help me figure it out.



jlburak wrote:
> 
> I have data in two lists on two different worksheets. The data is laid out as:
> 
> Worksheet A
> A
> B
> C
> 
> Worksheeet B
> X
> Y
> Z
> 
> I need to define the relationship between each element in a vertical
> setting, In the past I have used cut and paste but am hoping to write a macro
> that would create the relationships for me. For example:
> 
> Col A         Col B        Col C
> A                                X
> A                                Y
> A                                Z
> X                                A
> Y                                A
> Z                                A
> 
> Does anyone have any thoughts if this is feasible or not?
> 
> Thanks!

-- 

Dave Peterson
0
ec357201 (5290)
12/15/2004 12:10:41 AM
Sorry relationships is probably the wrong word to use. For something specific 
to my business, I need to submit a request to a team that basically says what 
product one person can transfer to from another. At the end of the day I need 
to supply to the team that updates a table an excel file in the format

Column A                 Column B                 Column C
Orig Product              Allow/Don't Allow     Desitnation Product

Column B must be manual, but I was hoping there might be a way to 
pre-populate the template with A and C so I didn't have to copy and paste so 
many times.

Thanks for your help.



"Dave Peterson" wrote:

> I think you'll have to share how you came up with the relationships in your
> sample data.
> 
> I don't see anything that could help me figure it out.
> 
> 
> 
> jlburak wrote:
> > 
> > I have data in two lists on two different worksheets. The data is laid out as:
> > 
> > Worksheet A
> > A
> > B
> > C
> > 
> > Worksheeet B
> > X
> > Y
> > Z
> > 
> > I need to define the relationship between each element in a vertical
> > setting, In the past I have used cut and paste but am hoping to write a macro
> > that would create the relationships for me. For example:
> > 
> > Col A         Col B        Col C
> > A                                X
> > A                                Y
> > A                                Z
> > X                                A
> > Y                                A
> > Z                                A
> > 
> > Does anyone have any thoughts if this is feasible or not?
> > 
> > Thanks!
> 
> -- 
> 
> Dave Peterson
> 
0
jlburak (2)
12/15/2004 2:31:04 PM
I'm still not sure what you want, but it kind of sounds like you want:

For every entry in column A of sheet1, you want all the entries in column A of
sheet2.

So if you had 

in sheet1
a
b
c
d

and in sheet2

1
2
3
4

you'd end up with

a  1
a  2
a  3
a  4
b  1
b  2
b  3
b  4
c  1
c  2
c  3
c  4
d  1
d  2
d  3
d  4

Is that close?

If yes:

Option Explicit
Sub testme01()

    Dim wks1 As Worksheet
    Dim wks2 As Worksheet
    Dim newWks As Worksheet
    Dim wks1Rng As Range
    Dim wks2Rng As Range
    Dim myCell As Range
    Dim destCell As Range
    Dim CountOfEntriesInWks2
    
    Set wks1 = Worksheets("sheet1")
    Set wks2 = Worksheets("sheet2")
    Set newWks = Worksheets.Add
    
    With wks1
        Set wks1Rng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
    End With
    
    With wks2
        Set wks2Rng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
        CountOfEntriesInWks2 = wks2Rng.Cells.Count
    End With
    
    Set destCell = newWks.Range("a1")
    For Each myCell In wks1Rng.Cells
        destCell.Resize(CountOfEntriesInWks2, 1).Value _
            = myCell.Value
        destCell.Offset(0, 2).Resize(CountOfEntriesInWks2, 1).Value _
            = wks2Rng.Value
        Set destCell = destCell.Offset(CountOfEntriesInWks2, 0)
    Next myCell
    
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



jlburak wrote:
> 
> Sorry relationships is probably the wrong word to use. For something specific
> to my business, I need to submit a request to a team that basically says what
> product one person can transfer to from another. At the end of the day I need
> to supply to the team that updates a table an excel file in the format
> 
> Column A                 Column B                 Column C
> Orig Product              Allow/Don't Allow     Desitnation Product
> 
> Column B must be manual, but I was hoping there might be a way to
> pre-populate the template with A and C so I didn't have to copy and paste so
> many times.
> 
> Thanks for your help.
> 
> "Dave Peterson" wrote:
> 
> > I think you'll have to share how you came up with the relationships in your
> > sample data.
> >
> > I don't see anything that could help me figure it out.
> >
> >
> >
> > jlburak wrote:
> > >
> > > I have data in two lists on two different worksheets. The data is laid out as:
> > >
> > > Worksheet A
> > > A
> > > B
> > > C
> > >
> > > Worksheeet B
> > > X
> > > Y
> > > Z
> > >
> > > I need to define the relationship between each element in a vertical
> > > setting, In the past I have used cut and paste but am hoping to write a macro
> > > that would create the relationships for me. For example:
> > >
> > > Col A         Col B        Col C
> > > A                                X
> > > A                                Y
> > > A                                Z
> > > X                                A
> > > Y                                A
> > > Z                                A
> > >
> > > Does anyone have any thoughts if this is feasible or not?
> > >
> > > Thanks!
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
ec357201 (5290)
12/15/2004 11:21:04 PM
Reply:

Similar Artilces:

combining cells #4
how do i combine two cells in two rows into one cell without losing data for example A1 information here needs A2 to be combined with information here using a helper cell =A1 & A2 Copy and Edit/Paste Special/Values back into a1 Via macro, one way: http://www.mcgimpsey.com/excel/mergedata.html In article <E8557060-AE72-4C4E-9523-1F3C897B7289@microsoft.com>, "lyneday" <lyneday@discussions.microsoft.com> wrote: > how do i combine two cells in two rows into one cell without losing data > > for example > > A1 information here needs &...

Sumif with two criteria
I want to do a sumif() command if cell a=x AND b=y. Dan, =SUMPRODUCT((A1:A10="x")*(B1:B10="y")*(C1:C10)) Change the range references and criteria as needed. PC "Dan Perez" <danperez@joannstores.com> wrote in message news:040101c36c0b$2874d010$a301280a@phx.gbl... > I want to do a sumif() command if cell a=x AND b=y. Paul Corrado wrote > =SUMPRODUCT((A1:A10="x")*(B1:B10="y")*(C1:C10)) > Change the range references and criteria as needed. Very good solution. One little problem I have found with this in the past. Suppose...

How to program dependent list in Excel using VBA?
I have several columns in a spreadsheet, each has a lis of values. Column B's values depend on the values of Column A -- once a user selects a value in the list of column A, column B will populate a list crresponding to the selected value in Column A. There are about 1000 rows in the sheet and for each row, a user has to first select from Column A list and then Column B list. I have implemented this funtionality using the method describing here: http://www.ozgrid.com/News/excel-dependent-lists.htm Now my boss wants me to do it using macro/VBA. Because the lists in each cell that I created...

send to distribution list without showing list contents?
How can I send an email to members of a distribution list without every recipent seeing everybody else's e-mail address? Thanks, Paul. Yes, if you use the BCC: field, everyone will get the mail, but they won't see any address but your own. >-----Original Message----- >How can I send an email to members of a distribution list without every >recipent seeing everybody else's e-mail address? > >Thanks, > >Paul. > > >. > Yes, I had tried that, but it's kind of funky that they see the email addressed to ME. Any other suggestions out ther...

Conditional pick list
We are trying to use 2 fields in which to drill down data from our advertising. For example, if the source feild (1) is internet, then source feild (2) field would provide a pick list of just that information from the first field. Any ideas? -- Allied Fun With CRM Ahh, wait til v3.0? Seriously, this will be a LOT easier in v3.0. You could get this to work in v1.2, but it takes a lot of effort and is not supported. You basically need to manipulate the DHTML DOM to change the values of the picklist via javascript. -- Matt Parks MVP - Microsoft CRM "Allied Modular" <Al...

Turn off Outlook creating a new version when an E-mail is edited
In my Outlook 2003, whenever I edit and save an E-mail, say to remove a large attachment after copying it to my hard drive, it creates a new version of the E-mail instead of changing the original. How can I turn this of? I think I'm seeing the same issue...I keep my deleted items folder empty on a pretty regular basis but I continually find "ghost" email records and other items that don't have anything on them. Is this what you are experiencing as well? (odd about the screen names ) {:0) "Craigster" wrote: > In my Outlook 2003, whenever I edit ...

algorithm for combinations & permutations
Does anyone have a simple, elegant algorithm to list all the possible combinations of n integers, taken one at a time? For example, suppose we wish to assign the integers 1, 2, 3, 4, 5, 6, & 7 to the variables A, B, C, D, E, F, & G in every possible way it can be done, with no repeated combinations. There are 5,040 ways to do it. ( 7! ). Is there an easy way to code it? Thanks for the help. Bill McKeever Yes, use a recursive algorithm. "Bill McKeever" <willymac@alaskalife.net> wrote in message news:001f01c36f68$e7b84140$a001280a@phx.gbl... > Does anyon...

List View question
Short and crisp question: How do you reference to listitems in multi selections in List Views? any hints are most appreciated... regards, Jos --------------= Posted using GrabIt =---------------- ------= Binary Usenet downloading made easy =--------- -= Get GrabIt for free from http://www.shemes.com/ =- If you want the items selected, call something like this on each lstbox click event: Public Sub Add() Dim frm As Form, ctl As listbox Dim varItm As Variant Set frm = Forms!YourformName Set ctl = frm!YourlstboxName ' loops thru lstbox to get total selected ...

Multiple domains in Query-based Distribution List
Hi Everyone I would like to find out if/how it is possible to setup a query-based DL with the following: Include: All users with email address @domain1.com **OR** all users @domain2.com I know you can add multiple domains/email addresses but what it effectivley does is look for All users with email address @domain1.com **AND** all users @domain2.com i.e The object has to satisfy both the criteria. I need a DL that picks up the users if they satisfy either/or. Your assistance is greatly appreciated Regards Here's a response I gave to a similar issue that was posted a few days ago ...

Access(2003) - DataSheet
How do I select a record in either a DataSheet view OR on a Subform and then "GoTo" OR open the Formview of the selected record? It Would be great IF: -> right click (on record selector ie. 1st column) (Dropdown Menu appears - Choices with: -> "GoTo form view"? (of this ROW I right clicked on) A List (Subform OR DataSheet) to pick where you wanted to go or view would be great! but how? I added a button to "OPEN FORM" and added criteria for selecting, but I got a FILTERED view of ALL the records (from view) great! BUT I wanted the one I choose ...

Clear Autofill List
Hello, How do I delete individual values or the entire autofill list? Thanks for help. Robert Robert Not sure what you're asking here. Can you copy and paste a sample of your data and what you want done with it. Autofill list is what? A list that you have entered into Tools>Options>Custom Lists? If so, just select in the dialog box and delete it. Gord Dibben MS Excel MVP On Mon, 11 Dec 2006 18:51:20 -0500, "Robert Pickett" <pickett@transcounsel.com> wrote: >Hello, > >How do I delete individual values or the entire autofill list? Thanks for &...

Creating a Database with Macros
I have a folder of files each of which are an excel macro form and ar serialized (serial number is the file name as well). I want to tak all of these files and 5-10 cells of information from them and compil them into a database. I think I need to use macros but am unfamilia with how to go about this. Any help would be greatly appreciated. Thanks in advance, d -- Message posted from http://www.ExcelForum.com ...

Spam and Black Lists
Ok, I understand what a Black List is but somehow my server has been put on one. I am in the process of trying to determine which list I am on but have not had much luck. What I want to find out is if there is any central point for checking a blacklist. I googled Blacklist and I get too many hits to try them all. I am currently working at finding out from the organizations we are sending mail to in order to find out which lists they use but thought I would ask here. Also, In the NDR I noticed that it is listing the Servername.DomainName.Local name instead of the mail.EmailDomainNam...

How do I quickly move up or down list
I have a list with 4000+ items in alphabetical order. Is there an easy way to move up or down to another beginning letter other than scrolling? Say I am in the Z's & want to go up to the B's, then back down to the W's, etc... Any help would be appreciated. Thanks, Carol Have you tried using <Ctrl><f>? "Carol" wrote: > I have a list with 4000+ items in alphabetical order. > Is there an easy way to move up or down to another beginning letter other > than scrolling? Say I am in the Z's & want to go up to the B's, then back &...

how can i create a leaflet in publisher
How do i crete a leaflet that folds, two folds, three pages, six counting the backs as well. and how do i change what page i look at. In Publisher 2000 it's; File New The Wizard comes up, select Brochures. -- Don Vancouver, USA "C" <C@discussions.microsoft.com> wrote in message news:1AA87D44-A572-4CA3-AB9D-79D46A699EFD@microsoft.com... > How do i crete a leaflet that folds, two folds, three pages, six counting > the > backs as well. > and how do i change what page i look at. > ...

How does one create a custom Menu on the Menu Bar in Word 7?
It was quite simple in Word 2003 via Tools Customize. There is no Menu Bar in Word 2007, only the Ribbon, and the only customization permitted is of the QAT. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "kaoinla" <kaoinla@discussions.microsoft.com> wrote in message news:B9590384-4E4F-48BB-9764-4B360724D3AB@microsoft.com... > It was quite simple in Word 2003 via Tools Customize. > That's a bit sweeping? - http://gregmaxey.mvps.org/Customize_Ribbon.htm and http://gregmaxey.mvps.org/Cus...

Drop down list display options.
I have a subform that uses a dropdown list to select up to six different items. One (or more) of these items is already selected for the given record. Is there a way to remove the previously selected item(s) from the available list so no duplicates can be entered? Example: Drop Down List: Mining Construction Industrial Powergen Defense Marine If Mining is selected and another item will be selected as well, Mining will no longer be listed as one of the available items to select within the drop down list. Is this at all possible??? Thank you for taking the time and effort to assist ...

distribution lists
I have set up several distribution lists in Outlook 2003. These lists contain contacts from the main contacts folder. When I update a contact's email address, then click UPDATE in the distribution list the changes are reflected perfectly. However, if I delete a contact from the main contacts folder then click update in the distribution list, the contact is not removed from the list even though the no longer exist in the contacts folder. Does anyone know of a way to correct this without deleting the contacts manually? Best regards, Alexis. ...

Drop Down Lists in Organize Pane
In the Organize Pane, in Outlook 2000, when selecting the folder to move a message into, or create a rule (Using Folders on the left side of the Organize Pane), the drop- down lists show a bunch of folders, many of which have been deleted. How can we remove (or modify) items from the list that appears in that drop-down list? Is it just a regedit hack? Thanks, Darrel. ...

Show two value ranges on one axis
How do you show two value ranges of the same data on one axis? (the axis range is separated by a zigzag/heartbeat style line). e.g. instead of showing 0-50, the axis could show 10-25 and 40-45. Any help appreciated! Take a look at Jon Peltier's Broken Y axis example. http://www.peltiertech.com/Excel/Charts/BrokenYAxis.html "NOKIA" <NOKIA@discussions.microsoft.com> wrote in message news:2564B7F7-0693-4A7E-A5C8-9D3180B4EA6B@microsoft.com... > How do you show two value ranges of the same data on one axis? (the axis > range is separated by a zigzag/heartbeat style l...

Newly created user and mailbox!
When I create a new user and also create a new email address for that user, the email address is never generated, and I can't open the mailbox logging in as that user, even though the exchange task allows me to move or delete the mailbox. Can anyone help me. Ultimately, I can not create any new mailboxes that work. On Fri, 8 Oct 2004 07:45:25 -0700, "Dewayne Martin" <dmartin@metalpowderproducts.com> wrote: >When I create a new user and also create a new email >address for that user, the email address is never >generated, and I can't open the mailbox ...

Purchase Orders
Does anybody know of any RMS Add-On where you can Create a Purchase Order by importing an item list from a CSV text file? We have a customer that gets an excel spreadsheet from their supplier of all the items that they should be receiving via UPS. The customer doesnt know before hand what they will be receiving until they get the excel spreadsheet. Any Ideas? I'm new to RMS, so forgive me if this info is slightly inaccurate, try it out first. If you create a blank P.O for adding item manually, on the screen where you enter your items, right click, and import. Then you choose you...

create dynamic range for chart
Hello, I want to export data from Excel, perform some calculations on it, and then link the data to an Excel chart. Although the exported data has a named range with it, the area where I've done calculations doesn't have a dynamic named range. It's the calculated area that I want my chart to reference. How can the chart reference only the dimensions of the calculated area that correspond to the variable dimensions of the exported data? Hi You create yourself dynamic ranges (Insert.Name.Define) for chart data series - like any dynamic ranges. Just remember for chart you must ...

Why does Excel automatically create a temporary Word archive file.
Each time I 'save as' MS Excel saves an additional file in my folder which has archive properties and also tags it as temporary. I would like to eliminate this additional file from the saving process. How is this done? That is by design and cannot be turned off -- Regards, Peo Sjoblom "Perplexed" <Perplexed@discussions.microsoft.com> wrote in message news:BB08DB1A-BF6B-48AE-81E4-6B0383D36455@microsoft.com... > Each time I 'save as' MS Excel saves an additional file in my folder which > has archive properties and also tags it as temporary. I would ...

Distribution List Question #4
I'm running Win XP and Outlook 2000 and am taking over emailing a weekly letter to people on a distribution list. The distribution list that I was given works fine in Outlook and I'm now trying to copy each Name/email pair to a separate listing in my address book. I can see how to add new names to the distribution list from the address book but I can't see how to copy names in the distribution list to the address book. \ Is there a way to do this? Bob L <nospam@nospam.com> wrote: > I'm running Win XP and Outlook 2000 and am taking over emailing a > wee...