Classic Customer Invoices Filter problem

  • Thread starter Thread starter u473
  • Start date Start date
U

u473

Specific list of Customers in Sheet1
Invoices list in Sheet2 [Of course with more than one invoice per
Customer]
How do I loop thru Sheet1, retrieve pertaining data in Sheet2 and
write it in Sheet3.
Classic and simple but I need a refresher and my browsing of this
group has not
produced the desired result yet.
What will change in the code, if any, if the 2 lists are not sorted by
Customer ?
Thank you for your help

Celeste
 
Specific list of Customers in Sheet1
Invoices list in Sheet2 [Of course with more than one invoice per
Customer]
How do I loop thru Sheet1, retrieve pertaining data in Sheet2 and
write it in Sheet3.
Classic and simple but I need a refresher and my browsing of this
group has not
produced the desired result yet.
What will change in the code, if any, if the 2 lists are not sorted by
Customer ?
Thank you for your help

Celeste
Click to expand...

Here is some general pseudo-code ... if you need help with any of it,
post again!

'First, get list of customers
sheets("sheet1").activate 'or better yet, change the sheet name in
VBE, so it's just Sheet1.Activate
cells(1,1).select
do until activecell=""
for icounter = 1 to inumcustomers
if strCustomer(icounter)=activecell then
 
Specific list of Customers in Sheet1
Invoices list in Sheet2 [Of course with more than one invoice per
Customer]
How do I loop thru Sheet1, retrieve pertaining data in Sheet2 and
write it in Sheet3.
Classic and simple but I need a refresher and my browsing of this
group has not
produced the desired result yet.
What will change in the code, if any, if the 2 lists are not sorted by
Customer ?
Thank you for your help
Click to expand...
Celeste
Click to expand...

Here is some general pseudo-code ... if you need help with any of it,
post again!

'First, get list of customers
sheets("sheet1").activate 'or better yet, change the sheet name in
VBE, so it's just Sheet1.Activate
cells(1,1).select
do until activecell=""
for icounter = 1 to inumcustomers
if strCustomer(icounter)=activecell then
Click to expand...

AAAH! Hit Send too early!

Option Explicit

Private Sub OneTwoThree()

'declarations
Dim bnDup As Boolean
Dim iCounter As Integer
Dim iNumCustomers As Integer
Dim strCustomers() As String
Dim varValue As Variant
Dim varValueSum() As Variant
Dim strCustomersActive As String

'First, get list of unique customers
Sheet1.Activate
Cells(1, 1).Select

Do Until ActiveCell = ""
bnDup = False
For iCounter = 1 To iNumCustomers
If strCustomers(iCounter) = ActiveCell Then
bnDup = True
End If
Next iCounter

If bnDup = False Then
iNumCustomers = iNumCustomers + 1
ReDim Preserve strCustomers(iNumCustomers)
strCustomers(iNumCustomers) = ActiveCell
End If

ActiveCell.Offset(1, 0).Select
Loop

'Next, get required data from sheet 2 .... say we just need to sum
values in column B for customers in column A
Sheet2.Activate
Cells(1, 1).Select

ReDim varValueSum(iNumCustomers)

Do Until ActiveCell = ""
strCustomersActive = ActiveCell
varValue = ActiveCell.Offset(0, 1).Value

For iCounter = 1 To iNumCustomers
If strCustomersActive = strCustomers(iCounter) Then
varValueSum(iCounter) = varValueSum(iCounter) +
varValue
End If
Next iCounter

ActiveCell.Offset(1, 0).Select
Loop

'Go to sheet 3 and print

Sheet3.Activate
Cells(1, 1).Select

For iCounter = 1 To iNumCustomers
ActiveCell = strCustomers(iCounter)
ActiveCell.Offset(0, 1) = varValueSum(iCounter)
ActiveCell.Offset(1, 0).Select
Next iCounter

End Sub
 
Wooowww!!! Brilliant, You made my day.
I am going to put it to test and chew on it.
Thank you again.

Celeste
 
I did appreciate your code, it works and I am still going thru it to
learn.
But I was not trying to sum the filtered data, just listing the
filtered data
as shown below. The modif is probably minor but I cannot figure it
yet.
Thank you again
Celeste

Sheet1 :
Customer
B
G
K

Sheet2 :
Invoice Customer Product
100 K 200
101 L 201
102 B 202
102 B 403
105 K 307
106 A 251
107 B 525

Sheet3: (Filter Result)
Customer Invoice Product
B 102 202
B 102 403
B 107 525
G
K 100 200
K 105 307
 
This should mimic pretty closely what you're looking for:

Option Explicit

Private Sub OneTwoThree()

'declarations
Dim bnDup As Boolean
Dim iCounter As Integer
Dim iNumCustomers As Integer
Dim strCustomers() As String
Dim strCustomersActive As String
Dim strInvoice As String
Dim strProduct As String
Dim iCountNumTimes As Integer

'clear sheet3
Sheet3.Activate
Cells(2, 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

'First, get list of unique customers
Sheet1.Activate
Cells(1, 1).Select

Do Until ActiveCell = ""
bnDup = False
For iCounter = 1 To iNumCustomers
If strCustomers(iCounter) = ActiveCell Then
bnDup = True
End If
Next iCounter

If bnDup = False Then
iNumCustomers = iNumCustomers + 1
ReDim Preserve strCustomers(iNumCustomers)
strCustomers(iNumCustomers) = ActiveCell
End If

ActiveCell.Offset(1, 0).Select
Loop

'Next, get required data from sheet 2 (invoices and products)

For iCounter = 1 To iNumCustomers
Sheet2.Activate
Cells(2, 2).Select
iCountNumTimes = 0

Do Until ActiveCell = ""
strCustomersActive = ActiveCell
strInvoice = ActiveCell.Offset(0, -1)
strProduct = ActiveCell.Offset(0, 1)
If strCustomersActive = strCustomers(iCounter) Then
iCountNumTimes = iCountNumTimes + 1
Call CopyMe(strCustomersActive, strInvoice,
strProduct)
End If

Sheet2.Activate
ActiveCell.Offset(1, 0).Select
Loop

If iCountNumTimes = 0 Then
Call CopyMe(strCustomers(iCounter), "", "")
End If
Next iCounter


End Sub

Private Sub CopyMe(strCust, strInv, strProd As String)

'Go to sheet 3 and print
Sheet3.Activate
Cells(2, 1).Select

Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select
Loop

ActiveCell = strCust
ActiveCell.Offset(0, 1) = strInv
ActiveCell.Offset(0, 2) = strProd

End Sub





Cheers

Chris
 
I did appreciate your code, it works and I am still going thru it to
learn.
But I was not trying to sum the filtered data, just listing the
filtered data
as shown below. The modif is probably minor but I cannot figure it
yet.
Thank you again
Celeste

Sheet1 :
Customer
B
G
K

Sheet2 :
Invoice Customer Product
100 K 200
101 L 201
102 B 202
102 B 403
105 K 307
106 A 251
107 B 525

Sheet3: (Filter Result)
Customer Invoice Product
B 102 202
B 102 403
B 107 525
G
K 100 200
K 105 307
Click to expand...

Celeste, be aware that you could probably use a list of customer names
and a autofilter to get the required format if you're so inclined to
do it that way .... but this code does work pretty well :P

HTH

Chris
 
Back
Top