Getting Data With Conditions To Another Sheet?
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
ADVERTISEMENT
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
Mar 24, 2014
I have some problem with the extraction, I would like to extract the data to another sheet based on variable conditions but I can not do it. My code in just case working if the all conditions are specified. The target is that if want the code could extract the data based on one condition even, or two or three or more.
View 7 Replies
View Related
Jul 1, 2014
I have an Access table which has following fields:
ScanDate Number DataType
Type Number
Type1 Number
Type2 Number
BatchNo Number
Cases Number
Pages Number
Now I have a useform in Excel so that when the user selects the Date from the combobox then it should check for total batch numbers (Count(BatchNo)), total cases (Sum(Cases)),Total Pages(Sum(Pages)) where ScanDate= Date from the combobox group by Queue Number. The Queue Number consist of Type+Type1+Type2.
See attached the Excelsheet where the data should be populated to. WBCount.xls‎
View 1 Replies
View Related
Apr 26, 2014
I'm trying to auto populate a calender style sheet in Excel 2010 based on data from a Work Schedule sheet. The work schedule sheet contains a list of jobs, with each row representing a different job. There is a column for the start date (e.g. 25/04/14) and a column for the end date (e.g. 26/04/14). There are other columns which select resources such as people and vehicles. Each resource may appear on any one of several columns for each row, e.g. Site Operative 1, Site Operative 2 etc.
On the calendar sheet, in which one cell represents one day (e.g. 25/04/14), all the dates are shown along the top going right and all the resources are shown on the left going down.
On the calender sheet, in every cell I want a formula that will look at the Work Schedule sheet and see if that particular resource is being used on that particular date. If it does, the cell can display information from another cell such as the job number or job name to which the resource is assigned; if it doesn't, the resource isn't being used so it can display "Free" or "Available".
View 2 Replies
View Related
Jul 3, 2014
I am trying to write a macro where it will delete rows in Sheet A if any condition is found in the row which is based of conditions in Sheet B.
Sheet A :
Column A Column B Column C Column D
1234 ABC tyu 4588
asd qwe www 4455
zxc zdaa 1234 4441
ghj llll 1111 poo0
Sheet B (where i kept my conditions in Column A: )
Column A
1234
1111
(and many more....)
the end results will be the Row 1 and row 4 will be deleted. I have tried to google but most only contains deleting rows with a specific conditions.
View 10 Replies
View Related
Dec 10, 2008
I am trying to run conditions on rows and if conditions are met move all rows that meet that condition to a new sheet. I have serveral thousand rows on what we will call sheet1 with about 25 coulumns.
View 12 Replies
View Related
Sep 14, 2013
It is any code in VBA to find a Maximum value whit 3 conditions from one sheet to another ? Somethig like this:
ActiveCell.FormulaR3C7 = "=SUMPRODUCT(Max((sheet1!R2C6:R65536C6=RC[-1])*(sheet1!R2C3:R65536C3=RC[-2])*(sheet1!R2C4:R65536C4=RC[-3]))"
ActiveCell = ActiveCell.Value
I need to find a max value from a sheet if acomplish 3 conditions in another sheet.
View 1 Replies
View Related
May 31, 2009
I’m having difficulty writing a formula to solve the following dilemma:
I have “Sheet1” with staff details and each staff member may be listed more than once. I need to use columns C (StaffID) and L (Load). Load values may only be 1.0 or 0.5.
Then I have “Sheet2” which is being used to compile data from a range of sources – this is where I need to put my formula to get my answer. “Sheet2” has column A (StaffID) and each ID will only appear once on Sheet2.
The formula required needs to solve “if the StaffID from Sheet2 appears in Sheet1 (keeping in mind it may appear multiple times), sum the Load values for that StaffID provided the Load value on each line only equals 0.5".
If the StaffID doesn't match, the cell return is to be 0.
I will then need to do this again in another cell where the Load value only equals 1.0 (obviously just amending the condition value in the formula).
View 11 Replies
View Related
Apr 17, 2012
I have 2 worksheets (X and Y) and am trying to write a macro/vba to copy certain cell values from a row if that row has a marked cell. For example, on sheet X, marked cell is column P and when P = "Y", then values from cells B, D, I, J, L need to be copied to sheet Y.
Sheet Y has multiple rows and the values from X need to be copied into appropriate rows. B from X goes into A from Y, D into B, I and J into C, etc.
Im not sure how to go about this other than have every cell copied and then filtering sheet Y. But Id like to have a macro only copy marked rows and appropriate cell values (some contain formulas) as the data is constantly changing and sheetY has to be printed up.
View 3 Replies
View Related
Sep 26, 2013
I want to be able to create a log of incoming orders and requests with a column containing initials of the person who will be responsible for handling the item entered. From this sheet, I want to populate other worksheets in the workbook with the data base on the initials placed in the first column. Each initial set would have its own dedicated sheet in the workbook.
Is it possible to create and IF "ST" in column A of main log THEN copy all data to designated fields on the ST worksheet?
View 9 Replies
View Related
Apr 25, 2008
Im trying to copy data that meets a certain criteria from a list on one sheet to another. The problem with the code i've written is that i can't get the loop to work. Once it finds one example it copies it but then stops. My code is as follows:
Private Sub Find_Ammendments_Click_Click()
Dim RowCounter As Integer, RowCounter2 As Integer, RowRange As String
RowCounter = 1
Sheets("PINTS & BOTTLES").Select
Range("Start_pb").Select
While ActiveCell.Offset(RowCounter, 0) <> ""
RowRange = ActiveCell.Offset(RowCounter, 0).Address & ":" & ActiveCell.Offset(RowCounter, 8).Address
If ActiveCell.Offset(RowCounter, 5) >= ActiveCell.Offset(RowCounter, 10) Then......................
View 3 Replies
View Related
Nov 21, 2007
I am trying to generate a number based on the Month, Year and then three digits. For Example: 1107-001. I would need it to refer to another sheet for the previous used last three digits. Each month it would start over at 001. On the Reference sheet it would always be in the first column.
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
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
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
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
Jan 1, 2014
I have a workbook where Sheet A is a Master Order Form. Employees will input the quantities they need in Column C. The sheet will be otherwise protected. Sheet B is a "printable" order form that only contains the rows from Sheet A where the Quantity on Sheet A is not blank and is >=1. This will allow me to print sheet B with no blank rows for the items I don't need.
I found the following formula which claims to be able to do what I want:
=IF(ROWS($1:1)>MAX(C:C),"",INDEX(A:A,MATCH(ROWS($1:1),C:C,0)))
I can't get it to work correctly across two sheets.
I've attached Sample of spreadsheet for review : HDForm_Test01.xlsx
View 6 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
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