Cross product of text fields
Results 1 to 3 of 3

Thread: Cross product of text fields
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2006
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Cross product of text fields

    I have 2 columns of data, each with variable length:
    C1 C2
    a x
    b y
    c z
    d

    I need to create a cross-product of these text fields that would provide the following:
    ax
    ay
    az
    bx
    by
    bz
    cx
    cy
    cz
    dx
    dy
    dz

    Is there a formula in Excel or a VBA macro that could produce this?
    Thanks.
    MD

  2. #2
    Legend VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Cross product of text fields

    Try

    Code:
    Sub Xprod()
    Dim LRA As Long, LRB As Long, i As Long, j As Long, k As Long
    LRA = Range("A" & Rows.Count).End(xlUp).Row
    LRB = Range("B" & Rows.Count).End(xlUp).Row
    For i = 1 To LRA
        For j = 1 To LRB
            k = k + 1
            Range("C" & k).Value = Range("A" & i).Value & Range("B" & j).Value
        Next j
    Next i
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    11,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Cross product of text fields

    Suppose your sample data are in B2:C5.

    Then, in some cell, say D2, enter the formula
    =INDEX($B$2:$B$5,INT((ROW()-ROW($D$2))/ROWS($C$2:$C$4))+1)&INDEX($C$2:$C$4,MOD(ROW()-ROW($D$2),ROWS($C$2:$C$4))+1)

    Copy D2 down until you get an error.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •