Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Classic Customer Invoices Filter problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Classic Customer Invoices Filter problem
On Apr 5, 1:06 pm, u473 wrote:
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Classic Customer Invoices Filter problem
On Apr 5, 6:00 pm, cht13er wrote:
On Apr 5, 1:06 pm, u473 wrote: 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 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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Classic Customer Invoices Filter problem
Wooowww!!! Brilliant, You made my day.
I am going to put it to test and chew on it. Thank you again. Celeste |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Classic Customer Invoices Filter problem
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Classic Customer Invoices Filter problem
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Classic Customer Invoices Filter problem
On Apr 5, 8:35 pm, u473 wrote:
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter Customer Dates not working | Excel Discussion (Misc queries) | |||
CONVER CSV CUSTOMER DATA TO A CUSTOM INDIVI CUSTOMER PRICE SHEET | Excel Discussion (Misc queries) | |||
how do I advance filter a large csv file for unique customer #'s | Excel Worksheet Functions | |||
linking customer database to invoices in excel | Charts and Charting in Excel | |||
linking customer database to invoices in excel | Excel Worksheet Functions |