Create A Loop: Find Out For Each Item Number Which Customer Put Order
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.
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?
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:
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
I have a Purchase Order template that I created in Excel and an Excel Customer spreadsheet. I am trying to get the Customer Name, Address, City/State/Zip (all found in different columns on the customer spreadsheet) to autopopulate on the Purchase Order when the matching phone number is entered. New to advanced formulas but looking for something like: if the phone number on the purchase order equals the phone number on the customer database, bring over the Customer info above to the appropriate cells on the purchase order.
I have a spreadsheet with data of customer orders. I am looking for a way to pull the customers who have placed an order between January and August, but did NOT place an order in September (in order to reach out to those customers who have stopped ordering).
I can't post attachments for some reason, so this is a description of how the sheet looks.
Column: A - Delivery Date (MM/DD/YYYY) B - Month (MM) --> formula created to pull month from column A. For example 2/12/2013 returns "2" C - First Name D - Last Name
I need to find a way to create a list of customers, without duplicates, who placed an order previous to Sept, but not in Sept. So, pull unique names from column C and D that have an entry in column B of 1-8, but NOT 9.
I am trying to create a spreadsheet that will automatically increase the work order number by 1. The cell will always be in the same place. The idea is that when the file is opened it populates the number. After being closed and reopened the number will be 1 higher than the previous.
The page will reused by various people to create and print work requests for my mechanics, I want it to assign the W/O automatically.
I have two workbooks, (Invoice.xls) and (Inventory.xls) both using sheet1. When I fill out an invoice, with lets say 10 items on sheet1 column B, I need a macro to find those items in workbook "Inventory" sheet1 column A. When the item is found, I need the qty I entered on the invoice workbook "Invoice" sheet1 column C (same row as the item number) to be subtracted from the current total for that item in workbook "Inventory" sheet1 column C. I would like to run the code from the workbook "Invoice" before I do a "Save As". I have found other Vb code on this site that seems very close to what I need, but I just don't know enough to make the changes necessary.
I am using a vlookup and have a problem. I am assigning a category to an item number based on the first two characters of the item number. For example item number 60123 would equal scrap because of the first two characters of 60. But the item number can begin with either a number or letter. Here is the formula I am using that works for item numbers that begin with numbers:
It works fine until I reach a item number that begins with a letter, then I get the dreaded #Value error. If I take the value out of the formula then it works for the letter based number items but not for the number based item numbers.
I am looping through each cell in a range and I would like to loop in reverse order.
Dim CELL As range Dim TotalRows As Long TotalRows = Cells(Rows.Count, 1).End(xlUp).Row For Each CELL In Range("C1", "C" & TotalRows) CELL.Select 'Code here to delete a row based on criteria Next
I have tried:
For Each CELL In Range("C" & TotalRows, "C1")
and it does not make a difference. I need to loop in reverse order since what I am doing in the loop is deleting a row. I am looking at a cell and determining its value. If the value is so much, then the row gets deleted. The problem is that the next row "moves up" one row (taking the pervious cell's address) and therefore the For Each Next loop thinks it has already looked at that row.
I have created a excel workbook in which I use for keeping up with storage unit rent. I have a tenant list sheet and each tenant has a sheet on which I keep up with charges and credits. What I want is a macro the will loop thru each tenants sheet based on tenant list and insert data and go to the next sheet. I found this on the web:
I am using Vlookup to compare our item number with the suppliers item number but the result is removing the decimal point on some but not all items.I have included a small sample but it concerns almost 10.000 items.
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)
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.
I have a spreadsheet of our Customers rankings with a column with the date that they sent their Report Cards in. I need to pull by Cust# the most current date. I tried using DMAX but I can not get the formula to work. The sheet is not sorted and can not be sorted by Cust#.
range field criteria
Title row starts at 6
A B C DCust #Marketing LocationCustomerReport dated1034ColmarScientific Systems11-Nov-021032ColmarRaymond4-Feb-031029ColmarPhiladelphia Gear6-Feb-031028ColmarPall4-Apr-031016ColmarGreene Tweed 22-Apr-031032ColmarRaymond6-May-031032ColmarRaymond15-Apr-041012ColmarATK Launch Systems, Inc.27-Apr-041032ColmarRaymond22-Oct-04
I've modified some code to perform a loop which creates a new worksheet for each item in a pivot table Page By field (EmpName), copying and pasting values for the displayed pivot table with each name selected. Everything is working fine until it gets done with the last item in the list, at which point I get a Run Time Error 1004, "Unable to Get Properties of the Worksheet Class".
I want the MsgBox to pop up at the end, stating how many sheets have been added.
Sub Loop_PivotItems() 'Turn off screen updating Application.ScreenUpdating = False 'Store the sheet with the Pivot Table Piv_Sht = ActiveSheet.Name 'Loop through every PivotItem in the PageField (Filter) of the Pivot Table
I would like to perform an operation on each filtered range. To do this I need some kind of loop which would enable each item in the auto filler. I dont know how to do this. The loop i need is to enable one item, do operation (ex sum of visible rows --> i already have a code for that) then disable that currently visible range and enable next one, perform sum operation again.
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.
I need to create a macro that can create a dynamic copy/paste loop. So far what I have is horribly inefficient. Each row in colmn A(minus the header) has a unique number in it. For each unique number, I need to paste it based on the number of column headers in row 1(minus column A). So, if there are 20 column headers, I need to copy cell A2 and paste it 19 times in another sheet. Then, I need to move to the next number in column A and do the same thing. Here's what I have:
You can see that this is not dynamic. If I add another row to my table and rerun the macro, it will not catch it. I've attached a sample file to show you the big picture of what I'm trying to do. The data that I have is in Sheet1, and I'm trying to get it into the format in Sheet3. Rows/columns will be periodically added to the table in Sheet1, so the macro needs to be dynamic to catch that. The data in Sheet3 will always remain, and the macro will add the updated data below the old data in Sheet3.
I am using stuff from loops http://www.ozgrid.com/VBA/control-loop.htm to loop through all the controls on a UserForm. But I can't find a way of controlling the order in which this is done. I tried both TabIndex and moving the controls around without success. Is there a property of a control that controls the loop order.