Look Up Customer Information Based On Dates
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
ADVERTISEMENT
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
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
Sep 29, 2008
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.
View 5 Replies
View Related
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
Jul 27, 2007
I have 2 spreadsheets. One is a promotion calendar that lists the dates that promotions on a certain product runs. The other is a shipment grid of shipments of that product to the customer.
I want VLookup to find the customer and the dates and then bring me back an asterisk in a separate column to show me that that certain week that product was delivered was a promotion week. The problem I have is using Vlookup to lookup 2 things at once (and if they match to the promotion calendar) and return me an asterisk.
Here is my formula now:
=IF(VLOOKUP(J2&" "&L2,'East Data'!M:AU,2,FALSE),"*",0)
J2 is the customer name
L2 is the week
"East Data" is the spreadsheet with all of the promotions and customers.
View 9 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
Apr 22, 2006
I need to dispatch 10 technicians across the US starting each Monday. They go to 10 different customers for the whole week. I know the distance between each technician and each customer. What I would like to do is assign each technician to a customer so that the total distance traveled for all the technicians is minimized. I know that if it was only 3 techs and 3 customers (as an example), I could build a table of all the possible combinations (3 factorial or 6 permutations), determine the distance for each permutation and select the minimum. I'm not sure that doing this method with 10 techs is feasible as the number of permutations is 10 factorial or over 3 million possibilities. Anyone see a workable solution?
I've include a spreadsheet with customers, technicians and the distances between them!
View 9 Replies
View Related
Dec 11, 2012
We've got a bug in our finance system where it can't handle any transactions that have sales but no related commission. The BI team provides a CSV file separately with this information and the sales team has to manually input it. I know how to create a template that can be uploaded into the system but don't know how to pull the data into the template from the CSV file.
I've created the attached example and what i'd like is a drop down box in cell B1 (template tab) listing all the customer codes in column B on the data tab and then based on your selection all the related transaction lines pull into columns A to F (starting on row 4).
Manual Invoicing Query.xlsx
View 1 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
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
Jul 21, 2009
I have 2 Tabs.
1) Furnaces --> INPUT
2) Report --> OUTPUT
1) I have this speadsheet and every day i entry new information:
Date Furnace# Burner
6/1/2009F-11
6/1/2009F-513
6/1/2009F-512
6/2/2009F-16
6/3/2009F-15
6/4/2009F-14
6/4/2009F-514
6/5/2009F-511
6/6/2009F-12
6/7/2009F-11
2) I want in this tab to get information from the other tab (Furnaces) in order to have an every day control.
Furnace #Burner 6/1/2009 6/2/2009 6/3/2009 6/4/2009.. etc
F-1 1
2
3
4.............................
View 2 Replies
View Related
Nov 22, 2011
I am working on a project at work. I need to track our VIP guests. I only have their arrival and departure date.
Is there a way to graph and track the days between so that I can have a total of all arriving and In-house VIP guests on all dates in the range?
Here's a sample of the data.
10/18/201110/19/2011HOERMAN/SCOTT10/17/201110/21/2011SMITH/JOHN M
10/10/201110/13/2011WARNER/AMY10/18/201110/19/2011GERONDALE/RON
10/11/201110/12/2011BUCHANAN/JEFFREY10/17/201110/18/2011HUND/LARRY
10/20/201110/23/2011HADDOCK/CHRISTOPHER10/9/201110/12/2011
BLOOMER/ROBERT10/20/201110/23/2011COLE/ELIZABETH10/7/201110/8/2011IWASA/KENNETH
View 6 Replies
View Related
Aug 21, 2009
Basically I have two groups of data
Group 1 is machine info and has
Serial Number Build Month Commission Month
Group 2 is warranty info and has
Serial Number Service Claim Value of Service Claim
What I need to do is for all machines built in a given month count the number of claims within a given number of months after commissioning and the total value of the claims.
Eg
If I have a machine serial number abc123 built in jan 07 and commissioned mar 07 I need to search the table of data in group 2 for the number of claims between mar 07 and may 07 and also the total value of those claims
View 6 Replies
View Related
Jan 20, 2010
I have an employee summary sheet, and another sheet where data will be entered by date.
On the summary sheet I want to gather only the last 3 dates that were entered.
Attached is an example.
View 10 Replies
View Related
May 18, 2006
I would like to calculate the date but depends on how the information is scattered. In the first coloumn I have a set of dates, in the second coloumn I have a list of letters. I would like a macro that reads the the letters, and as they disappear calculate the time interval. For instance,
01/01/2002 B,C
02/01/2002 A,B,C
03/01/2002 C
So it firsts starts off and sees that the letters are B and C are there to begin with, then it moves on to the second row and then sees that the letter A has been added. Then to the third row and sees that there is only C so A lasted for 1 day (was there on 02/01/2002 and disappeared on 03/02/2002) and B lasted for 2 days (was there on 01/01/2002 and disappeared on 03/01/2002).
View 6 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
Jan 27, 2014
Imagine I have 2 columns of information that look like this:
Column A
Column B
AS
Dog
AS
Cat
AS
Hamster
FT
Fish
These are my key columns. The letters are initials of people and the animals are the pets they're responsible for.
Now, I have 3 more columns that look like this:
Column D
Column E
Column F
These columns can go on for hundreds of rows.
What I want to do is pull out the information from columns D, E and F where the initials and pet match those in the key list, then paste that elsewhere (say to columns J, K and L).
So, for instance, the first entry would be copied across because, according to the key list, AS is responsible for a Dog, but the bottom entry for AS wouldn't because he was looking after a fish, and that pet isn't listed as one of his animals in the key list (Fish is listed alongside FT). Likewise, the entry for AH wouldn't come across because AH doesn't appear on the key list at all.
View 1 Replies
View Related
Dec 31, 2006
I have a very large Excel spreadsheet that is generated through an Oracle application at least once a month at work. I would like some code (either a worksheet function or a VBA) to check and report the following. If the frequency in column C is "Monthly" or "Weekly" just go to the next row. If the frequency in column C is "Annual", add 183 days to the date in column B; if it is "Semi-Annual", add 92 days to date in column B; if it is "Quarterly" , add 46 days to the date in column B; if the frequency in column C is "2-Year", add 365 days to the date in column B. Once the check has been made; I need the new total or date that was calculated checked against the date the report is being run (system clock date). If the date or total days is less than today, a new worksheet, titled "Late" needs to contain that row of data. If the date or total days is greater than today's date, just go on to the next row. I've attached an example worksheet.
View 9 Replies
View Related
Apr 29, 2014
I have been struggling hard to pull information out of a table using vlookup and match & Index functions. It does not seem to be working. Below is my criteria:
I have a data of call centre agents with names , dates and calls offered answered details. On another sheet i have given a list of names of agents in a drop down list and all the dates of the week. Now i want that when a user selects a particular agent name from the drop down list, the data fetched under '07042014' date column is against the number of calls answered by the selected agent on this date. I have 5 columns of title '07042014' , '08042014', '09042014', '10042014' and '11042014' Capture.PNG. Attach is the file how it looks like.
How to use match and index function or any other function to fetch information against this date as per the agent selected.
View 3 Replies
View Related
Oct 8, 2007
If you look at the attached file you will understand what I am trying to get at more. I have a download which comes from a time management system. What I want to do is have a macro which runs everytime the name dropdown box on the summary page is changed. The macro needs to lookup the correct times for that person and the day and place them in the correct order (i.e 1st clock in, 1st clock out, 2nd clock in, 2nd clock out, etc.) I am trying to find the best way to do this.
View 3 Replies
View Related
Dec 3, 2013
I have a problem with excel. I attached the excel sheet to show the question.
excel problem.xlsx
View 2 Replies
View Related
Jan 9, 2014
Based on the photo below i am looking to sum and multiple totals based on information presented.
I am looking to have the Total items calculated based on either the size run value(which in this case is 111, or 121, or 123,or 222 etc, all based on clothing sizes 1-s, 1-m,1-l) or calculated based on the Total from the sum of all the sizes (xs,s,m,l) which in this case is 9. and if that was not enough, then have the Total Items multiple based on the No of Packs, which in this case is 6.
In this example i have managed to get the formula to multiple 1 size run (111 = 3) multiplied by the number of packs (6) to get 18, (Size Run, is a drop down list from a reference page
I guess i need to know if there is an AND or OR function that can be combined with the SUM or SUMIF function.
Excel formula.jpg
View 2 Replies
View Related
Mar 1, 2014
I have found a list of newspaper in an excel file and at the end of the text it is written the name of the language e.g Bangladesh News 24 (Dhaka) [In Bangla], The Bangladesh Monitor [In English]. I would like to distinguish the names ended with [In Bangla] and in [In English]. How can i distinguish this two different type of data with VBA?
View 1 Replies
View Related
May 21, 2014
I'm wondering how I could display information based on the user input through dropdown selection. I know how to make a dropdown-list already.
Example: A1 has a dropdownlist with Apple and Banana in it. If I select banana then I want B1 to display "8" and if I select Banana I want B1 to display "4".
View 5 Replies
View Related
Mar 23, 2009
I want to populate separate tabs from data in a master table based upon multiple conditions shown in the separate tabs. Each tab is a summary of a particular engineer's shifts over multiple weeks and their results on various projects. Each summary tab has the engineer's name and the number of the week (the two conditions). Example: On Aaron's tab, I want to show a summary of Aaron's work and his results over a period of several months, as well as who is working the other shift on that day for those projects. So I need to get the following information for Aaron's tab from the master table:
Week 1, Aaron is working the morning shift and Zeb is working the afternoon shift,
Week 2, Bill is working the morning shift and Aaron is working the afternoon shift
Week 3, Aaron is working the morning shift and John is working the afternoon shift
etc etc
I also need to know how much work each engineer pairing has achieved in a particular week on a particular project. The engineer working in each shift may vary from week to week, as will their results. There is no formula for when a particular engineer will be working either morning or afternoon or whom they will be working with.
View 5 Replies
View Related
Nov 3, 2011
I would like to filter my list to show rows with a date difference of >0 (raised date later than date of contact) and move certain cells onto another sheet in the same workbook.
e.g if diff in F1:F1000 is >0, populate the following onto this sheet
Job no - Address - Postcode - Name - Date raised
Would this be a case of using a vlookup? having a few problems with it at the moment
View 9 Replies
View Related
Mar 12, 2012
I have a Sheet1 that contains 900 rows of information 14 columns wide (C7:P7 are headers). Column M has an important date. The information is constantly changing.
On Sheet2, I want to display the upcoming dates, in numerical order for each row that's within 5 days of today's date. After the date has passed, it no longer needs to display on Sheet2.
View 1 Replies
View Related
Feb 7, 2013
I have one workbook where someone enters information on a daily basis and id like to sum this in another workbook weekly.
The daily sheet "sheet1" has headings in row 1 starting B1 = 11/02/2012 this is then dragged along till the end of the year. In B2 the user will enter a number so B1 - 11/02/2012 and B2 - 5
On the next "sheet2" in cell B1 i have the date 17/02/2012 and in B2 id like to sum the total from "sheet1" B2 - H2 which is in effect the range 11/02/2012 - 17/02/2012 then id like to be able to drag this formula along so it automatically sums the weekly totals based on the first sheet
View 3 Replies
View Related
Mar 11, 2013
I am looking for a macro to rename tabs based on information in A1 on each tab. The thing that is causing me issues is the cell A1 has a formula in it and it changes on a daily basis with days of the week but the tab only updates when i double click A1 and press enter.
I need it to update automatically.
View 4 Replies
View Related
