Counting Total £££ For Customers That Can Appear More Then Once
Apr 15, 2009
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.
View 3 Replies
ADVERTISEMENT
May 21, 2009
I have 80 entries in Column A. they all have a text of SS, LN, LA, LB in column B.
I want column C to total the said codes in B.
EX:
COLUMN A COLUMN B COLUMN C
UNIT # CODE TOTALS
6100 LN SS=3
6101 LA LA=2
6102 LB LB=2
6103 SS LN=2
6104 LA
6105 LN
6106 LB
6107 SS
6108 SS
View 3 Replies
View Related
May 21, 2013
I have a Workbook with multiple sheets with Data in them. I would like to Count the number of duplicate values between the sheets.
For example.
Sheet one has a list of case references Person A is dealing with.
Sheet two has a another list of case references Person B is dealing with however the also have a list of case reference's they are monitoring as part of a project.
Sheet 3 has got the total number of case references each Person is dealing with, it also contain the total number of cases Person A&B together are dealing with. however i need to find the total number of cases Person B is monitoring that Person A also has so that the total number of cases is the true number of total cases with no duplicates involved.
(References begin with VB then have a string of numbers afterwards and I would like to know the total number of cases the Person B is monitoring as well so these need ot be calculated).
View 7 Replies
View Related
Sep 25, 2013
I am trying to find a way of counting the total number of days medical devices were in-situ for a fairly large dataset.
The worksheet has a few thousand rows. Column A - unique identifier for patient; column B - date inserted (Aug 02 - Aug 13); column C - date removed or audit date. There is no missing data, all rows have both dates. Some devices in for few days or weeks, some for up to 7 years.
I want to count each 12 month period (starting 1 Aug 02) the total "device days" for that year. (e.g. if device inserted 1 Feb 03 and removed 1 Apr 03, in year period starting 1 Aug 02 would have been in for 59 days. Another device inserted on same day and not removed until 9 Sept 05, would for first period (01/08/02- 31/07/03) 181 days, second period 366 days, third 365 days and fourth (01/08/05-31/07/06) 39 days). Therefore for the two rows, the first period total would be 240 days (59+181).
View 3 Replies
View Related
Apr 1, 2014
I 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.
View 1 Replies
View Related
Apr 13, 2012
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].....
View 4 Replies
View Related
Jan 22, 2014
Making a customer analyse and now I want to know which customers are resposible for 50 % of my revenue.
View 4 Replies
View Related
Nov 12, 2012
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?
View 4 Replies
View Related
Oct 22, 2008
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
View 9 Replies
View Related
Mar 30, 2009
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.
View 9 Replies
View Related
Sep 12, 2009
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")
View 9 Replies
View Related
Aug 31, 2013
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
View 4 Replies
View Related
Aug 27, 2009
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.
View 10 Replies
View Related
Jan 15, 2010
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......................
View 5 Replies
View Related
Apr 12, 2013
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.
View 14 Replies
View Related
Oct 7, 2011
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.
View 5 Replies
View Related
Jun 13, 2013
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 Related
May 4, 2014
I 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
View 4 Replies
View Related
Aug 18, 2006
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 Related
Apr 6, 2008
I 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
View 7 Replies
View Related
Oct 28, 2009
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.
View 14 Replies
View Related
Sep 11, 2007
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 Related
Aug 5, 2008
I 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.
View 9 Replies
View Related
May 22, 2006
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 Related
Jul 1, 2013
I 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
View 3 Replies
View Related
Jul 7, 2014
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 Related
Apr 30, 2014
How 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 Related
Jan 7, 2010
I have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell.
Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example.
I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.
View 2 Replies
View Related
Apr 22, 2009
This may not be the best way to do this, but I don't know Macros or Pivot Tables.
I am looking for a way with formulas to do the following:
Within a workbook the 1st sheet is the data entry.
In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column:
Example:
Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry.
G2:G2999 in the same sheet is where the data is.
Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?
View 9 Replies
View Related
Mar 5, 2014
I need to set up an easy to use spread sheet for my office. It needs to be able to calculate the running total spent of fuel, as well as include any discounts we get and then calculate our total savings.So basically, total spent and total saved.
View 3 Replies
View Related
