Copying Data With Conditions?
Apr 13, 2014
I am trying to copy data from one spreadsheet which is updated daily to a Master spreadsheet. But when the data is copied to the master spreadsheet it compares the data and if the data is missing from the daily sheet that row gets moved to a 'achieve' tab within the workbook.
Scenario: We get a daily spreadsheet with outstanding jobs this gets copied to the master spreadsheet. But when copying the data to the master spreadsheet it check to see if todays daily sheet matches the master sheet. If any data is missing this mean the job has been completed yesterday so the row needs to be moved to a tab called archive in the master spreadsheet.
trying to automate process: Copy data from daily spreadsheet to master spreadsheet -> vba/macro - compares data copied in to (master spreadsheet) see if any jobs are no long on the daily sheet = (jobs been completed) -> IF this is the case move row of data to achieve tab in the master spreadsheet.
View 1 Replies
ADVERTISEMENT
Jun 25, 2008
I'm working with a DDE feed of stock quotes (much like this thread: http://www.mrexcel.com/forum/showthread.php?t=322427)
Ideally, I'd like a row of information to be cut & pasted as the values into another worksheet IF an objective condition is hit.
A very simple example: Let's assume I've got the following data:
Book1___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA2=ABCDEF1TimeStockBid VolBid PriceAsk PriceAsk Volume211:10:23MSFT3035.5535.5810311:10:23YHOO50029.8529.920Sheet3 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I'd like to be able to copy the entire line to a new worksheet IF the bid volume is above 400, every time it updates with new data.
Unfortunately, my VBA knowledge ends very shortly after recording a macro & making small tweaks, so I have no idea how I'd set something like this up.
View 9 Replies
View Related
Dec 17, 2009
What I'd like to do is to create a loop that will run through the cells in column B and change the values in column A to the value in column B IFF the current cell in column B does not equal "#N/A" AND the cell above the currently selected cell in column A contains the text "Manhattan". The code should run through each row and the data from the cell in column B if the above conditions are met will be copied into the cell in column A on the same row;
View 4 Replies
View Related
Apr 16, 2008
I have found several forums with this question but none of them seem work/fit my needs
What I need to do is look in Sheet1 at column 'B' begining at row '15' (row 14 is table header) and if the cell contains "Yes" then copy 3 cell directly to the right (C15:E15 in the case of the first row) into Sheet2 into cells B15:D15 then do the same for row 16 and so on up to row 600
Creating a cut down copy of the Sheet1 which does not contain the row which determines if it should be copied.
View 9 Replies
View Related
Oct 17, 2007
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies
View Related
Dec 27, 2012
I have a simple reporting sheet where the data for orders place is in one sheet and on the other sheet is an imput box for 'date' and it filters through and presents a table of data and two graphs for the date chosen.
I give a daily report (contents of this sheet) but I also need to leave this sheet available for anyone to open and change to another date.
I want to copy the repor sheet into a new one and email but I want the graphs to remain, not go blank when anyone changes the original sheet.
Excel 2007 / Windows 7.
View 3 Replies
View Related
Mar 21, 2014
I have a range of weekly data that I need to copy into another workbook, paste it below the data that already exists there and then delete the data from the original workbook. I would love to do this with vba but after hours of searching on how to do this my brain is frazzled.
I have 50 workbooks that I need to import weekly into one master sheet (Master.xlsm) but they need to be done individually after the weekly data has been checked. The master sheet will therefore have existing data and the new data needs to be appended at the bottom. Also column A will be blank in both workbooks so to find the last used row it will need to look in column B.
The number of rows in the weekly sheet will also vary rather than be a fixed range so I guess the last used row will also need to be found there too.
The attached file is a cut down version of my working file showing where the data starts on Row 14, I won't need to copy the headings.
Example file 21.03.xlsm‎
View 4 Replies
View Related
May 31, 2006
breeze.
Here is my situation:
X Y Z
Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep
A
B
C
The above format is how I want my spreadsheet to look like. On another sheet within the workbook, I have a chart with some control options that allow me to vary X, Y, Z (I can vary either of these individually). Anytime I vary any one of X,Y,or Z, then the values in A,B,and C all change. This is what I am looking for, a Macro that will allow me the opportunity to everytime I change or vary an option (X,Y,Z), I can hit "Save Scenario" and the new values will be appended to my "Report" sheet. The values of A-C and X and Y are kept on a sheet entitled "FY 07". X is maintained on a sheet called "Inputs". So in all, X,Y, Z and A,B,C are all copied for each scenario.
View 9 Replies
View Related
May 17, 2014
I'm trying to set up a sales tracker that will list items sold and corresponding account number of each sales call per day .
On sheet 1, I have the "tracker". A1 date, A2 account number (manually filled in). A5 to A15 product codes (numbered). B5 to B15 each product sold for that account.
On sheet 2, I have columns for each day of the month. In row 1 each column has each days date for that month. I would like to be able to pull in data from sheet one (the "tracker"). For example on the date 1/2/2015 each time a sale is made to a different account number that day and entered on sheet 1 it would be automatically entered in sheet 2 in the column with the matching date heading in row 1 (for example 1/2/2015). It would first show the account number, then each product that was sold to that account.
There could be around 20 different accounts per day each with around ten different products sold per account per day. Each time the "tracker" contents on sheet 1 are cleared to make room for a new sale(s) that day, the sale data on sheet 2 would not be over written or deleted. The next sale that day would start under the previous account entry. If each account number could have a colored background that would be useful in quickly locating a specific account number, but not absolutely necessary.
View 2 Replies
View Related
Nov 25, 2006
I have 4 columns of data:
Employee; Invoice #, Invoice Total ($), Month of Invoice
I am trying to create a simple report that will display Invoices, By Employee, for a given month. I have tried using the following VLOOKUP statements within an IF statement:
=IF(VLOOKUP(H2,S1DATA,4,FALSE)="OCT",VLOOKUP(H2,S1DATA,2,FALSE),0)
"S1DATA" is the table with the data I described above. I am trying to retrieve data for the month of October. When I use the above statement I get the information that I want, but when I copy the formula down (matching the employee name in H2, H3, H4... etc I continuously get the same invoice information.
View 9 Replies
View Related
Jan 21, 2013
I have a worksheet which contains certain sections. I want to create a macro which will run if data is input into those sections. This macro should copy whatever was entered into another worksheet automatically as data is being entered. Is there a way for that?
View 8 Replies
View Related
Jun 3, 2006
I have a workbook with a database of 15 different products. We receive information with current prices for each of the 15 products on a weekly basis. This info comes in one single worksheet. I have created a different file with one worksheet for each product to track price behavior over time. I need to update these database everytime I get a new report. What I was trying to do is to develop a Macro to copy the data from a "Master" worksheet that includes all 15 products info to the respective worksheet for each product. Information is sorted in columns having the most recent date in the lowest column. I would need the Macro to copy the new data below the last price for each product. I would copy the data into the "MAster" worksheet, then run the Macro who would copy each row and paste it in its respective worksheet in the row rightafter the previous one.
View 4 Replies
View Related
Sep 25, 2012
I created a master data sheet to enter data manually to analyze. It contains 20 columns, and rows(continue change, because we enter data all of the days). I need to extract data to another sheet, when the user enters data in the master data sheet, but with some conditions, when the column 6 contain the word ASQ, and then extract all of the data, but the new sheet do not contain 20 columns, because the data that contains the word ASQ have only 16 columns.
View 6 Replies
View Related
Sep 28, 2012
I need to create a macro to extract data from sheet1 to sheet2.
The sheet1 contains 15 columns, and the data starts at row 5.
The sheet2 contains 15 columns, and the data starts at row 5.
We enter data all of the days, so we have like 500 rows in the sheet1.
Only 2 conditions to extract data from sheet1 to sheet2:
if the column9 of the sheet1 contains the word "NO" and column11 of the sheet1 contains the word "ASQ".
View 4 Replies
View Related
Mar 15, 2014
I am trying to pick certain data from a table and sum it in order to produce a formatted report.
The data has many different items of which I want to pick certain ones to sum on one line
See attached and some of the formula's that I have used, but aren't quite what I'm after
The formula I want to use is .... VLOOKUP(E5:G5,A14:C23,3,) ......
But it wont reference a range (E5:G5) !!! and I also want the formula to ignore blanks (as blanks in the data file are actual totals).
View 2 Replies
View Related
Jul 31, 2008
My data is as follows
MonthMilesAmountLocation
June 8£3.20 Loc1
June 8£3.20 Loc1
June 8£3.20 Loc2
July 8£3.20 Loc2
July 8£3.20 Loc1
July 38£15.20Loc1
July 4£1.60 Loc2
Aug 38£15.20Loc2
Aug 4£1.60 Loc1
Aug 8£3.20 Loc1
Here is what I want to do
- Avoide filtering and manually calculating data for the total expense in the month
I want to SUM all the (3)AMOUNT in the (1)MONTH of JUNE for
(4)LOCATION LOC1
I know its too much to ask for but can you suggest if i can combine SUMIF or SUM(IF) or any other formulas ???
View 10 Replies
View Related
Jun 1, 2009
I want to sum certain data, which meets two conditions.
My data set contains three columns and a lot of rows. The columns are the following:
PostalCodeDeparture
PostalCodeArrival
PassengersInCar
I want to sum the total number of passengers with departure postal code 5100 and arrival postal code 5110. (and I want to do the same for all other postal code combinations in the data set)
With "SUMIF" I can only include one condition.
View 4 Replies
View Related
Nov 3, 2009
I have a need for a formula that is able to count the data in column that coresponds to data in another column.
Example work sheet attahced for a quikie look.
View 8 Replies
View Related
Jan 17, 2013
combining 2 rows of data if certain conditions are met. I have attached an example of my worksheet for reference.
Example.xlsx
What I want to happen is if a number in column J is followed by an "x" then the values in columns AQ and AR should be combined with the row of the same number (e.g. if 236x is in column J2 then the values in AQ2 and AR2 should be combined with AQ3 and AR3 because 236 appears in column J3, the same process needs to be repeated for 237x with 237 etc.).
View 3 Replies
View Related
Jun 23, 2009
method to extract certain data from a column of values.
The attached workbook has two sheets. From the worksheet named "Data" I would like to be able to extract any values >0.00% and then display the corresponding "model" (C:C) on the other sheet called "Results". I need to be able to achieve this for each day in the month and disregard all other values so I am left with a concise list.
I have manually entered the info for the 1st June as an example.
View 6 Replies
View Related
Nov 7, 2006
I have very limited vb experience and I believe it’s the only way I can do what I need to get done. I am working on creating an invoice template. I want the price to populate based on two conditions. The two conditions are invoice line quantity and month. Let me provide data ranges. The user will enter a quantity for each invoice line moving down B17, B18, B19, B20 etc up to 24 lines. (through B40). The user will enter a current date in F13. Away from the main body of the invoice:
Range T5 thru T16 will be pre-populated with each month of year 2007. So T5 = 1/1/2007, T6 = 2/1/2007, T7 = 3/1/2007 etc.
Range U5 – U16 will be pre-populated with the line price for each month if the line quantity is less than 10
Range V5 – V16 will be pre-populated with the line price for each month if the line quantity is 10 or more
The price should autopopulate in G17 - G40 where a quantity is placed in the corresponding column range B17 - B40. The number of lines varies per invoice so I'm setting the template up with sufficient lines. As an example, the price should populate for each invoice line beginning in G17 to correspond to the month entered in F13 and quantity entered in B17 based on the corresponding month in T5-T16 and the price in either U5-U16 or V5-V16 given the quantity. So if the Month in F13 was February and the quantity in B17 was 25, the price that populates G17 would come from V6. If the Month in F13 is March and quantity in B17 is 5, the price that populates G17 would be from U7. I just don’t have enough experience to write this myself.
View 7 Replies
View Related
Jul 7, 2014
I am moving along in my masters project and I am having a problem organizing some data I got dumped with.
What I have is a 196 x 196 matrix of the worlds countries and the distance between each one. So each Row has a title header of a country and each column has a country and the cells between the two are the distance of said countries.
On another sheet I have about 10 000 different combinations of dates/years/months ect that I need to populate in order for Stata to work properly.
Rather than manually enter thse in one by one or copy paste what I would like to do is write a code that would do something like this.
If on sheet 2 (the combination sheet) A3 = Afghanistain and C3 = China than F3 = The China/Afghanistain intersection (In this case AN3 on the Distance Sheet)
I am having problems because I can't see to figure out a way to have a program lookup the intersection and give me the China/Afghanistan number based on all the conditions. I am assuming I need to use a matrix lookup of some sort. This way when I do some like Canada/Denmark on the Combination sheet, it will automatically look up and populate the distance from the distance sheet (That being AZ33).
View 10 Replies
View Related
Oct 26, 2008
I need to sort data similar to below using the following conditions. If the total of A's is greater than 4 they get an A. If the total of E's is greater than 4 they get an E. If they have more A's than E's, assuming they have at least 4 of each then they get an A. If the total is 5, but they don't have 4 A's then they get an E
NAME E'sA'sTOTAL
John 235 ( so this one should get E)
Mary 459 ( This one should get A)
Tim 112 (This one shouldn't get anything)
Jane 145 ( This one should have an A)
Sam 415 (This one should have an E)
I would like this to sort in one cell - I can do this over 5 cells - but not in one.
View 3 Replies
View Related
Jan 1, 2009
I'm working with wookbooks used company wide and I cannot add any helper columns which would solve the problem. I need to add data which meets certain conditions see attached workbook for a sample.
View 5 Replies
View Related
Oct 22, 2009
I'm using Excel 2002 and am having trouble with what I thought was a simple conditional command. On part of the spreadsheet I have 3 columns: Hours (D5), Cost Per Hour (E5) and Total Cost (F5). Here's what I'm trying to do.....
If there is no value in the Hours then the Total Cost should show an empty cell and conversly if there is a value in Hours then calculate Hours*Cost Per Hour in the Total Cost cell. My condition for cell F5 is as follows: =IF(D5="","",SUM(D5*E5)). Whilst it works if there is a value in D5, when empty F5 shows #VALUE!
View 5 Replies
View Related
Dec 29, 2009
hope the below example is clear to understand and makes sense .....
View 10 Replies
View Related
Nov 14, 2008
I work in Local Government and I am recording the contact that we receive for each transaction that are avoidable or unavoidable. This is broken down into service areas.
The data is imported into excel from a database and therefore is constantly growing.
What I want to do it a very basic summary page so that I can group those that are and those that aren't avoidable for each service.
Because the data is ever expanding I would like this on a separate sheet from the actual data.
I attach a sample of the worksheet and would be grateful if someone could advise what formula to use on sheet 2 in cells c4, c5, d4 and d5 to make it do it automatically.
View 5 Replies
View Related
Oct 2, 2011
I have two columns. In one people either saw Red, Blue, or Green. In the other people either chose One, Two, or Three.
I would like to get excel to read the first column, find all the times someone picked red, then look to the column next to it and see if they picked the number One. Then I would like it to count that situation. Then I want to do it for Two and Three. In the end I would have something that looked like this
---------One----Two---Three
Red-------5------8-------9
.
Blue-------4------5-------3
.
Green-----4------2-------6
Generated from something that looks like this
Red One
Blue Two
Blue Three
Green Two
Some kind of biconditional countif maybe
View 1 Replies
View Related
Oct 10, 2011
Currently trying to solve a niggling issue with removing duplicate row data from competition entry databases using the following code...
Code:
Sub remove_duplicates()
Dim lastrow As Long
Dim i As Long, j As Long
On Error GoTo errhandler
Columns("A:G").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
[Code] ......
errhandler:
'MsgBox Err.Description
End Sub
What I need to happen is for duplicates to be based on both the name and email matching (columns A + B) and then remove all duplicate rows, whilst ignoring but preserving the content in columns C,D,E and F on those rows that remain. Column G contains either "Yes" or "No" depending on a users membership status. Sometimes duplicated data will contain identical name and email address but have different membership status in column G where someone was logged in when submitting a form the first time, and then logged out the next (potentially a mixture of the two over multiple times). Therefore I need any duplicate users who have different values in the G column throughout the database to be detected by the macro and we must ensure that the single row retained in the final non-duplicated list displays "Yes" in column G for that user.
Duplicate users who are always "yes" or always "no" work fine, its only the ones with mixed values for column G that need to be analysed and their return forced as Yes in the final list.
The final list should preserve the original column structure and thus be only one row per unique user.
The above code appeared to work the first time with a sample file, but when running the macro on one of my full size lists (3k - 5k rows) it returns 2 rows for each user who started off as a mixture of Yes/No... ?
Both rows will say yes in column G (so that part is correct!) but I have to re-run the macro again on the same dataset for it to remove the duplicate yes rows to get the list as it finally should be!
So I can achieve the result by running it twice, but this is a bit of a cheating way to do it and also the next stage after this involves something similar across multiple worksheets so I need something rock solid to build upon!
View 5 Replies
View Related
Dec 27, 2012
What I would like to do is combine all data (from sheet: Page1,Page2, and Page3) into one sheet named 'Consolidated'. Unfortunately, as you will see from the attached file, my programming and understanding of VBA programming is pretty naive.
What I am after is, when I combine the data, from all sheets into the one, the data should be
1. Automatically Sorted by Patient Name
2. Group, results of the same patient from all sheets one below the other in seperate rows
3. Delete any other rows that may have a patient name and ID, but rest of the rows (New Mole,Clinically Suspicious......Benign Naevus) are blank. (This happens because of the way I have designed my userform. For first visit the details are entered in Page 1, then for second Visit Details are entered in Page2, and for third visit in Page 3. So when I enter the first visit details, the Patient Name and Patient Number are copied into Page 2 and Page 3 even though the other details remain blank. I have done it this way because, if say the Patient comes for visit the second time, the clinician may or may not be aware which visit this would be for the patient. So as soon as they enter patient number, it will give them a message that Patient already exists. So when they click Open Existing Patient Record, the form autopopulates with any notes from their previous visit into the respective page (i.e Page 1,Page 2 or Page 3).
Page 1 (Page 2 and Page 3 are exactly the same)
Patient Name
Patient Number
New Mole
Clinically suspicious
Changed from mapping Photo
Level of Suspicion: Score
Monitor
[code].....
View 3 Replies
View Related
