Customer Filter
Mar 30, 2009
I have data for customers and the amounts they have paid.
In the amounts column i have figures and word.
What i wish to do is exclude/ filter from the list customers which only say large in the amount column.
However the problem i have is some customer which also have figures which i do not wish to exclude.
So basically i what to look down the whole list of customers for j blogg ( example) and see in the corresponding amount column. if it says large on every
amount line then i want to exclude them from the list.
View 9 Replies
ADVERTISEMENT
Apr 1, 2013
I have problems with my project. I want to filter data between two dates with customer ID and when click Export Report button , result will copy on next sheet ( sheet's name is report in my file attachment ) and auto sum similar products.
eExample_report.xlsm
View 1 Replies
View Related
Aug 1, 2014
I have a list of Spid numbers and a service category identifying if it is a Water Spid or a Waste spid.
The Spid is split into 2 parts. The Core is the identifier and the Category states if it is a Water or a Waste spid.
If the 1st digit in the Category is a 1 = Water.
If the 1st digit in the Category is a 2 = Waste.
There may only be a water spid or a property may have a water and waste spid.
What I want to do is have the information on 1 row rather than 2 rows
I have data in this format
SPID
Core
Category
Service Category
100000000151
100000000
151
1
[code].....
and I need to convert it into this format.
Core spid
Water
Waste
100000020
100000020101
100000020251
I have tried IF(lookup) and it works for the 1st row but I can't get it to look at the 2nd row.My data consists of 400,000 plus rows
View 3 Replies
View Related
Mar 30, 2009
I have a list of data in which it will ALWAYS have the following set up:
S-CustomerName-####
"S-" and "-####" will always remain the same - however, the customer name may be 2 digits or 20 digits - or anything inbetween.
I'm looking for a formula that will allow me to isolate the Customer name
I've attached a sample list of my data.
View 3 Replies
View Related
Jul 14, 2014
i have 2 dilemmas
**dilemma 1**
i have a date of purchase in column A
i have a customer number in column B
now if i want to search for a date and for it to display the results from columns C,D,E and F (purchased items) i know i use HLOOKUP
but how would i look for a customer and a date at the same time.
**dilemma 2**
i want to be able to put in a customer number and excel to tell me all the dates they purchased items.
currently i use
={IFERROR(INDEX(Table1[DATE],SMALL(IF(Table1[NUM]=$L$3,ROW(Table1[NUM])-MIN(ROW(Table1[NUM]))+1),ROWS($1:1))),"error")}
and i change the ROWS($1:1) to ROWS($1:2) or ROWS($1:3) etc
to bring in that result, but i want it to bring in unique dates, as a customer might ring us back with another purchase on the same date and i dont want it to duplicate the date.
View 13 Replies
View Related
Feb 3, 2009
I have been tasked with tracking the expected income for our Advertising Department. (I work for a newspaper.) The rate I charge an ad depends upon the total number of advertising units a customer purchases during an entire billing period. Essentially: I need to have a total of column G for when column B matches the current row. I need this in column K. The value of K should be the same when the value of B is the same.
My programing flow chart I sketched out checked to see if any row in column B that is above the current row matched the current row's data. If it did then it would use that row's value for the total number of units. If it did not it would add up the units from G when B matched the current column. I attached a document
View 2 Replies
View Related
Apr 16, 2009
I have 2 sheets in a workbook.Sheet1 is a master sheet with Customer code and there Sale.And Sheet2 is the sheet which has only Customer code.
Note: Its just a Example i have total 1924 data.
Now in Sheet 1 there are some Customer code which is Repeated some times 2 and 3 or even 4 times. In Sheet 2 Same as sheet 1 there are some data which is also Repeated. Now i how can i know which Customer Code in sheet 2 Missing from Sheet1
I have Colored Some Data in sheet 2 which is not match with Sheet1
For Example.
In Sheet 1 221000000232 is repeated 2 times but in Sheet2 has only 1 times.
View 2 Replies
View Related
Dec 13, 2012
I have a worksheet that contains the following columns:
Month
Customer
Invoice
Freq.
Total
[Code]...
Now, I want to have subtotals for each customer and each month.
Right now, I have to manually insert =count & =sum functions at the appropriate rows.
Ultimately, I want to know the average sales activity per month and average sales nominal per month.
Is there a better way to achieve what I want?
I had thought of COUNTIFS and SUMIFS, but then I'll have to change the criteria at every change of month & customer.
The average sales activity & average sales will be derived manually.
View 6 Replies
View Related
Aug 8, 2008
I need to create a list that has one row per paid date per customer on a new worksheet. My source data has one row per customer with the paid dates across 12 month columns. I have many rows in the source data with zero paid on all 12 months and I'd like to filter those out in the process.
This is not a simple Transpose because I want the customer number, and customer name to repeat for every instance of a paid amount.
I have attached a sample file with the current and desired layouts of the data.
View 6 Replies
View Related
Jan 27, 2014
I have a list of customer including customer name and customer number. Is there a way to exact customer number out only? This is the example list.
WAL-MART STORES INC - 1028
KOHL'S - 28983
JOHN'S PET PRODUCTS - 3692
PET SUPERMARKET, INC. - 2298
COSTCO WHOLESALE - INTERNATIONAL - 2783
LOBLAW CO-WESTERN GROCERS - 7424
All I want is the number behind the dash. There are few customers that have more than 2 dashes.
View 8 Replies
View Related
Feb 18, 2014
I've attached a quick sample, I have two customer ID's, AB1 and AB2, I want to be able to put a formula into column C that will return the latest date for each customer. So AB1 will show 01/04/2013 and AB2 will show 01/05/2013. I know I can use a MAX formula but I don't know how to connect it to the specific customer ID.
Max Date Query.xlsx
View 3 Replies
View Related
Apr 25, 2014
I have a workbook that I open for each job we receive. On the first sheet I call the job information sheet I hand enter the following info
Customer name in one merged cell
address in one merged cell
city and state zip in one merged cell
This info is then transferred to a few other sheets within the workbook IE invoice sheet, Labor sheet ect
I would like to created a customer list that I can use a pull down list and input this info on the job information sheet
I have attached a copy of my file for reference.
Master CoopyALL SEASONS MATERIAL & LABOR REPORT.xlsm
View 4 Replies
View Related
May 3, 2014
I want to create a customer ID for thousands of customers and then be able to find the customer by doing a search for the ID later. I'm even thinking of using a customer phone number for the ID. What function can I use to later find the customer?
View 10 Replies
View Related
Nov 4, 2009
Please see the attached example.
I am working with Office 97 and I know that if I had Office 2007 this question could be easily answered with the "SUMIFS" function. But since Office 97 doesn't have that formula I need to find another way to accomplish what i am trying to do.
In the gray boxes I need a formula that will sum the amounts for each customer for each month. For example, the total amount that shipped to customer A in October is 180.4.
I will be applying this to a much larger amount of data, so please do not provide a formula that only works for the data supplied in this example. I will be continuously adding more info to my list over time and I want the count to automaticaly include the data when I enter it.
View 13 Replies
View Related
May 14, 2013
I am looking for a formula to capture the "date" of my first sale to new customers.
A B C D E F G H Date of first sale
1 Co. Name 2/2/2012 6/5/2012 7/6/2012 10/16/2012 1/22/2013 4/17/2013
2 ABC Co. $10 $11 $5 $12 $12 2/2/2012
3 Smith Co. $19 1/22/2013
4 Brown Co. $11 $15 6/5/2012
5 AAA Co. $5 $10 7/6/2012
View 2 Replies
View Related
Nov 8, 2008
i use excel in my dispatch office. i am working on a new workbook and could use some help. In one sheet of the workbook we enter in customer calls. i would like to keep customer records in another sheet, which would include their address, phone numbers, and past invoice numbers all on the same row of the customer database sheet. At the top of the sheet that we enter caller information i would have a table set up to automatically fill in the customer info based on the name typed in,
if the customer exists in the customer database sheet of the same workbook the table will automatically fill in their address, phone number and past invoice numbers. i was thinking something like this =IF(COUNTIF('Customer Database'!A:A,Blanks!D6)>0,'Customer Database'!A2,"Not Found") ....the problem is that no matter who's name i enter, it will always add the information from row 2 of the customer database sheet. i need it to take information from the corresponding row(if customer name is found on row 45 then my formula needs to enter the info from row 45, not row 2. not sure how to do this.
View 5 Replies
View Related
Jan 9, 2009
I'm still having trouble with this formula. I have a sheet, sample attached, that has a list of transactions, with customer name, date, cash-in & cash-out information that is listed down a set of rows. I'll have a tab for each year & the information will be seperated by months.
My other sheet is a monthly report that I will be printing. I have a seperate "section" for each customer & I want excel to fill the information from that customer based on the month. I tried using a index match formula & a vlookup formula in a helper column, but it doesn't bring out the info that I want.
View 6 Replies
View Related
May 28, 2013
I have to create about 100 sheets based off of a customer number in column a. The customer number in column a is something like 000000001905. I want the name of the sheet for that customer to be 019.05. Here is my code which is returning the sheet name as 01905.00
Code:
Sub createWorksheets()
nRow As Long
nRow = 7
Do Until nRow = 108
Sheets("Blank Customer Statement").Copy after:=Sheets("Table of Contents")
Sheets(2).Name = Format(Right(Sheets("Table of Contents").Range("A" & nRow).Value, 5), "000.00")
nRow = nRow + 1
Loop
End Sub
View 3 Replies
View Related
Jun 9, 2014
I have a spreadsheet that contains over 3000 customers. On this there is much data...but what I am interested in is purchase date. The problem I am having is that customers have purchased multiple times and have multiple purchase dates. I was wondering if there was a way to get rid of all but the most recent purchase dates for each customer...thus having only one line per customer as opposed to 5 lines per customer?
In other words: Jon purchased on 1/2/14; 2/4/14 & 5/8/14
Bill purchased on 2/17/14; 3/19/14 & 4/21/14
Tom purchased on 4/2/14; 5/1/14 & 6/5/14
I need to get rid of the oldest dates and keep one line item by most recent purchase date for each customer...so instead of having 300o line spreadsheet I will only have 1000 lines.
View 8 Replies
View Related
Dec 14, 2007
I have the following formula copied down from A3 to A200 in a spreadsheet, which creates a unique standardized customer number for the each names listed in B3 to B200:
=IF(B3="","","S"&LEFT(B3,1)&"-"&TEXT(SUMPRODUCT(--(LEFT($B$3:B3,1)=LEFT(B3,1)))*10,"0000"))
Therefore, with the following names listed in B3 to B10:
UPS
FedEx
Fisher Price
Bell Canada
Grand & Toy
Rogers Cable
Dominion
Blue Jays
The respective customer numbers would be:
SU-0010
SF-0010
SF-0020
SB-0010
SG-0010
SR-0010
SD-0010
SB-0020
I would like these numbers to be static, so I would appreciate it very much if someone can give me a VBA code to replace the formula.
View 9 Replies
View Related
Jul 21, 2009
I am trying to write a fromula that will identify the customer and the discounted amount for that customer. The problem that I am having is I have more customer than what I am allowed to put into my formula.
here are the formulas that I am using, I am not sure how to make this work for more customers.
=IF(A2="","",(IF(A2="Customer 1","Discounted Price:",(IF(A2="Customer 2","Discounted Price:",(IF(A2="customer 3","Discounted Price:",(IF(A2="customer 4","Discounted Price:",(IF(A2="customer 5","Discounted Price:",(IF(A2="customer 6","Discounted Price:",(IF(A2="customer 7",c1,"")))))))))))))))
=IF(A2="","",(IF(A2="customer 1",100-C1*F2,(IF(A2="customer 2",100-C1*F3,(IF(A2="customer 3",100-C1*F4,(IF(A2="customer 4",100-C1*F5,(IF(A2="customer 5",100-C1*F6,(IF(A2="customer 6",100-C1*F7,(IF(A2="customer 7",100-C1*F8,"")))))))))))))))
I have tried to put in the excel cell to show the example, but I can't seem to figure out how to add it to this.
View 9 Replies
View Related
Jul 3, 2009
I have created a simple order form to take customer orders . I would like to summarize the orders on a summary page .(The order Form will be very long so i don't want to print the entire document every time .
I have started to create a summary page using information from a similar post on this forum. I am having trouble creating a summary when the products on my order form have multiple sizes rather than just one
View 9 Replies
View Related
Jun 27, 2006
I am trying to automatically show the date of the last invoice per the customer number.
I have attached an example.
View 9 Replies
View Related
Dec 14, 2007
I have 3 sheets (Master,Detail1,Details2) in my workbook. In the master sheet I w'll have customer names. In the detail1 & 2 sheet2 I will have their details.
When I click on the customer name from my Master sheet it will go to corresponding sheet. I have provided link for that and it's working fine.
But my requirement is when I click the customer from Master sheet, I want to get into the detiled sheet as well as want to display the records only related to the custmor I clicked/selected in my master sheet.
View 2 Replies
View Related
Jul 1, 2008
I am trying to develop a procedure that allows the user select a customer from a drop down list (using Data validation), thereby providing the user with a summary of customer information. That I can do. What I would like to do is allow the user to add new information in the summary section which is then appended to the customer record further down the sheet (see attached example). New data could only be added to the last two columns. The user would be required to press a form button to append the data.
The catch would be if the customer was selected again, the new data would also display in the summary section.
View 4 Replies
View Related
Jul 27, 2014
Look at the image I've attached.
I'm trying to have the table on the right match the first two columns to display the customer name taken from the table on the right.
testtable.jpg
View 13 Replies
View Related
Feb 8, 2014
I have two different spreadsheets with customer leads on them. I want to ensure that I don't duplicate my effort by contacting the same customers again, so I want to remove duplicates, but not in the usual way. If I simply use the remove duplicates function I will be left with a combined list of customers, some of which I have contacted and some of which I will not have contacted. I want to be able to search the second spreadsheet so that it removes the information that also exists in the first spreadsheet without combining the two so that I am left with a list of only customers I have not contacted already. I understand I may have to copy and paste, but I need a way to ensure the two lists are able to be separated once the duplicates are gone.
Someone said something to me about an "IF" function
View 1 Replies
View Related
Feb 18, 2014
I'm looking to count the number of customer accounts in a report based on the branch number. I generally need to know, how many accounts has been opened per each branch without double counting the same account number (as it happens that the report may have duplicate number.
finally I need to know the sub total showing the total per branch and subtotal of all branches together.
View 14 Replies
View Related
Mar 7, 2014
Would like to enter a formula in column B of Table 1 (on attached file) that would identify if that customer spent $90 or more (on any one purchaese) for any of their 4-5 purchases from Table 2. Expected results have been entered in Column B of Table 1.
View 3 Replies
View Related
Mar 14, 2014
I have a very detailed spreadsheet with drop down lists. I would like to block other cells if previous cells have certain selections. I have 7 columns of data that get inputted throughout the month. These cells have drop down selections that I have created. Is there any way to create a drop down menu AND another data validation? So If a selection is made, then the next cells will be blocked, using data validation "custom"?
View 2 Replies
View Related