Create Formula That Will Determine Number Of Orders For Specific Customer?
Aug 29, 2012
I am trying to create a formula that will determine the number of orders for a specific customer whose orders have a pallet count between a certain range. The customer code is a cell reference, as well as the beginning and ending values I am looking for. I have the data on a separate tab from where I am putting the formula.
Here is what I was trying to use, but it was coming up with "0" for everything:
=COUNTIFS(Data!$I:$I,E7,Data!$I:$I,">=A8",Data!$I:$I,"
View 1 Replies
ADVERTISEMENT
Jul 31, 2009
I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month.
Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10
10/15/2009 01/13/2009
I'm using Excel 2007.
View 9 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
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
Apr 22, 2014
I am trying to count how many work orders we have created from 3 months (90 days) ago. In an effort to make the input work minimal. I wanted to use the =Today() formula and then subtract 3 months.
So I currently have =Today() in A1 and then (A1-90) in cell B1 and I get the date I want.
However, When I use a countifs formula and my criteria is
View 1 Replies
View Related
Oct 8, 2008
I’m getting data from query. My data has fields: Item No, Customer ID & Sales rep.
I need to find out for each Item number which customer put order and who was the sales rep. (Excel 2002). see file attached.
View 4 Replies
View Related
May 13, 2007
I have a worksheet that contains active rows and hidden, inactive rows. On occasion, I would like to unhide an inactive row. I intend to do this by running a macro which will generate an input box to enter the file number of the row in question. I can restore the row, but what I am unable to find a solution for is converting the file number to the row number to be restored. The file numbers reside in column A, so if I input file number 2300, I need the macro to look through column A, find the cell that contains the value 2300, and return (as a string?) the row number that contains that value. From this I can have that row returned to visible status.
View 9 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 11, 2010
I'm documenting the steps of a process with each step accounting for a specific amount of business days. How do I determine the specific business day based on the number of days it takes to complete a process? For example: Start date then 45 business days from that date the next step begins...then 3 business days from that date the following step begins and so on.
View 5 Replies
View Related
Sep 14, 2005
The following are a few of generally accepted values: ...
View 8 Replies
View Related
Jan 7, 2008
I have a simple list of all purchases made. ie)
Name.......Purchase date
John........01.01.07
Susan......06.08.07
John........07.07.07
John........01.05.07
I'd like to roll up the sames to create one customer row, but so I see the varience between purchase times. ie)
Name.......Ist Pur date....2nd pur date.....3rd pur date....time from pur 1 to 2
John........01.01.07.........01.05.07..........07.07.07........120 days
Susan......06.08.07...................................................(not sure to include this)
Is this possible in excel?
View 9 Replies
View Related
Nov 1, 2005
I have a column that contains sale # and another column that has cust names.
How could I get the highest 10 sale #s for a specific customer?
Is this a place for index / match?
View 10 Replies
View Related
Nov 22, 2006
how to create a barcode in an excel spreadsheet? I am trying to print out a sheet that includes a code 128 barcode of a specific number that I type in.
View 2 Replies
View Related
Sep 15, 2009
I am creating a tracking spreadsheet and I will need to f/u on a process 30/60/90 days out from a particular date. Example: CPAP Therapy began 1/22/09 and I need to follow-up by 2/22/09. How do I create formula to add 30/60 or 90 days out from the setup date?
View 6 Replies
View Related
Jul 6, 2008
I have an excel file which contains following data in it.
Col-A Col-B Col-C ......
Cust Cd Name Sales
=======================
101 AAA 1000
101 AAA 500
101 AAA 3000
102 BBB 800
102 BBB 200
103 CCC 200
103 CCC 200
103 CCC 200
I need to create following three workbooks with name based on Cust Cd from above excel file.
Workbook - 1 : 101.xls which contains records only pertaining to Cust Cd 101.
Workbook - 2 : 102.xls which contains records only pertaining to Cust Cd 102.
Workbook - 3 : 103.xls which contains records only pertaining to Cust Cd 103.
View 4 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
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
Dec 9, 2011
I am trying to do a vlookup to 1 sheet based on customer number and return price. If no customer number than I want to do a vlookup based on dunnage number to return the price. This is what I have for a formula but it never returns a value if one is found in the first lookup.
=IFERROR(VLOOKUP(C4,'Dunnage Exception'!A:C,3,FALSE),IFERROR(VLOOKUP('Raw Data'!D4,'Dunnage Master'!A:C,3,FALSE),"0"))
View 2 Replies
View Related
May 19, 2009
Im trying to come up with a password that changes daily that contains 2 constant numbers and some multiplication:
The formual would need to be like a string for example:
1 (the day * a number) 2 (the month * a number)
The numbers 1 and 2 are always the same.
I have a spreadsheet that has the month (number) in one cell.
The days are in columns A1 - A31
Everytime i try to do this in Excel, it trys to multiply every number.
View 10 Replies
View Related
Dec 23, 2008
I have a range ie C2:D400.
The word "ball" appears many times in this range. How do I determine the last row that "ball" appears in?
View 9 Replies
View Related
Sep 22, 2013
I am creating a custom autonumber in excel. The autonumber will be based on the value of another cell's value. So for eg, in cell A1 will look at B1, if B1 has the number 1, A1 will take B1 and add the autonumber to it, eg a, b, c.
If within, b5 the number is 2. So if the number in the corresponding row in b changes, the autonumber must restart.
I've decided to go with a-z for the autonumber as i realized for .1, .2, .3 is limited to 9 values in the list bec. 1.10 may be read as 1.1
I've tried a few options such as creating a named range, however the problem is getting the autonumber to restart.
Below is what i want to achieve via excel formula:
Column F
Column F
1.a
1
[Code] .........
View 5 Replies
View Related
Jun 18, 2008
Basically, I want a formula that searches for more than one word anywhere within a cell, and if the cell contains any of those words somewhere in it then I want to display a value, but if the words aren't there, then stay blank.
For example, my cell could say: "Magenta, Rose, Lime, Lavendar". I want to search this cell for any of the these words: "Lime", "Forest" or "Teal". If any of these appear, I then want the cell with this formula to say "Green".
I know the formula to search for only one word /phrase in a cell, I use: =(IF(ISNUMBER(SEARCH("yellow",A1)),"colour","-")), (i.e. if the word "yellow" appears anywhere in cell A1, then this cell will show the word "colour", if not then a "-".) I tried adding an 'OR' function in this to search multiple words but it didn't seem to work.
View 2 Replies
View Related
Apr 9, 2008
See attached spreadsheet. I have a set of Jobs with a set of end dates. A meeting date should be held after the end of each job. I need a formula or macro to determine when the meeting date will be held? The criteria to determine the meeting date is found in rows 12-18. The end dates are set on different days of the weeks and different weeks of the month depending on the region that the job is located in. The meeting dates will be input in colums d, f, h & j.
View 7 Replies
View Related
Dec 5, 2007
I am trying to create a formula to pull the person's name and the avg number of cartons sorted for the month from this spreadsheet.
I've attached a sample of the spreadsheet I created, you will see at the bottom I would like to see the person that had the highest avg for the month and the person with the lowest avg for the month.
This is just a simple spreadsheet to track productivity of my sortation employees.
View 9 Replies
View Related
Apr 29, 2014
I have created an online survey, and people choose ten words (skills) out of 24 possible. Please see screenshot. I would like a formula that does this in layman's terms: "If I see the word "Cooperation" in the source cell, then I'll put "03b" into the target cell; but if I see "Managing" in the source cell instead, I'll put "21a" in the target cell, etc."
I've tried a few IF / Then statements, with no success. Screenshot shows the source cell upper right, and the ten target cells below left with two rows of sample results. I'm guessing the same formula can be in each of the 10 target cells.
View 8 Replies
View Related
Apr 29, 2008
I am attempting an If Or formula and keep retuning a #Value error, I can't for the life of me figure out why.
This is the formula that I have written.
=IF(OR(H44,H45,H46,H47,H48)="Other:","True","False")
Cells H44,I44,J44 have been merged into one cell (H44), and the same for 45,46,47,and 48 - Could this affect the Formula?
View 4 Replies
View Related
Mar 13, 2014
I'm using the VBA code below for a piece of code.
[Code] .....
However, I want to use this same function in another place, without the limit set on the cells that will be cleared.
What I would like to do is send the formula the cell to start at (E14), offset that by one column, and then send a number of rows. With that, create the range to be cleared.
Something like:
[Code] .....
View 3 Replies
View Related
Feb 4, 2009
following calculation that someone created for me.
=LOOKUP(10^10,CHOOSE({1,2},0,AVERAGE(IF(Sheet1!$C2:$C13=$A2,IF(Sheet1!$B2:$B13-DAY(Sheet1!$B2:$B13)+1=B$1,Sheet1!$D2:$D13)))))
My problem is that I have some customer account numbers that are alpha/numeric (1B0012) and some that are just numeric (1675) . The above formula does not pick up the numeric accounts correctly.
Is there something that I need to add/remove from this formula so that it will pick up both? or is it a format issue?
I have attached an example of the spreadsheet with this problem. If you select either of the alpha numeric codes it works, but if you select the numeric code it doesn't.
View 9 Replies
View Related
Dec 30, 2009
I'm having difficulty creating an array formula. In a multi-column sheet, I am looking at a column with classes and a column with a date (in the format 7-Oct-09). I need to list the number of a specific class for a particular month (any day). I have tried the following which gives only the number of classes:
=COUNTIF(A4:A2500,"AA")+COUNTIF(H4:H2500,"10/??/09") and
=SUM((A4:A2500="AA")*(H4:H2500="??-Oct-??")) which gives me 0. Maybe an array formula is not the way to do this.
View 9 Replies
View Related
Sep 7, 2009
Given a pivot table, is there any way that I can determine the source data that was used to create the pivot table?
I suspect that the pivot table was created using only part of the data, but I'm not sure.
View 9 Replies
View Related
