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

 

[elysium]

Wow, 58k lines in just 2 seconds. Thank you so so much! I will make sure to break this down and learn it ?

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

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.
Note: you don't need to quote the whole post, 1-4 lines should be enough.

 

[hernantorres23]

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

Spyder (Python) of course you can, Python is the best, take a look to Pandas Library

 

[kennypete]

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

Hi Erica, my (and it seems others') view is Excel/VBA is ugly for doing this because what you are really doing here is chunky processing (for a much larger dataset). Others made suggestions such as MS Access, which you say is not an option for you, so...

A better solution anyway would be using a language with pattern matching strengths to build shelves/arrays for the sheet1 and sheet2, Python (you asked about Spyder) or Perl perhaps, and minipulate those to product sheet3. If you can run it, check out nested-loops-multiple-conditions.1118750.zip where I illustrate your solution in a standalone Omnimark language procedure (more correctly the free OMLE lite edition, which is decades old now, but hey, it works well still even in Win10). Presuming your PC is not so locked down that you cannot run .exe files or .bat files, if you extract this .zip file to any folder and run the sheet3.bat it will process sheet1.csv and sheet2.csv creating your sheet3.csv, which can then be opened up in Excel. (It would be a simple matter to invoke this with VBA (i.e. have VBA save down your two sheets to a folder, run the OMLE script outside Excel, and reopen the sheet3.csv output)

Incidentally this script will handle any combinations of Markets, plus it will handle quotes in the customer names (which Excel will add if it is something like "ACME & Co., Ltd" because it has to escape out the comma in the cell). I tried it by adding a few hundred customers and some more products and it was all good. Bonus: I expect (given Omnimark's inherent stream processing speed) it will run your hundreds of customers in a few seconds, not minutes or hours, so if you replace sheet1.csv and sheet2.csv with your data it should be lightning fast).

 

[elysium]

Thank you all!