Highline Excel Class 33: IF AND OR Customer Credit Analysis

Hi...I am looking for someone to help me on this problem of mine...I am running a small business of timber and plywood and I usually record my daily sales in a workbook which has a main sheet for recording daily sales [having multiple columns] and some other sheets for particular customer credit a/cs....I want the customers credit info from the daily sales sheet to get entered in the customer's respective worksheet automatically....I have tried to use advance filter but that does not add the data automatically everytime I update the daily sales sheet..My daily sales worksheet look a bit like this

Date cash/credit CustomerName Item Qnty[in cft] Rate[per cft] Amount
03/10/2011 credit xyz 4x1.5 2.0355 100 203.55
03/10/2011 cash cash 5x1.25 1.0000 200 200.00
03/10/2011 credit abc 3x1.5 3.0000 300 900.00

and so on.......and my cust a/c worksheet have columns like this...
Date Item Qnty[in cft] Rate[per cft] Amount


Here xyz and abc are customers name with whose names i have a worksheets and i want their respective entries to get copied in their particular worksheets whereas i don't want the cash sales to get entered anywhere else...

Can anyone help me with this please.....????

This might be a 400-level question, but figured this would be the correct forum to address those type of questions...

Let's say I have a flat file with three columns of data; Date (A), Email Address (B), and Revenue (C). Each time someone completes a purchase, a new row is added with the above information - the date they purchased, their email address, and how much they spent. So I can have several hundred rows per day.

Straight-forward so far.

So I have a year's worth of data (meaning several thousand rows) in which I need to figure out which customers are return customers (based on Email Address) and which ones are new. Then I would like to figure out things like 'average days between purchases' and essentially the Lifetime Value.

I'm assuming helper rows or additional classification tables will be needed, but I sure could use some help getting started.

Much appreciated. Thank you.

Can anyone help?

I've been searching the Excel is Fun site for 3 hours trying to avoid asking this question, but I just can't find the video to show me what I am looking for. Would you be kind enough to point me in the right direction?

Here's what I am looking to figure out:

Let's say a have a huge set of data that contains 3 columns (Invoice Date, Customer #, Sale Amount) and I am looking to determine what % of my customers have bought 1 time, 2 times, 3 times, etc. Then also figure out the average order amount for each of the aforementioned groups.

So for example:

1/1/10 - Customer #1 - $25
1/1/10 - Customer #2 - $50
1/9/10 - Customer #1 - $50
9/3/10 - Customer #2 - $10
9/7/10 - Customer #3 - $35

From the list above...

- you can see that Customer #1 bought 2 times, for a total of $75, creating an average order of $37.50

- you can see that Customer #2 bought 2 times, for a total of $60, creating and average order of $30.00

- you can see that Customer #3 bought 1 times, for a total of $35, creating and average order of $35


I have tried countless way to do this in a pivot table, but just can't get close.

Once I have my data, I will then look to find a way to further group everyone who purchased 1 time only, more than 1 time, 2-5 times, etc. in order to learn more about the purchasing habits of customers.

Any idea how to do all of this?

Dear Sir,


I am facing an recon problem for matching debit and creit basis individual & summation of values also. The total of debit values plus credit values = Zero. We need to mark off these debits with the credits ( single or multiple ) values. Is there any solution / macro available. Kindly guide.

I have attahced a sheet with example. I am not good in reading a macro hence requesting assistance.






I have a set of debit values with a reference and I have a set of credit values which out reference numbers I need to know from the debit valus a summation of which all the credits can be used and marking the debit refernce against the credit values If the credit values have the reference then it should be identifed as matched and the other credit entries needs to be taken for next caluclation for Ex. Ref 16 = Rs.30000 which is matched below with Rs.5000, Rs.12000, Rs.13000 there is one more creidt is available for Rs.30000 but no debits hence is blank in the reference column Hope you understand the logic like goal seek Amt and ref will be in the same column . Is there any macro for matching and mentioning the reference numbers against credit values Single debit and multiple credit and multiple credit and single debit also possible

Can i convert a csv database with over 900 customers, to an individual
template that i have created which i use for customers as their individual
price sheets

Hi All,

Cant quite get my little macro to work.

Basic premise is i have a table of customer records in the following format.

Customer1 | Invoice no. | Invoice class | Due Date | Amount
Customer1 | Invoice no. | Invoice class | Due Date | Amount
Customer1 | Invoice no. | Invoice class | Due Date | Amount
Customer2 | Invoice no. | Invoice class | Due Date | Amount
Customer2 | Invoice no. | Invoice class | Due Date | Amount
Customer3 | Invoice no. | Invoice class | Due Date | Amount

What i need to do is to sort these records so that i have 1 row for each customer which repeats the various fields accross the row instead of listing them on multiple rows.

e.g

Customer1 | Invoice no. | Invoice class | Due Date | Amount | Invoice no. | Invoice class | Due Date | Amount

Problem that i am having is remembering where the customer "changes"

see my attempt:

Code:

Dim LSearchRow As Integer
Dim Ccount As Integer

LSearchRow = 2
Ccount = 0

     
    While Len(Range("A" & CStr(LSearchRow)).Value) > 0
    
MARKER:
            
            Cells(CStr(LSearchRow), 2).Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Cut
                              
  
            
            If Range("A" & CStr(LSearchRow)).Value = Range("A" & CStr(LSearchRow - 1)).Value Then
            
                Ccount = Ccount + 1
                            
                Cells(CStr(LSearchRow - Ccount), 2).Select
                
                Selection.End(xlToRight).Select
                Selection.End(xlToRight).Offset(0, 1).Select
                ActiveSheet.Paste
                
                LSearchRow = LSearchRow + 1
                
            GoTo MARKER
                
            End If
            
            
            
            
            Cells(CStr(LSearchRow), 2).Select
            Selection.End(xlToRight).Offset(0, 1).Select
            ActiveSheet.Paste
                       
            LSearchRow = LSearchRow + 1
                   
            
            
            
    Wend


    
End Sub

Any help would be greatly appreciated! - Still struggling with this one.

Hi guys,

I want to create a customer message box, with 6 different buttons, each taking the user to a different worksheet when clicked.

Can this be done easily?

Thanks
Joel

Hi I am looking to use Excel as a customer Database Just for My Daily call cycle as sales representative How would I set this up, we would need such tabs as Customer History, In-store Targets, Follow Up, Business Reviews Reminders, Much like a call card book with details of History & Invoices etc Is there a template i can use or modify are there any recommended software that have the same functions?

Thanks

Sir,
I have the following Data base in sheet 1:
Invoice No. Date Customer Qty. Rate Value(Rs)
10201 1.9.09 A 5 25.00 125.00
10301 1.9.09 B 10 25.00 250.00
10302 2.9.09 C 10 25.00 250.00
10205 3.9.09 A 10 25.00 250.00
10206 4.9.09 B 5 25.00 125.00
10303 5.9.09 A 5 25.00 125.00

If Filetered the above list by customer, customer details will appear on screen i.e; Invoice No., Date Customer, Qty. Rate, Value (Rs)
The same will appear in next sheet by using formula (without advance filter and sort). Because I have to give all details (i.e. Abstract) to Customer.
What I want, I will type Customer name in next Sheet and that customer details i.e; Invoice-wise, Date-wise, Qty.-wise, Rate-wise, Value-wise will display. Is there any formula?
The display will apeear like this:
Customer Name : "A"
Invoice No. Date Customer Qty. Rate Value (Rs)
10201 1.9.09 A 5 25.00 125.00
10205 3.9.09 A 10 25.00 250.00
10303 5.9.09 A 5 25.00 125.00

Does anyone know how to do a MOD-10 credit card number check in Excel 2000?
Is there a way to do it using a macro and Visual Basic?