How To Pull Transactions Into Template Based On Customer Code
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
ADVERTISEMENT
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
Oct 2, 2013
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.
View 3 Replies
View Related
Aug 14, 2014
I am after a VBA code that I can use to populate a fax template based on criteria's
Basically I have records which are being logged throughout the day via a User form and sometimes there may/maynot be a charge. the user decides at the time. The column is named "to be charged" and is filled with either Yes/ No.
I need to be able to select the date to print or Just the same day date and print all faxes' that have "Yes" for charge This way all the information for each fax is populated and the user can just print.
and if possible mark a Colum non the master sheet as printed with a X or something
View 14 Replies
View Related
Feb 20, 2014
I want to SUM monetary transactions based on two criteria.
1.) if the transaction occurred within a certain month (Jan)
2.) based on the transactions category ("Obligations")
I have two formulas which successfully validate the data individually but I need to combine them so that both criteria must be met before data is summed.
=SUMIF(REGISTER!E3:E1000,"Obligations",REGISTER!G3:G1000)
=SUM((IF(MONTH(REGISTER!$B$3:$B$1000)=1,REGISTER!$G$3:$G$1000,0)))
View 9 Replies
View Related
Jun 28, 2009
I have this spreadsheet that has over 20,000 rows. I was asked to build a search page to will bring back all transactions based on a primary key (account number). Here is a sample:
Account NumberDateComments2343566/2/2009 $ 111.43 3453465/1/2009 $ 89.34 5676552/5/2008 $ 643.23 8078989/3/2008 $1,245.34 12543612/5/2008 $ 56.65 2343562/2/2009 $ 343.54 3482459/9/2008 $ 78.76 9345641/2/2009 $ 356.22 2343565/3/2008 $ 529.66
The idea is to enter an account number like 234356 click a button and bring back:
Account NumberDateComments2343566/2/2009 $ 111.43 2343562/2/2009 $ 343.54 2343565/3/2008 $ 529.66
I got the button part done and using vlookup it brings back the first line. The problem is that it won't bring back all the rows just the first one.
View 9 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
Dec 12, 2009
i have one excel sheet (refer attach) i give some example 7 to 8 customers, but in sheet nos. Of customer name a to z suppose to 3000 customer list. Now i want to create automatic customer alfa- numaric code.
View 2 Replies
View Related
Feb 2, 2012
I currently have a number of data sets relating to customer's and values, eg.
Customer 1 12 12 12 12 12
However within this data set I also have a number of items where different customers have been grouped but are separated with a “/”, eg.
Customer 1 / Customer 2 / Customer 3 12 12 12 12 12
I want to be able to, find these rows, and break them out so that each customer with relating data is on a separate row, eg.
Customer 1 4 4 4 4 4
Customer 2 4 4 4 4 4
Customer 3 4 4 4 4 4
View 7 Replies
View Related
Feb 1, 2007
I have a master worksheet that contains a lot of datas. Example below:
Invoice # Code Customer Money
1 1101 a 10
2 1102 a 20
3 1102 b 30
4 1103 c 40
5 1104 d 50
6 1101 a 60
7 1103 c 70
After creating a pivot table with code, customer as Row and money as Data, I get this:
Sum of money
Code Customer Total
1101 a 70
a Total 70
1102 a 20
a Total 20
b 30
b Total 30
1103 c 110
c Total 110
1104 d 50
d Total 50
Grand Total 150
Suppose now, I have another data named Rate that I need to associate with each pair of Code and Customer. See below:
Sum of money
Code Customer Total Rate..................
View 2 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
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
Feb 14, 2014
I am trying to create a beforesave code within my template. The template has certain fields that are blank and when the end user enters data, I want them to be able to click on save or the blue disk and it saves it to a new location, based on their user and some of the cells within the form. I do not want the template to be saved with the data they input. The data they input is saved only when they click the blue disk and it saves to another location, with their username and certain cells in the form. Then the end user can continue using that same template for the next entry and so on. I have a beforeclose that asks them if they really want to close or not. The only time they would close, or hit the big red X is when they are done with all their entries for the day. Here is my code for beforesave:
[Code] ......
View 10 Replies
View Related
Feb 17, 2008
I have some code and then
ChDir "W:MET Logbook"
ActiveWorkbook.SaveAs Filename:= _
"Micro Evaluation " _
& Format(Date, "(mm-dd) ") & Format(Time, "hh.mm.ss AM/PM") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
And I want this to save my workbook in the different location as a template. Only it saves the workbook as a regular excel workbook instead. Any help with what I can change to save it as a template?
View 9 Replies
View Related
Aug 8, 2006
I have a census file. All records have a TK number assigned to them. I need to create a new workbook (for each TK number) from a template file and fill in the file with data from all the records belonging to that TK number. I can write this out in English, but I don't know what type of functions/searches/etc. to use to make this work most efficiently.
View 9 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
Apr 21, 2007
Does VBA code created in an Excel file that is saved as a Template (.xlt) work the same when the file is opened as an .xls file?
View 9 Replies
View Related
Mar 21, 2008
I am trying to use the worksheets.add method with the Type specifier pointing to a file location containing a worksheet template.
View 9 Replies
View Related
Aug 8, 2014
I use my workbook to track sales data from one store to the next. I use my workbooks to compare data from year to year. Each year's data is displayed on a separate worksheet. '2013' has 2013's data, '2014' has 2014's. On the 2014 sheet, I have a Prior Year's Sales that pulls data from the previous year's spreadsheet using a formula which I just drag down each day I enter sales. I would like to automate this process and have the VBA code check for today's date and automatically pull the previous year's sales data from the '2013' sheet and put it in the appropriate cell on the '2014' worksheet. I hope I have explained this well enough to understand. I've included a link to my workbook for reference.
I had to use dropbox since I can't post a file over 1 MB. The file size is around 1.25 MB.
View 5 Replies
View Related
Jan 22, 2010
Im looking to create an invoice-type format from data in a row but only when the client requests it.
So, if there is a Y in column R. i.e. the client wants an invoice format then.
I have been given a template format by the team manager that I have to use. Its far from ideal, well for me anyway, for moving things around and Im struggling to get anywhere with it.
Im trying to take the data from the row in the source / client worksheet, which will be created via a code from here, and transpose it to a copy of the template worksheet which sits within the same workbook. This is made more complicated because in some cases the data for the invoice will be in two or more rows, because there will be a few items on the same invoice, but they will still be on the same worksheet.
The source / client worksheet has data from columns A to T with a header in row 1.
The details will need to be transposed from the relevant row from each column to the template worksheet as follows
From column in source / client worksheet To cell in copy template worksheet.
From - To
CB2
AB3
BB5
HB7
IB9
GB11
EB13
FB15
RB19
SB21
QB23
OB25
PB27
This is where it gets messy, or more messy should I say
MB33
JB35
LB37
KB39
TB41
NB45
For each of the six sections M to N above there may be more than one relevant items which will be on rows 3 onwards of the source /client worksheet.
So, ideally Im guessing based on if there is data in column A of the source / client worksheet then M to N above will need to copied downwards, i.e. below itself on the template worksheet say from column A and B rows 33-45 copied to A and B rows 47-59 until all the data is copied over.
Blinking eck ... this is a nightmare a real nightmare. This more than one section above is really bad Im at a complete loss.
Although if its really not possible they may have to have another template worksheet with the second, third etc items on as I just cant see this working.
View 9 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
Jun 24, 2009
I have this macro below and would like the code changed to find the "Customer Number" column by name rather than by column B. Note that the "Customer Number" column will always be somewhere in row 1.
View 12 Replies
View Related
Feb 21, 2013
how to create a copy of my employee attendance template. Is there a way to create a copy of the template by entering an employee name in the "name" cell of the template and it automatically renames the sheet that employee name and also saves or recopies the template for use with the next employee?
View 8 Replies
View Related
Feb 9, 2014
I have a master template with columns running from A to Q. Most of what she wanted is fairly simple 'if' statements, however she also wants to move the contents of one cell to an adjacent sell based on the number exceeding a certain value,
[Code].....
My problems start when I want to create a new sheet/tab and let her name it. I would have simply created 12 months for her, unfortunately there is a sheet for a number of people and the start of the year can vary. My simple solution was to create a macro that copies the original Template (effectively sheet 1) and puts it into the newly created tab. However this does not copy the above VBA code across
[Code] .....
View 1 Replies
View Related
Feb 18, 2010
I am trying to create a workbook in which each person gets a seperate sheet.There are common cells in each sheet like name addrss,phone number, etc (about 10 such cells) and there are other cells which will be unique to each person like hyperlinks to letters I have sent to that particular person.
The tab will be named using that person's surname. (I use "find" on the surname and i can locate the particular worksheet).I can add the unique ones as and when needed in each particular sheet, but what i would like to do is to create the first sheet containing all the necessary common cells with a blank cell next to it and then (maybe run a macro?) to create multiple sheets all containing the same info so that with each sheet I only have to fill in the blanks (name,address,age,etc) .
At the moment I am using "copy sheet" each time using an unfilled template sheet and it is a pain.
View 9 Replies
View Related
Aug 18, 2006
If you have a workbook or a template open you can find out the full path of the file from the .FullPath property. My problem is that when a user double-clicks my .xlt file (to create a new workbook based on it), I want the code in the Workbook_Open event to be able to tell the full path of the template it was created from (because the code will later go on to save the new workbook under a date related filename in the same directory that the template was located in). I haven't been able to find a property that gives me any clue as to the location of the template this new workbook is based on.
View 5 Replies
View Related
Jan 5, 2009
I Have a number(30+) of excel files based off of one template. I have since updated the template, thus making the the old ones obsolete. Is there a way to update the older files to use the new template without having to copy and paste the addition's row by row column by column?
None of the additions to the template will change the placement of existing data, nor will it change the data itself. It will however add Values to cells that were previously empty.
Hints, Tricks, or Ideas?
View 9 Replies
View Related
Feb 16, 2010
I have the job of producing a survey in Excel and collating the results anonymously, to help with this I'm looking for a chunk of code that will enable me to pull a specified range of data from all the files in a particular drive with a filename starting with a specified word and insert them all in to the same blank sheet.
View 6 Replies
View Related
Apr 5, 2007
I just started playing with creating my own macros. I've written a macro to pull all the names of the worksheets in a workbook, now what I want to do is write a piece of code that will pull data from a cell in those worksheets.
So for instance I have all my worksheet names in cell A1 to A10, I want to pull the data from cell E1 from every worksheet in A1 to A10. Can someone help me with a sample code to work with. I'm still reading Power Progamming haven't gotten so far yet.
View 9 Replies
View Related
