Nested loops - multiple conditions

[elysium]

Hi All, seeking help for a possible VBA solution to a problem I am facing.

I will have 3 worksheets

First worksheet
Blank customer = applies to all customers
Blank product = applies to all products

Market	Customer	Product	Site	Ratio
US			New York	0.7
US			Orlando	0.3
US		999	New York	1
US	AAA	111	New York	1
US	BBB	222	Orlando	0.6
US	BBB	222	New York	0.4

Second worksheet is where all my customers are listed

Market	Customer
US	AAA
US	BBB
US	CCC
US	DDD
US	EEE

The 3rd worksheet is where the results will generate after macro runs:

I am still trying to figure out how to write this nested loop logic, any help would be much appreciated!

Market	Customer	Product	Site	Ratio
US	AAA		New York	0.7
US	AAA		Orlando	0.3
US	BBB		New York	0.7
US	BBB		Orlando	0.3
US	CCC		New York	0.7
US	CCC		Orlando	0.3
US	DDD		New York	0.7
US	DDD		Orlando	0.3
US	EEE		New York	0.7
US	EEE		Orlando	0.3
US	AAA	999	New York	1
US	BBB	999	New York	1
US	CCC	999	New York	1
US	DDD	999	New York	1
US	EEE	999	New York	1
US	AAA	111	New York	1
US	BBB	222	Orlando	0.6
US	BBB	222	New York	0.4

 

[kennypete]

The following VBA will achieve this, though the output (3rd worksheet) will not be in the same order as you have above (which I presume does not matter). The Sheet names are hard coded and not doubt this could be more efficiently achieved with arrays, but this does what you're after. Take note of the instructions in the comments to subStart().

Public iCustomer As Integer
Public iRow As Integer

Sub subStart()
' Run subStart to process.
' It requires sheets Sheet1, Sheet2 and Sheet3
' Sheet1 must have the format (with the heading row):
' Market|Customer|Product|Site|Ratio
' US|||New York|0.7
' Sheet2 must have the format (with the heading row)
' Market|customer
' US|AAA
' For both sheets the | denotes the next cell
' Sheet3 should be blank
Sheet3.Select
' Enter the headings to Sheet3
Range("A1").Value = "Market"
Range("B1").Value = "Customer"
Range("C1").Value = "Product"
Range("D1").Value = "Site"
Range("E1").Value = "Ratio"
' Now initialise and process Sheet2
iRow = 2
iCustomer = 2
Call subDoSheet2
End Sub

Sub subDoSheet2()
b = True
tempCustomer$ = ""
Do While b = True
    Sheet2.Select
    ' Get the next market/customer combination
    r$ = "A" & Trim(Str(iCustomer))
    Range(r$).Select
    market$ = ActiveCell.FormulaR1C1
    If market$ = "" Then
        ' When we get past the last market/customer we're finished
        Exit Do
    End If
    r$ = "B" & Trim(Str(iCustomer))
    Range(r$).Select
    customer$ = ActiveCell.FormulaR1C1
    ' Process Sheet1 with for market/customer
    Call subDoSheet1(market$, customer$)
Loop
End Sub

Sub subDoSheet1(marketS2$, customerS2$)
iLocal = 2
Do While iLocal > 0
    Sheet1.Select
    Range("A" & Trim(Str(iLocal))).Select
    marketS1$ = ActiveCell.FormulaR1C1
    If marketS1$ = "" Then
        iCustomer = iCustomer + 1
        Exit Do      ' end of the sheet
    End If
    Range("B" & Trim(Str(iLocal))).Select
    customerS1$ = ActiveCell.FormulaR1C1
    Range("C" & Trim(Str(iLocal))).Select
    productS1$ = ActiveCell.FormulaR1C1
    Range("D" & Trim(Str(iLocal))).Select
    siteS1$ = ActiveCell.FormulaR1C1
    Range("E" & Trim(Str(iLocal))).Select
    ratioS1$ = ActiveCell.FormulaR1C1
    Sheet3.Select
    ' When the customer is 'default' (not specified) we want that
    ' output for this customer
    If Len(customerS1$) = 0 Then
        Range("A" & Trim(Str(iRow))).Value = marketS2$
        Range("B" & Trim(Str(iRow))).Value = customerS2$
        Range("C" & Trim(Str(iRow))).Value = productS1$
        Range("D" & Trim(Str(iRow))).Value = siteS1$
        Range("E" & Trim(Str(iRow))).Value = ratioS1$
        iRow = iRow + 1
    ' When the customer matches we want that
    ' output for this customer
    ElseIf customerS1$ = customerS2$ Then
        Range("A" & Trim(Str(iRow))).Value = marketS2$
        Range("B" & Trim(Str(iRow))).Value = customerS2$
        Range("C" & Trim(Str(iRow))).Value = productS1$
        Range("D" & Trim(Str(iRow))).Value = siteS1$
        Range("E" & Trim(Str(iRow))).Value = ratioS1$
        iRow = iRow + 1
    End If ' else do nothing
    iLocal = iLocal + 1
Loop
End Sub

 

[elysium]

Hi Kennypete,

Thank you! This works but unfortunately I have easily over a few hundred customers and I gave up after running this for 20 mins as excel stops responding.

Hoping there is a faster solution.

Regards,
Erica

The following VBA will achieve this, though the output (3rd worksheet) will not be in the same order as you have above (which I presume does not matter). The Sheet names are hard coded and not doubt this could be more efficiently achieved with arrays, but this does what you're after. Take note of the instructions in the comments to subStart().

Public iCustomer As Integer
Public iRow As Integer

Sub subStart()
' Run subStart to process.
' It requires sheets Sheet1, Sheet2 and Sheet3
' Sheet1 must have the format (with the heading row):
' Market|Customer|Product|Site|Ratio
' US|||New York|0.7
' Sheet2 must have the format (with the heading row)
' Market|customer
' US|AAA
' For both sheets the | denotes the next cell
' Sheet3 should be blank
Sheet3.Select
' Enter the headings to Sheet3
Range("A1").Value = "Market"
Range("B1").Value = "Customer"
Range("C1").Value = "Product"
Range("D1").Value = "Site"
Range("E1").Value = "Ratio"
' Now initialise and process Sheet2
iRow = 2
iCustomer = 2
Call subDoSheet2
End Sub

Sub subDoSheet2()
b = True
tempCustomer$ = ""
Do While b = True
    Sheet2.Select
    ' Get the next market/customer combination
    r$ = "A" & Trim(Str(iCustomer))
    Range(r$).Select
    market$ = ActiveCell.FormulaR1C1
    If market$ = "" Then
        ' When we get past the last market/customer we're finished
        Exit Do
    End If
    r$ = "B" & Trim(Str(iCustomer))
    Range(r$).Select
    customer$ = ActiveCell.FormulaR1C1
    ' Process Sheet1 with for market/customer
    Call subDoSheet1(market$, customer$)
Loop
End Sub

Sub subDoSheet1(marketS2$, customerS2$)
iLocal = 2
Do While iLocal > 0
    Sheet1.Select
    Range("A" & Trim(Str(iLocal))).Select
    marketS1$ = ActiveCell.FormulaR1C1
    If marketS1$ = "" Then
        iCustomer = iCustomer + 1
        Exit Do      ' end of the sheet
    End If
    Range("B" & Trim(Str(iLocal))).Select
    customerS1$ = ActiveCell.FormulaR1C1
    Range("C" & Trim(Str(iLocal))).Select
    productS1$ = ActiveCell.FormulaR1C1
    Range("D" & Trim(Str(iLocal))).Select
    siteS1$ = ActiveCell.FormulaR1C1
    Range("E" & Trim(Str(iLocal))).Select
    ratioS1$ = ActiveCell.FormulaR1C1
    Sheet3.Select
    ' When the customer is 'default' (not specified) we want that
    ' output for this customer
    If Len(customerS1$) = 0 Then
        Range("A" & Trim(Str(iRow))).Value = marketS2$
        Range("B" & Trim(Str(iRow))).Value = customerS2$
        Range("C" & Trim(Str(iRow))).Value = productS1$
        Range("D" & Trim(Str(iRow))).Value = siteS1$
        Range("E" & Trim(Str(iRow))).Value = ratioS1$
        iRow = iRow + 1
    ' When the customer matches we want that
    ' output for this customer
    ElseIf customerS1$ = customerS2$ Then
        Range("A" & Trim(Str(iRow))).Value = marketS2$
        Range("B" & Trim(Str(iRow))).Value = customerS2$
        Range("C" & Trim(Str(iRow))).Value = productS1$
        Range("D" & Trim(Str(iRow))).Value = siteS1$
        Range("E" & Trim(Str(iRow))).Value = ratioS1$
        iRow = iRow + 1
    End If ' else do nothing
    iLocal = iLocal + 1
Loop
End Sub

 

[hernantorres23]

I don't understand what you want with this query, but if you have a lot of data, you can do it with MsAccess.
Full example here

rptElysium

Market	Customer	Product	Site	Ratio
US	AAA	​	New York	0.7​
US	AAA	​	Orlando	0.3​
US	BBB	​	New York	0.7​
US	BBB	​	Orlando	0.3​
US	CCC	​	New York	0.7​
US	CCC	​	Orlando	0.3​
US	DDD	​	New York	0.7​
US	DDD	​	Orlando	0.3​
US	EEE	​	New York	0.7​
US	EEE	​	Orlando	0.3​
US	AAA	111​	New York	1​
US	BBB	222​	New York	0.4​
US	BBB	222​	Orlando	0.6​
US	AAA	999​	New York	1​
US	BBB	999​	New York	1​
US	CCC	999​	New York	1​
US	DDD	999​	New York	1​
US	EEE	999​	New York	1​

SELECT master.Market, customers.Customer, master.Product, master.Site, master.Ratio
FROM customers, master
where isnull(master.product)
union
SELECT master.Market, customers.Customer, master.Product, master.Site, master.Ratio
FROM customers, master
where isnull(master.customer)
union
SELECT master.Market, master.Customer, master.Product, master.Site, master.Ratio
FROM master
where (not isnull(master.customer) and not isnull(master.product))
ORDER BY master.Product;

 

[Akuini]

Hi, elysium
Do you only have 1 market, i.e US?

 

[elysium]

Hi,

Will have more than that but I am stuck even with the data for US only.

Hi, elysium
Do you only have 1 market, i.e US?

 

[elysium]

No Access

I don't understand what you want with this query, but if you have a lot of data, you can do it with MsAccess.
Full example here

rptElysium

Market	Customer	Product	Site	Ratio
US	AAA	​	New York	0.7​
US	AAA	​	Orlando	0.3​
US	BBB	​	New York	0.7​
US	BBB	​	Orlando	0.3​
US	CCC	​	New York	0.7​
US	CCC	​	Orlando	0.3​
US	DDD	​	New York	0.7​
US	DDD	​	Orlando	0.3​
US	EEE	​	New York	0.7​
US	EEE	​	Orlando	0.3​
US	AAA	111​	New York	1​
US	BBB	222​	New York	0.4​
US	BBB	222​	Orlando	0.6​
US	AAA	999​	New York	1​
US	BBB	999​	New York	1​
US	CCC	999​	New York	1​
US	DDD	999​	New York	1​
US	EEE	999​	New York	1​

SELECT master.Market, customers.Customer, master.Product, master.Site, master.Ratio
FROM customers, master
where isnull(master.product)
union
SELECT master.Market, customers.Customer, master.Product, master.Site, master.Ratio
FROM customers, master
where isnull(master.customer)
union
SELECT master.Market, master.Customer, master.Product, master.Site, master.Ratio
FROM master
where (not isnull(master.customer) and not isnull(master.product))
ORDER BY master.Product;

 

[hernantorres23]

Are you saying what you will more data and then you are writing, no Access

?.

MsAccess is a very good choice/ option for you, reports, excellent for create forms, querys, recordsets, export to Excel, and much much more. I fact everything that you can do in Excel, is possible in Access
But, finally it is your your questions and you know better, the answer what you want
?.
Access is a very good option for you, reports, excellent for create forms, querys, recordsets, export to Excel,
and much much more.
But, finally it is your your questions and you know better, the answer what you want.

 

[Akuini]

Try this:
I assumed:
- data is in sheet1, sheet2, sheet3
- data in "sheet2" is sorted by col A (Market).

Sub a1118750a()
'https://www.mrexcel.com/board/threads/nested-loops-multiple-conditions.1118750/#post-5393973

Dim i As Long, j As Long, k As Long
Dim va, vb, x
Dim d As Object

Application.ScreenUpdating = False
Sheets("Sheet2").Activate
Set d = CreateObject("scripting.dictionary")
d.CompareMode = vbTextCompare

va = Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
For i = 2 To UBound(va, 1)
 j = i
    Do
        i = i + 1
        If i > UBound(va, 1) Then Exit Do
    Loop While va(i, 1) = va(i - 1, 1)
    i = i - 1

    d(va(i, 1)) = Application.Transpose(Range(Cells(j, "B"), Cells(i, "B")).Value)

Next

Sheets("Sheet1").Activate
va = Range("A2:E" & Cells(Rows.Count, "A").End(xlUp).Row)
ReDim vb(1 To 1000000, 1 To 5)

    For i = 1 To UBound(va, 1)
        If d.Exists(va(i, 1)) Then
            If va(i, 2) = "" Then
                For Each x In d(va(i, 1))
                    k = k + 1
                    vb(k, 1) = va(i, 1)
                    vb(k, 2) = x
                    vb(k, 3) = va(i, 3)
                    vb(k, 4) = va(i, 4)
                    vb(k, 5) = va(i, 5)
                Next
            
            Else
                    k = k + 1
                    vb(k, 1) = va(i, 1)
                    vb(k, 2) = va(i, 2)
                    vb(k, 3) = va(i, 3)
                    vb(k, 4) = va(i, 4)
                    vb(k, 5) = va(i, 5)
        
            End If
        
        End If
    
    Next

Sheets("Sheet3").Activate
Cells.ClearContents
Range("A1").Resize(, 5).Value = Sheets("Sheet1").Range("A1").Resize(, 5).Value
Range("A2").Resize(k, 5) = vb

Application.ScreenUpdating = True
End Sub

Example:

Book1
	A	B	C	D	E
1	Market	Customer	Product	Site	Ratio
2	US			New York	0.7
3	US			Orlando	0.3
4	US		999	New York	1
5	US	AAA	111	New York	1
6	US	BBB	222	Orlando	0.6
7	US	BBB	222	New York	0.4
8	China			Beijing	3
9	China	GGG	77	Beijing	4
10	China	BBB	88	Hong Kong	5
Sheet1

Book1
	A	B
1	Market	Customer
2	China	AAA
3	China	BBB
4	China	GGG
5	US	AAA
6	US	BBB
7	US	CCC
8	US	DDD
9	US	EEE
Sheet2

Book1
	A	B	C	D	E
1	Market	Customer	Product	Site	Ratio
2	US	AAA		New York	0.7
3	US	BBB		New York	0.7
4	US	CCC		New York	0.7
5	US	DDD		New York	0.7
6	US	EEE		New York	0.7
7	US	AAA		Orlando	0.3
8	US	BBB		Orlando	0.3
9	US	CCC		Orlando	0.3
10	US	DDD		Orlando	0.3
11	US	EEE		Orlando	0.3
12	US	AAA	999	New York	1
13	US	BBB	999	New York	1
14	US	CCC	999	New York	1
15	US	DDD	999	New York	1
16	US	EEE	999	New York	1
17	US	AAA	111	New York	1
18	US	BBB	222	Orlando	0.6
19	US	BBB	222	New York	0.4
20	China	AAA		Beijing	3
21	China	BBB		Beijing	3
22	China	GGG		Beijing	3
23	China	GGG	77	Beijing	4
24	China	BBB	88	Hong Kong	5
Sheet3

 

[elysium]

Sorry for not elaborating but I am on a company laptop and do not have MSAccess available.. Can I do this on spyder?

?.

MsAccess is a very good choice/ option for you, reports, excellent for create forms, querys, recordsets, export to Excel, and much much more. I fact everything that you can do in Excel, is possible in Access
But, finally it is your your questions and you know better, the answer what you want
?.
Access is a very good option for you, reports, excellent for create forms, querys, recordsets, export to Excel,
and much much more.
But, finally it is your your questions and you know better, the answer what you want.