Macro Produce Result In Sheet
Jun 20, 2009
I have the following data which is download as csv, i need a macro to produce result in sheet2, notice if there are duplicate stmt or ledg for the same case No then the units need to be consolidated as shown in sheet2
Outstanding Holdings Case ReportAs At Thursday 4 June, 2009 16:15AccountSourceCase No.Ledg StmtValue DateMessage FeedSecurity CodeDescriptionCcy CodeUnitsExceptionUnsettle UnitsCase TypeKnown SecurityMatch NoCase AgeAge BreakAgent Code:DERIVHOLD ABERDEENJPMC.AEIABIDERIV562272Ledg4-May-09JPMC.AEIABIXT0906AUS 10YR 0309 BOND FAUD-440Hold Singleton CaseYes305779830LATEST CASE NOTES:5-May-09FuturesJPMC.AEIABIDERIV569967Ledg1-Jun-09JPMC.AEIABIYT0906AUS 3YR 0609 BOND FUAUD7-70Hold Singleton CaseYes2599402LATEST CASE NOTES:2-Jun-09FuturesItem Count : 2JPMC.AEIABIFA1569598Ledg29-May-09JPMC.AEIABIQTC0813QUEENSLAND TREASURY CORP 140813 6.00 GBAUD150000015000000Hold Net DifferenceYes3598143JPMC.AEIABIFA1569598Stmt29-May-09305496516891QUEENSLAND TREASURY CORP 6+ BDS 14/ AUG/2013..........
View 9 Replies
ADVERTISEMENT
Feb 20, 2007
If I put a formular in cell A2 "A1*.10" how can I get the result to come back negative if there are no negative numbers involved?
View 2 Replies
View Related
Jan 30, 2009
I have a column of consignment numbers in this location
Sheet Name: C NUMBER
Reference: C3:C10000
I have an entry field for which people may type their Consignment number in at this location to search for possible match
Sheet Name: Query
Reference: Cell D10
I would like to be able to allow a portion of the number be typed in to D10 and then have Cell D11 show the full first match from that partial entry.
Thank you,
View 9 Replies
View Related
Mar 26, 2013
I have created a template workbook for individuals who work on different sites. The spreadsheets used by each individual have the same headers. The sheets all sit in the same folder in dropbox.
I am wanting to produce a summary workbook within the dropbox folder that automatically populates when an individual updates their workbook.
I have attached mock example of the individuals worksheet for your information.
The questions that I have are as follows:
1. Is it better within an individuals workbook that they record their activity all on one sheet as opposed to having a spreadsheet per months activity? It seems tidier to have a sheet per month but does this make it more difficult to produce a summary workbook?
2. The individual is required to record a patients individual drugs on separate lines but they need to record a unique identifier, age, *** and month worked. Do they need to record all this for each line of drug or is there an easier way?
3. How should the summary sheet be produced - pivot table or sheet with formulas linked to the individuals workbooks?
View 3 Replies
View Related
Apr 25, 2014
I have excel result sheet which contains students information. i.e. name, subjects and their corresponding marks, grade, percentage etc. So from that sheet I want to print result cards for each students separately from the data (result sheet).
View 4 Replies
View Related
Mar 27, 2009
I have attached a sample workbook, (Pricing Sheet - Major) within this work book there is a worksheet entitled Price Book which has commonly-used materials, unit prices and labour rates. What he wishes to do is use the contents of the Price Book worksheet within a worksheet called Pricing Sheet to enable him to prepare quotations.
I had the idea to first sort the items in the Price Book worksheet and then produce a dropdown list of these items for use within the Pricing Sheet worksheet and used the VLOOKUP function to obtain the values for unit prices and labour within the Price Book and use them in the Pricing Sheet worksheet.
View 5 Replies
View Related
Oct 17, 2008
i have a purchase order sheet
firstly i have attached said spreadsheet
1st sheet is named purchase order
2nd sheet is named purchase order numbers
upon opening the spreadsheet i would like on sheet purchase order to automatically input the next available number from column a in sheet purchase orders ( in this case next available is st010 and put that into cell k12 on sheet purchase order
i then want to enter the relevant data in sheet purchase order cells
b16 , b23 , g23 , i23 and d28
i then want to click a button with macro attached to transfer that data in to sheet purchase order numbers
View 14 Replies
View Related
Dec 2, 2009
I have a workbook with 2 worksheets, Sheet1 & Sheet2.
Sheet1 has a list of products on it, with a code, pack size and cost.
Sheet2 has got 8 columns in it.
The first column will be A , B , C
The second will be D, E, F
Third will be G, H, I
Going right through to column 8 which will be V, W X
In the second column in each on I will be have a (sell by date) So B, E, H, through to W will have sell by dates in them.
What I am looking for is a macro that will give me a list of short sell by dates when I click the button. So if today’s date is 02/12/09 when the button is clicked it will give me a list of all the product that have lets say a month sell by date left, I would like to be able to change the month to anything I want, just in case I want to do a longer check on the sell by date.
When it finds a short date I would like it to check against the code, so in the first column the code will be in A, it will match that code up against the code in Sheet1 and give me a list of the Code, and the Product from sheet1 and the sell by date that’s in Sheet2. The problem I have is when data is put into the second column on sheet2 which is D.E.F, I would want it just to check that column and not the A,B,C from column 1.
View 14 Replies
View Related
May 19, 2014
When I synthesize the mark of the class, I'm trying to build a macro to copy the top 5 highest marks in each class into a new sheet (sheet: Total) to compare the mark of the class results (like the file I attach).
I finding the way like that:
Step1. the macro run filter with data sheets. then sort by largest to smallest
Step2. the macro copy 5 top of data sheets to the result sheet.
View 5 Replies
View Related
Aug 14, 2014
In my attached sheet.. I want the result from data sheet.. I have to enter the batch no and pallete no.. then all the data have to display in result sheet
View 2 Replies
View Related
Jan 24, 2009
I need to calculate data from one sheet 'Promotions' and paste/drag the formula onto the 'Summary Sheet'.
The paste/drag is important to include all data.
Trying to multiply [on sheet Promotions] 'NumberOfPromos' times (*) 'ProfitPerOffer' (using rows 1-8, let's say).
Then paste/drag it in its place on the 'Summary Sheet' in the 'Total Receipts' column.
I've been playing with =Promotions!$E$2:$E$8 * $J$2:$J$8 = 'Summary Sheet'!$F2
and things but cannot get it to work (I know I am WAY off base).
I've been working with Access and trying to duplicate a simple database in Excel.
Attaching a simple project example.
View 10 Replies
View Related
May 29, 2006
Is there any way to use Advanced Filter placing the result (the filtered data) in another sheet ?
I have tried but with no success... An error message is displayed (something like "It's not possible copy filtered data from a sheet to another")...
What do I have to do to get it ???
View 6 Replies
View Related
Jul 3, 2014
I have excel file, consists of 4 sheets Data, PF, Gratuity, Discrepancy. Here I have Emp no. field as key field, now here first I have to check Data Sheet data with PF.
For example,
Suppose, we are considering Emp no.: 4 from data sheet. It will check in PF sheet for same Emp no. if it find then it will check the relationship and first name and last name from data sheet. If it is ok then it will return output in discrepancy sheet with OK. Discrepancy sheet format:
Emp No. Spouse Child1 Child2 Father
(All the relationship present in Data Sheet in Column)
4 OK
9 OK OK OK NA
And so on.
View 8 Replies
View Related
Apr 3, 2014
If I populate a cell from a cell on another sheet but plan on deleting the data on the other sheet is there any way to hold the result on the new sheet even if I delete where it came from?
View 3 Replies
View Related
Dec 12, 2012
In workbook 1, I have summary sheet with columns
A -> SNO
B -> Customer Name
C -> Product Name
D -> MODEL
E -> Quotation Rate
I have to retrieve MODEL & Quotation Rate .
and also having seperate sheet for each customers say X1 , Y1 , Z1 ( so other sheet names as X1, Y1 & Z1)
In X1 sheet , I do have following columns
A-> Product Name ,
b-> Model (as of now limited to 1 per product name)
c -> Rate
Now my request is as follows Based on the Column b value in Summary sheet - i have to goto respective sheet and do vlookup for the respective product name and retrieve model and fill it in Column D. I heard i can use INDIRECT function and Vlookup in this junction .
View 5 Replies
View Related
Jun 4, 2008
way I can search a particular column, and when results are found, copy the rows where the results are to a new worksheet? And perhaps reuse the same worksheet (so clear the sheet on every execution)
I have tried auto and advanced filter but its not really working to my liking....
View 6 Replies
View Related
Jul 16, 2013
I am working on a workbook in which I extract data from a SQL database into a sheet with more or less the following format:
ID_Company
ID_Level
ID_XX
[Code].....
The aim is to extract a list of any employee that has been tagged as "NEW" in the last column, with his/her detail as per the column headings - and place the resulting list in another sheet. The extract is done dynamically and varies from month to month. So in other words the column headings 2013.MAY and 2013.JUN will change next month to 2013.JUN and 2013.JUL respectively - and so would the detail of the data below them as well. The "NEW" tag is simply assigned through an IF statement.
I had a look at most of the Excel Magic trick video clips on YouTube and managed to get a data extract from my main data sheet - BUT was only able to do the extract on the SAME sheet and NOT onto another sheet in the same workbook.
View 3 Replies
View Related
Jun 3, 2008
I have a table in excel with a group of headings. (Serial Number, Model, Description, Repsonsible and a few others) What I would like to do is be able to search every cell within the range of that table. When the user clicks search an inputbox is displayed and you can enter any search term you like. if there is a match within the cell range, i want the entire row (and the heading row always at the top) printed into a new sheet to display results. There may be a match in multiple rows, and id like evrery row displayed.
View 5 Replies
View Related
Apr 11, 2014
vba script to convert the text in a cell(split considering "Space" as delimiter) into multiple Rows and Single column & the resultant row's(A1:A7000) values need to be searched in Column(B) of another sheet,if the search result is false then the value in the row need to be highlighted in red.
I am able to split the values in one column into mutiple rows,but need to have all the resultant values in a single column.
i,e,.
I am able to split it as below:
Column A
Column B
Column C
[Code]....
for second situation - search I could search based on the values by using Vlookup() function,but unable to highlight when search result is false.
ex:
if the search string "ACB" in Sheet1 is not available in the sheet2, then value "ACB" should be changed into red.
View 1 Replies
View Related
Sep 27, 2011
I have a query on retrieving the result from a set of range from other sheet.
Query: In the Sheet1 Column B has to take first two words from the
Column A & lookup the matching results from Sheet 2.
(The search range should be the first two words of each cell)
Result: The complete sentance in the Cells (First Two words) should get placed in the Sheet1 Column B
Example: The Expected result should be as mentioned below. (Blue in color should be the results)
Sheet1A BC1NameResults2abc def fkfeabc def words3ghi jkl kikgN/A4mno pqr stu lkasmno pqr must work5
Don't give my book don't give up6vwx yzyvwx yzy not7xwv uts rqpxwv uts yes8omn lkjN/A9ihg fed cbaN/A
Sheet2A BC1Lookup Data2abc def words3xxxghi jkl4yes you have to5don't give up6no you will not7mno pqr must work8vwx yzy not9Excel work10xwv uts yes11omn mre lkj12ihg not fed 13not work14
This is my query
View 7 Replies
View Related
Nov 7, 2013
I use the following formula in excel 2007 (and it's working perfectly in 2007) but this formula does not work if I work with the same file in excel 2003 ...w why and what I have to adjust?
=IF(SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B))=0;"not found";INDEX('sheet 2'!D:D;SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B));1))
(I'm working with the dutch excel version so it might be that ";" must be ",")
View 6 Replies
View Related
May 29, 2006
Is there any way to use Advanced Filter placing the result (the filtered data) in another sheet??? I have tried but with no success... An error message is displayed (something like "It's not possible copy filtered data from a sheet to another").
View 4 Replies
View Related
Feb 17, 2009
I have a formula that needs some tweaking. This formula is to reference the ACCT and find the “best” contact information and return the result to the Merge sheet. If the ACCT does not have a Parent then you reference the ACCT to the abc_Phonelist sheet. If the ACCT does have a Parent then use the Parent ACCT since it has a more desirable account number to reference against the abc_Phonelist. If the ACCT or the Parent ACCT uses Processors then the ACCT from the Processors sheet has the best reliable account number to reference against the abc_Phonelist.
In sum, there are three different possible “number tiers” that can be used. The first, the ACCT phone number. This means that the ACCT has neither a Parent nor a Processor. The second, the parent ACCT phone number. This means that the ACCT has a Parent, but neither the Parent ACCT nor the ACCT has a Processor.The third, the Processor phone number. This means that either the ACCT or the Parent ACCT has a Processor phone number.....
View 2 Replies
View Related
Jan 23, 2012
I have a spreadsheet containing 2 sheets. In cell A3 of sheet 1 I have a formula that generates either a blank or "1". In sheet 2 I have a macro called Index which I would like to run when the formula in A3 displays "1".
Having done some research I know that the worksheet change event does not work with formula changes so have tried the worksheet calculate function but to no avail. I have done a lot of searching but cannot find what the problem is.
View 6 Replies
View Related
Apr 15, 2009
I've looked online and through the forum and can't find a solution.
I'm using the following code...
View 3 Replies
View Related
Sep 7, 2009
I have 2 sheets of data one for data the second for input and calculations.
SHEET 1 (Data)
HEADINGS - FROM / NUMBER
A / 81
C / 99
E / 01
SHEET 2 (Input & Calculations)HEADINGS - FROM / TO / FROM val / TO val
I want to be able to stick "A" in Col1 (FROM) and the number 81 to automatically appear in Col3 (FROM Val). I am unabel to use LOOKUP as my Pocket PC doesen't recognise it.. VLOOKUP and HLOOKUP are ok though, I just can't seem to get them to work for me.
View 3 Replies
View Related
Mar 9, 2013
I coach High School track and recorded all the kids workouts in DATAEASE - I had to upgrade my PC - (Dataease no longer works) so I am trying to record the kids workouts in EXCEL and be able to produce reports either by Code or NAME. Attached worksheet - SHEET 1 - data entered / SHEET 2 - is a report I would like to produce.
View 3 Replies
View Related
Feb 22, 2008
Im wondering is there any way/method to make a certain number of cells randomly produce to an amount you want.
For Example:
1 92
2 150
3 37
4 86
5 38
6 97
7 =500
View 9 Replies
View Related
Jun 6, 2007
I have 2 XL sheets with the below data.
Sheet 1
EMP# Ename
111 AAA
222 BBB
333 CCC
Sheet 2
Emp# Marks
222 80
111 90
333 60
Now i want a macro which will compare the data of the two XL sheets with the help of Emp# and produce a new XL sheet which will look like this
Emp# Ename Marks
111 AAA 90
222 BBB 80
333 CCC 60
View 7 Replies
View Related
Dec 5, 2012
I want to run a macro if the result of an IF function is true.
E.g. cell J55 contains =IF(H55>I55,"Goodbye","")
H55 contains =NOW()
I55 contains =DATE(2012,12,31)
Now by changing the dates etc. it prints Goodbye.... simple enough, but what I want is for it to run a macro that selects a range from the previous year's figures and simply changes the fill colour on the range. The macro for this also works fine.
View 3 Replies
View Related
