CODEWITHSUNDEEP
excelvba
okoester
okoester

Reputation: 5

Combining Excel Tables/Ranges Line by Line

I have two lists. One contains names and the other contains workshop information.

So list one looks like this:

Name 1
Name 2
Name 3
.
.
Name X

This list results from an external query and is formatted as a table. It's content changes from time to time.

The other looks like this and is simply formatted as table (without an underlying query):

Workshop 1
Workshop 2
Workshop 3
Workshop 4

I'd like to combine these two lists and the result should look like this:

Name 1 | Workshop 1
Name 1 | Workshop 2
Name 1 | Workshop 3
Name 1 | Workshop 4
Name 2 | Workshop 1
.
.
.
and so on.

So, as you can see, the total number of lines expands four times. Since the first table has already around 400 lines, copy and paste is not an option.

I have no clue to resolve this. All I've found is about JOIN tables, but therefore I need some common columns that I do not have here.

Is there any way to resolve this problem with formulas/Power Queries? Or do I have to use VBA?

Looking forward for some support,

regards Olaf

Upvotes: 0

Views: 54

Answers (3)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

A Power Query approach.

You have the two tables created on a sheet - one named NameTable the other WorkshopTable.

Create a connection only query for each table. I've named these NameTableQuery and WorkshopTableQuery: 

let
    Source = Excel.CurrentWorkbook(){[Name="NameTable"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}})
in
    #"Changed Type"

and 

let
    Source = Excel.CurrentWorkbook(){[Name="WorkshopTable"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Workshop", type text}})
in
    #"Changed Type"

Now to create a cartesian product of the two. Start with a reference to one, then add the other as a new column which you expand: 

let
    Source = WorkshopTableQuery,
    #"Add Names" = Table.AddColumn(Source, "Names", each NameTableQuery),
    #"Expanded Names" = Table.ExpandTableColumn(#"Add Names", "Names", {"Name"}, {"Name"})
in
    #"Expanded Names"

You final table will be:

| Workshop   | Name   |
|------------|--------|
| Workshop 1 | Name 1 |
| Workshop 1 | Name 2 |
| Workshop 1 | Name 3 |
| Workshop 1 | Name 4 |
| Workshop 1 | Name 5 |
| Workshop 2 | Name 1 |
| Workshop 2 | Name 2 |
| Workshop 2 | Name 3 |
| Workshop 2 | Name 4 |
| Workshop 2 | Name 5 |

Upvotes: 2

Dang D. Khanh
Dang D. Khanh

Reputation: 1471

Here is another approach, try this formula:

=OFFSET($A$1,INT((ROW()-ROW($A$1))/4),)&" | "&INDEX($B$1:$B$4,MOD(ROW()-ROW($A$1),4)+1)

enter image description here

Detailed explanation:

  • INT((ROW()-ROW($A$1))/4) will add +1 to the ordinal numbers after 4 rows
  • MOD(ROW()-ROW($A$1),4)+1 will generate ordinal numbers 1,2,3,4,1,2,3,4 ..
  • Finally combining them into offset and index gives the desired result

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37377

Try below code. I assumed that names and workshops are in column A in their respective worksheets.

Sub JoinData()
  Dim wsName As Wroksheet, wsWorkshop As Worksheet, wsResult As Worksheet
  Set wsName = Worksheets("Worksheet with names")
  Set wsWorkshop = Worksheets("Worksheet with workshops")
  Set wsResult = Worksheets("Worksheet to put results in")

  Dim lastRowName As Long, lastRowWorkshop As Long, i As Long, j As Long, k As Long
  Dim columnWithNames As Long, columnWithWorkshops
  ' here you mark, which columns hold the data
  columnWithNames = 1
  columnWithWorkshops = 1
  lastRowName = wsName.Cells(Rows.Count, columnWithNames).End(xlUp).Row
  lastRowWorkshop = wsWorkshop.Cells(Rows.Count, columnWithWorkshops).End(xlUp).Row

  k = 1
  For i = 1 To lastRowName
  For j = 1 To lastRowWorkshop
    wsResult.Cells(k, 1) = lastRowName.Cells(i, columnWithNames)
    wsResult.Cells(k, 2) = lastRowWorkshop.Cells(j, columnWithWorkshops)
    k = k + 1
  Next
  Next
End Sub

Upvotes: 0

Related Questions