CODEWITHSUNDEEP
excelvba
user2962502
user2962502

Reputation: 47

How to combine 2 seperate arrays to an extensive combined list in excel

I've been looking for it everywhere but I can't seem to find the answer although it seems an regular problem.

I'm trying to automate the duplication of cells in excel. I have two lists: list 1 with values 1,2,3,4 and the second list is with values a,b,c,d. Now I want to have a file where for every value in list 1, the values in list two are duplicated in excel. So:

1 - a
1 - b
1 - c
1 - d
2 - a
2 - b
2 - c
2 - d
3 - a
...

I'm wondering if there's a function within excel or if not a macro that I could use to solve this? For this short list it is of course easy to do with autofill, but when the list consists of a few hundred values, it gets more complicated...

Upvotes: 3

Views: 1697

Answers (2)

Peter Albert
Peter Albert

Reputation: 17475

You can do this also with a few formulas/support columns without VBA:

Let's assume your first category is in column A, starting in A2 and your category is in column B.

  1. Determine the record count for each category, e.g. in C1: =COUNTA($A:$A)-1 (assuming a header row) and C2 equivalent
  2. Place two support column, e.g. in E and F) - E will hold the row for the first category and F the Id for the second category. Place the following formula in E2: =IF(ISTEXT(E1),1,IF(F2=1,E1+1,E1)) and this in F2: =IF(ISTEXT(F1),1,IF(F1=$C$2,1,F1+1))
  3. Add two more columns for the final result - G for category one with formula =INDEX(A:A,E2+1) and H for category 2 with the formula =INDEX(B:B,F2+1).
  4. Now simply copy the formulas for the columns E:H down for a many rows as required (number of rows required is =C1*C2

In the end it'll look something like this: enter image description here

You can download the file here.

Upvotes: 2

user2140173
user2140173

Reputation:

This should be really easy to understand ...

Open VBE ALT+F11 and right click VBA project. Insert a Module and copy-paste the below code. Hit F5 to run

Sub Main()

    Dim number As Range
    Dim letter As Range

    For Each number In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        For Each letter In Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
            With Sheet2
                .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row + 1) = number
                .Range("B" & .Range("b" & Rows.Count).End(xlUp).Row + 1) = letter
            End With
        Next
    Next

End Sub

The above code assumes that your sheet1 looks like this

enter image description here

and the results will be on Sheet2 like this

enter image description here

Upvotes: 1

Related Questions