Retrieve all combinations from 2 columns of data

[DK4700]

Basically I need all combinations possible from 2 columns of data (sales person and items)

I receive information in 2 sheets like this:

* List of Sales person (column A)

Joe​

Isa​

.​

.​

​

* List of items (column a) and margin (column G)

item 100 - margin 100​

item 200 - margin 200​

.​

.​

Now I need to combine this information so that I will end up with a list like this

Joe - Item 100 - margin 100​

Joe - Item 200 - margin 200​

Joe - Item 300 - margin 300​

Isa - Item 100 - margin 100​

Isa - Item 200 - margin 200​

.​

.​

.​

.​

.​

The margin is just a "add on information" to the item and are not to be combined individually. The number of sales persons as well as the number of items can vary,

Until now we have copy/pasted the item list X times and then added the sales persons manually. I can't seem to solve it via formulas and I'm not good creating VBA from scratch. Hopefully somone can assist me to do this smarter ?


BR
Susan - DK4700
Windows / Excel 2016.

 

[mohadin]

Hi
And welcome

Sub testr()
    Dim a, b, k
    Dim i, ii, c
    c = 1
    a = Sheets("Sheet1").Cells(1, 1).Resize(Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row)
    b = Sheets("Sheet2").Cells(1, 1).Resize(Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row)
    ReDim k(1 To UBound(a) * UBound(b))
    For i = 1 To UBound(a)
        For ii = 1 To UBound(b)
            k(c) = a(i, 1) & " - " & b(ii, 1)
            c = c + 1
        Next
    Next
    Sheets("sheet2").Cells(1, 3).Resize(UBound(k)) = Application.Transpose(k)
End Sub

 

[DK4700]

Sub testr() Dim a, b, k Dim i, ii, c c = 1 a = Sheets("Sheet1").Cells(1, 1).Resize(Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row) b = Sheets("Sheet2").Cells(1, 1).Resize(Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row) ReDim k(1 To UBound(a) * UBound(b)) For i = 1 To UBound(a) For ii = 1 To UBound(b) k(c) = a(i, 1) & " - " & b(ii, 1) c = c + 1 Next Next Sheets("sheet2").Cells(1, 3).Resize(UBound(k)) = Application.Transpose(k) End Sub

Thanks, I wil try to fit it into my data tomorrow. At first sight your VBA code seems do the trick.
I will get back in a few days time.

 

[DK4700]

I still struggle on defining the exact data areas - eg the header is to be excluded and blank lines in the bottom as well. But it is workable. From here I will use TFLO (Try, Fix, Learn - and over again)

 

[mohadin]

Hi Susan
Thank you for the feedback
then
Try this

Sub testr()
    Dim a, b, k
    Dim i, ii, c
    c = 1
    a = Sheets("Sheet1").Cells(1, 1).Resize(Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row)
    b = Sheets("Sheet2").Cells(1, 1).Resize(Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row)
    ReDim k(1 To UBound(a) * UBound(b))
    For i = 2 To UBound(a)
        For ii = 2 To UBound(b)
        If a(i, 1) <> "" And b(ii, 1) <> "" Then
            k(c) = a(i, 1) & " - " & b(ii, 1)
            c = c + 1
            End If
        Next
    Next
    Sheets("sheet2").Cells(2, 3).Resize(UBound(k)) = Application.Transpose(k)
End Sub

Or

Sub testr()
    Dim a, b, k
    Dim i, ii, c
    c = 1
    a = Sheets("Sheet1").Cells(2, 1).Resize(Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row - 1)
    b = Sheets("Sheet2").Cells(2, 1).Resize(Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row - 1)
    ReDim k(1 To UBound(a) * UBound(b))
    For i = 1 To UBound(a)
        For ii = 1 To UBound(b)
        If a(i, 1) <> "" And b(ii, 1) <> "" Then
            k(c) = a(i, 1) & " - " & b(ii, 1)
            c = c + 1
            End If
        Next
    Next
    Sheets("sheet2").Cells(2, 3).Resize(UBound(k)) = Application.Transpose(k)
End Sub

 

[lrobbo314]

Another way using Power Query. Load both of your tables into PQ. On each, add an index column like this.

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_per"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 0, Int64.Type)
in
    #"Added Index"

I named my tables 'tbl_per' and 'tbl_im'.

Then I merge the queries as new like this.

let
    Source = Table.NestedJoin(tbl_per, {"Index"}, tbl_im, {"Index"}, "tbl_im", JoinKind.LeftOuter),
    #"Expanded tbl_im" = Table.ExpandTableColumn(Source, "tbl_im", {"I&M"}, {"I&M"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded tbl_im",{"Index"})
in
    #"Removed Columns"

End Reuslt.

Book1
	A	B	C	D	E	F
1	Sales Person		I&M		Sales Person	I&M
2	Joe		item 100 - margin 100		Joe	item 100 - margin 100
3	Isa		item 200 - margin 200		Joe	item 200 - margin 200
4	Mike		Item 300 - margin 300		Joe	Item 300 - margin 300
5	Eddy				Isa	item 100 - margin 100
6					Isa	item 200 - margin 200
7					Isa	Item 300 - margin 300
8					Mike	item 100 - margin 100
9					Mike	item 200 - margin 200
10					Mike	Item 300 - margin 300
11					Eddy	item 100 - margin 100
12					Eddy	item 200 - margin 200
13					Eddy	Item 300 - margin 300
Sheet1