Combine lists

  • Thread starter Thread starter Defranco
  • Start date Start date
D

Defranco

Hi,

There is a way to make a combination of a table with a set of values
resulting in another table?

For example I have a table with 3 rows, so I combine it with 3 values
that will generate a new table with 9 rows.

For example:

Name | age
------------------------
John | 23
Mike | 29
Bobby | 26

Combined with:

Girl
--------------
Alice
Sara
Susan

Would result automatically in a final combination table like:

Name | age| Girl
--------------------------------
John | 23 | Alice
Mike | 29 | Alice
Bobby | 26 | Alice
John | 23 | Sara
Mike | 29 | Sara
Bobby | 26 | Sara
John | 23 | Susan
Mike | 29 | Susan
Bobby | 26 | Susan


Any idea in how to make it in Excel?

Thanks!

/erico
 
Assuming
Col A, Col B, , , , Col F
Name,Age,,,,Girl
one line blank
John,23, , , ,Alice
Mike,29, , , ,Sara
Bobby,26, , , ,Susan

may be this :

Sub m()
Set Rng = Range("A3").CurrentRegion
lrow = Cells(Rows.Count, "F").End(xlUp).Row
icol = Rng.Columns.Count
irow = Rng.Rows.Count

Range("A3").Select
ActiveCell.Offset(0, icol).Resize(irow, 1) = Range("F3")

i = 4
Do Until i > lrow

ActiveCell.Offset(irow, 0).Resize(irow, icol) = Rng.Value
ActiveCell.Offset(irow, icol).Resize(irow, 1) = Cells(i, "F")
ActiveCell.Offset(irow, 0).Select
i = i + 1
Loop

End Sub
 
Outstanding!

Thanks Herbert and KC

I have now two different very good solutions for my problem!

thanks!
 
Back
Top