-1

I am stuck with what I though was a simple action to ask excel. I need to concatenate each and every line of a column with each of the column of a second column. This is what I would like to do

Column A Column B Result
AA XX AA XX
BB YY AA YY
CC ZZ AA ZZ
..n BB XX
BB YY
BB ZZ
CC XX
CC YY
CC ZZ
AA ..n
BB ..n
CC ..n

I tried to look into the existing questions, but maybe I don't know which keywords to use, I can't find a good code.The closest I found is this but I can't "translate" the pseudocode mentioned: I'm lost with the "iterration" mentionned

Could you please help me?

Txs very much in advance

Sandrine Pichenot
  • 3
  • 1

2 Answers2

1

This can also be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

To use Power Query

  • Select some cell in your Data Table
  • Data => Get&Transform => from Table/Range
  • When the PQ Editor opens: Home => Advanced Editor
  • Make note of the Table Name in Line 2
  • Paste the M Code below in place of what you see
  • Change the Table name in line 2 back to what was generated originally.
  • Read the comments and explore the Applied Steps to understand the algorithm

M Code
If your column names are not auto-generated, you may need to edit Column1 and Column2 to whatever the actual column names are (eg: Column1=>ColumnA)

let

//change Table name in next line to actual table name
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],

//set data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Column1", type text}, {"Column2", type text}}),

//for each entry in Column 1, create a List of all the entries in Column 2
//then remove column 2 and expand the custom column
    #"Added Custom" = Table.AddColumn(#"Changed Type", "List of all in Column2", 
            each #"Changed Type"[Column2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column2"}),
    #"Expanded List of all in Column2" = Table.ExpandListColumn(
        #"Removed Columns", "List of all in Column2"),

//remove the rows with nulls
//  (happens if one column longer than the other
    #"Filtered Rows" = Table.SelectRows(#"Expanded List of all in Column2", 
        each ([Column1] <> null) and ([List of all in Column2] <> null)),

//merge the two columns with space delimiter to get Results
    #"Merged Columns" = Table.CombineColumns(#"Filtered Rows",
        {"Column1", "List of all in Column2"},
        Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Result")
in
    #"Merged Columns"

enter image description here

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
0

If column C have only a title, below code does the work:

Sub test()
    Range("A1").Select  ' Select 1st row of column A
    Selection.End(xlDown).Select  ' Move to last row in column A
    last_col_A = ActiveCell.Row  ' Get last row number in column A
    
    Range("B1").Select  ' Select 1st row of column B
    Selection.End(xlDown).Select  ' Move to last row in column B
    last_col_B = ActiveCell.Row  ' Get last row number in column A
    
    row_C = 2 ' Start from second row of column C
    For rowA = 2 To last_col_A
        For rowB = 2 To last_col_B
            Range("C" & row_C).Value = Range("A" & rowA).Value & " " & Range("B" & rowB).Value
            row_C = row_C + 1  ' Increment row number
        Next
    Next        
End Sub
A. Mello
  • 51
  • 2
  • ... just some hints: a) use `Option Explicit` enforcing variable type declaration; b) fully qualify your range references (e.g. via sheet Code(Name) `Sheet1.Range("A1")` or tabular sheetname string like e.g. `ThisWorkbook.Worksheets("MySheetName").Range("A1")`) as the current active sheet referenced by default need not be the one in focus; consider use of a `With` - `End With` structure; d) [avoid using `Select` in VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba); e) loops in cells are time consuming compared with loops in a datafield array – T.M. Jan 21 '22 at 15:00