I am working on a project and I have a list of customers. I need to calculate how many customers we had in week 1, week 2 etc of year. This would allow me to draw a beautiful chart of our company. How would you calculate this? Here is some data:
ABCDEF1February 2, 2011
Week1232February 15, 2011
Customers???3March 15, 2011
4April 15, 2011
5April 15, 2011
[code].....
Making a customer analyse and now I want to know which customers are resposible for 50 % of my revenue.
View 4 Replies View RelatedI have huge data of income of employees.
In column "A" i have names of employees,
in column "B" i have income of last month and
in column "c" i have income of current month.
In column "D" i have calculated difference between two month's income(C1-B1).
Now based on values in column D i want to find names of top 5 gainers and top decliners (i.e. i want names of top 5 people who are having highest value in column "D"). I don't want to use pivot and sort, I want these names by using any formula.
Its working perfectly. But now I have added one more column in the beginning as country names. Now i want to make a dashboard so that i will create a drop down with country names and country i will select top gainers should come with ur given formula.
I have the following sheet, each time a customer buys a savings product they are put onto the list, the same customer can appear as many times as they like on the list is they buy more than once, buys product on 1st of april then another product on the 2nd of april they appear 2ce on the sheet.
I have done the forumla to sum the amount of times they appear on the sheet ( total number of sales ) but I need to work out the total comission per person.
Code:
Customer 1 5,000
Customer 2 10,000
Customer 3 6,000
Customer 4 4,000
Customer 5 3,000
If I have a Table as above, how do I return the Top 3 Customers in 3 cells?
I can get the Values with
=LARGE(Table1[[#All],[Sales]],1)
=LARGE(Table1[[#All],[Sales]],2)
=LARGE(Table1[[#All],[Sales],3)
How would I return the Customer Names?
I have exported a report out of Quickbooks with all my customers that have done business with us from 2005-2008. I want to be able to delete the customers from the worksheet that have done any business in 2008. So that I am left with a list of customers that have not done any business with us in 2008. Can a formula or macro do this for me. I don't just want to delete the rows that contain any dates in 2008, but delete the customer as a whole from the workbook.
TypeDateNumMemoNameItem@Home RealtyInvoice10/20/200522813BIC Clic Stic Black Barrel Red Trim White and Red Imprint Black Ink Medium Point@Home RealtyCSInvoice10/20/200522813Printing an additional color on the merchandise@Home RealtyADDCOLORInvoice10/20/200522813Shipping & Handling@Home RealtyShippingInvoice12/01/2005FC 2058Finance Charges on Overdue Balance@Home RealtyFin ChgInvoice07/18/200730753Silkscreen add web address to shirts@Home RealtySilkscreenInvoice07/18/200730753Silkscreening Set-up fee@Home RealtySet-upTotal @Home Realty1 Source ManufacturingInvoice08/16/2007311575-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice08/16/200731157Set-up for Printing Plate1 Source ManufacturingPlate ChargeInvoice08/16/200731157UPS Ground Shipping & Handling1 Source ManufacturingShippingInvoice10/25/2007318755-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice10/25/2007318755-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice10/25/200731875Set-up for Printing Plate1 Source ManufacturingPlate ChargeInvoice10/25/200731875Shipping & Handling1 Source ManufacturingShippingInvoice07/08/2008343575-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice07/08/200834357Shipping & Handling1 Source ManufacturingShippingTotal 1 Source Manufacturing
I need some code to add to a macro that will delete customer account names that start with an "A" in column C. Specifically it needs to find customer names starting with the letter "A" and delete that entire row.
I currently have a combo box which enables me to create a new customer and save their details to a sheet called ClientTable. I can also press the drop down arrow of this combo box to retrieve an existing customer and their details from the same ClientTable sheet.
My problem is that when I select an existing customer and ammed any of their details - the new information is not being saved. Hence I cannot update an existing enrty once it is created.
how I can adjust the script below to enhance its functionality so customer details can be updated.
Code in the module
Public Sub SaveNewClient()
Dim NewRow As Long, IDNum As Long
If ClientTable Is Nothing Then
Set ClientTable = Sheets("ClientTable").CurrentRegion
End If
NewRow = ClientTable.Rows.Count + 1
If ClientTable.Rows.Count = 1 Then
IDNum = 0
Else
IDNum = NewRow - 2
End If
With Sheets("ClientTable")
I need to compile a 'sumifs' formula to add sales for the salesmen.
Below is an example table. My dilemma is determining that in addition to the sales I only want to 'sum' the 'CANCEL' sale of a customer that initially purchased a 'GOOD' Status and not a 'PENDING' status.
How can i write a formula to determine that (for example) Customer3 'cancelled' the 'pending' sale, therefore I do not want the 'cancel' to sum in my calculations.
Date Salesman Customer Product Status Sale
Jan 8th John Customer1 Apples Good $500
Jan 9th Mark Customer2 Pears Good $200
Jan 8th Kevin Customer3 Oranges Pending $250
Jan 15th Mark Customer2 Pears Cancel $200
Jan 16th Kevin Customer3 Oranges Cancel $250
I have a worksheet that needs customers split into different price bands
here we go
I have 13 customers all from the same group (a)
1 customer in group (b)
1 Customer in group (c)
& 80 independant customers (d) worksheet only has 9 (1,2,3,4,5,6,7,8,9)
Column a is a list
D17 is the drop down list
D21 is the starting price
C2:D5 is the pricing multiple to be applied
What I am trying to achieve is when an independant company is chosen it defaults to d for pricing.
I am looking for an efficient solution to the following problem. I have a sales table with two columns, titled C1 and C2. The first column lists the product sold, and the second column lists the associated customer.
Here's what I mean (though I can't figure out how to create neat columns in this post):
[C1] [C2]
Prod1 CharlieCo
Prod3 AlphaCorp
Prod2 BetaInc
Prod3 BetaInc
Prod1 AlphaCopr......................
The project I am currently working on is to extract the from over 25,000 lines of data the top ten of our customers for 2012. Our customers are billed monthly. We have about 2,300 customers. There are several customers who have more than 1 account.
The easy part has been to create a "charges" report and download into an excel format. The information can is alphabetized, but then the challenge comes in. Ideally it would be a snap to total each customer seperately and then somehow be able to extract the top ten from those totals.
I have the following data:
TRX_IDQTYCUST_ID892486021-67.991623230892486140-35.411623230892486378-29.271623230892486515-60
.711623230892488006-69.081623230892488082-43.261623230892486668-31.091623230892486794-27
.0562118898892546057-42.829337675892487417-19.331623230892487513-33.251623230892488890-33
.61719407892543537-13.891623142892544321-11.021623142892487602-18.921623230
Would like to know the number of unique customers (CUST_ID) have purchases and the sum of those purchases and how many transactions:
CUST_ID Sum of QTY Count of Transactions
50 $2000 1500
I cant seem to find how to do unique Cust using a pivot table - it just gives me a count of cust which is the same number as the number of transactions.
I have come across a situation where I am listing the top 10 customers based on their sales. I have a worksheet for each product I sell. I'm wondering if there is a way to write an excel formula to pull just the top 10 customers from just a single pivot table (respective to the product worksheet) instead of having to create a new pivot for every product and sort based on top 10 and then link to my spreadsheets?
View 2 Replies View RelatedI have sheet 1 a1:a3 with 3 check boxes.
In sheet 2 i have 3 ranges green = a1:a20, red = b1:b20, orange = c1:c20. (in each range there is a different list of customers) What i would like is on sheet 1 to click check box 1 and the values from range green (on sheet2) are dispalyed in sheet 1 @ d1
I am trying to figure out how I can sort multiple customers in a single cell, and then assign the customers to the outside salesman. The basic idea is in column A I have the customers for a specific job. Assume I have three customers (Alpha, Omega & Gamma) and they are all in ONE cell. I need to sort this and assign each customer to each of my outside salesmen (Bob, Ted, Fred) in another column. The other component to this question is that Bob, Ted & Fred may have MORE than one customer in that single cell........
View 5 Replies View RelatedI need a macro that will find a row on "Sheet4" that has the word "YES" in column "K" and then varify that there is a balance owed (<$0.00) in column "N" of that same row. If there is a balance owed I need the macro to copy the data from that row into a specific place on "Sheet2" (Column "C" to E10 on the new sheet, D to E11, Q to E12, R to M12, S to T12, G to Y26, H to AF29, M to AF30, N to AF32, and lastly the current date to AC10) then Copy that sheet to a new workbook then rename the copied sheet the the Value of "E10" on that sheet and then clear the data on "Sheet2" so that only the information that was on the sheet before the data transfer is left, If there is no balance owed then it continues down column "K" on it's own to check the next one until it reaches the end of the data in "sheet4" (which is row 2111).
My goal is to be able to use this Macro to sift through a list of customers and to find ones that are past due on their bill (Based of of Column "K" which states that Yes the Bill is past due, and then variefies that it hasn't already been paid by checking the Balance Due Column "N") and then transfers their customer information to an invoice and then moves the invoice to a New workbook (So as to not clutter up my already to large Customer workbook)
This is the code that I have been playing with but i'm getting stuck on how exactlly to do this so i'm sure theres a lot in there that doesn't make any sense.
Sub FindandCopyRows()
Dim Data As Variant
Dim DataFound() As Variant
Dim iValue As Integer
Dim j As Long
Dim i As Integer
I have 8 Sheets in my worksheet with orders from my customers. Column C is their PO# and Column M is the Status of the order. What im looking to do is set up a summary sheet with the List of 8 customers that tells me what PO#'s are in a particular status.
I need a formula to list all PO's that are in "Fabricating" or "Completed" Statuses.
I have gotten this far:
=if(Sheet1!M25="Fabricating",Sheet1!C25,if(Sheet1!M25="Completed",Sheet1!C25,"None"
which works perfectly for the individual rows, but i need it to do it for all rows (1-2500). If i do Sheet1!M2:M2500, i loose it.
I have many customers particulars and data coming in everyday. How i do create a code such that with one click i'll be able to update my worksheet in Excel without typing in manually?
View 10 Replies View RelatedI have account numbers in column A where the customers have purchased at least 1 accessory, where the accessory numbers are in column B. If they have purchased more than 1, their account number will be duplicated in column A. I would like to represent the data so that the account number is unique in column A, accessory 1 in column B, accessory 2 in column C and accessory 3 in column D.
Is there any way of doing this?
I'm open to suggestions in MS Access.
I have attached a spreadsheet with a small amount of the data I need to use. This is the distance between customers in kms. I need to be able to have excel look up the cell reference for distance between two customers though they will not always be the same customers every day in the same order.
View 2 Replies View RelatedI am creating a spreadsheet to track customers contact information and purchase history.
A B C D
1 First Name Last Name Phone Number Etc.
2 Mike Jones
3
I wanted to know if theres a way to click on a customer (say Mike Jones) and be able to enter and view purchase history information relating to that customer in either a drop down box or different sheet?
ex.
Mike Jones
Date Item Spent Last Contacted
4/24 Toy $500 4/31
5/16 Movie $15 5/20
I had sent 1000 emails to the my customers by using mail merge in word document. Now I need to reply to these 1000 customer again individual one by one. The message is the same, but I need these thing to be sent in individual.
View 1 Replies View RelatedHow to use conditional formatting in Excel 2007 (). I'm creating a spreadsheet to track my group's timeline for completing strategy meetings with our customers. When we have a meeting, I will enter the date of that meeting in a specific cell. In a successive column(s), I'd like for a cell(s) to turn a specific color based on the length of time from the last meeting (or if there is no date for a meeting entered - i.e. no initial meeting has been set).
View 9 Replies View RelatedDropbox - Final.xlsm
Here is the above link. Am looking at a button which saves whatever is the temp worksheet row in the customers worksheet. The temp worksheet basically takes the data from the Quotations worksheet and places it in a row.
I need some code to add to my macro. The code needs to cut customer names starting with "A" or a numeral, from sheet 1, column C.
The entire row needs to be pasted into sheet 2, starting at cell A2
I have two columns in a payment schedule (which adjusts according to certain user inputs) that I need to use in my NPV calculation.
The first column is the Total Payment and the second is Inducements.
Therefore each value in the NPV calc. needs to be the sum of a given period's payment and inducement (but i don't want/have a separate column which calculates the sums). The number of periods adjusts with the users input of Term. There also may be periods where there is a payment but no inducement.
The formula below calculates between rows 2 and 109. How can I change it to calculate between row 2 and the last used row in the sheet.
Code:
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=(RC[-1]-MIN(R1C[-1]:R109C[-1]))/(MAX(R1C[-1]:R109C[-1])-MIN(R1C[-1]:R109C[-1]))"
i got a problem to calculate IRR and NPV for my company cash flow. i so confuse how to calculate cos the initial investment (expense) is pay in installment by yearly basis. i hope anyone can help me to solve this problem.
I m not sure whether what i'm doing is correct or not. thanx
http://spreadsheets.google.com/ccc?k...Hd2JSrIj7H-Pew
If I have this serie of values in the A column:
34
33
33
33
30
29
26
26
20
19
17
17
And want this results in the B column:
1
3
3
3
1
1
2
2
1
1
2
2
Those numbers will indicate how many of the same are in a row.
What's the easiest way to accomplish this?