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 RepliesI 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 RelatedI 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".
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.
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).
I want to know how I can make Excel look at a list of information on one worksheet, extract certain items from the list, and place it on another workshet
For example, the following list would be imported from an inventory program as text and pasted on worksheet 1:
P+green beans
d-2.99
c=2
cd+10258
p+cherry tomatoes
d-3.99
c=1
cd+11204
p+red apples
d-.69/lb
c=3
cd+08902
p+tissue
d-1.99
c-2
cd+20023
How can I have Excel take this information and create a list like the following on worksheet 2? ...
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".
I have a rolling calendar worksheet that I need to create a formula to auto populate the sum of 8 weeks out, 4 and 8 weeks back based on any work week date I may plug in. So on the "summary page" based on a work week date, I need 8 weeks out, 4 and 8 weeks back projections to be auto summed for 10 different part numbers. The "13 week rolling" page will continue to be updated so that everyweek another week is added and the last week will fall off. (this part can be done manually if no function can delete the oldest column) When the weeks are added it must include the workweek in question. i.e. ww25 = (8weeks out) ww25:ww32 / (4weeks back) ww25:ww22 / (8weeks back) ww25:ww18. see example workbook attached.
This has been driving me nuts for a few weeks now.
I am an architect and recently I was required to do some extensive calculation relating to water requirements and toilet requirements for a building that i was designing. i created a excel file, with a basic vlookup function to put certain values in the cells that are pre-defined for a particular building typology (mostly created by governmental bodies as guidelines). for example, when i choose a particular type in column E, column F and G fills up automatically. now this table also gives me occupant load based on what is the area i enter in column D.
The problem is that I want to automate the calculation of toilet requirements. Now, each typology will have a different formula to calculate WC/Urinal/washbasin.
1. there will be 17 different formula - one for each typology (as shown in sheet 5)
2. Column j will calculate what is the number of WC required based on 2 criteria - first id what is the typology and then looking what is the number of occupant.
The problem here is for example, if in E9, selected typology as "offices", the formula of offices typology will be used in J9 and occupant value from H9 will be used but if i have E18 also selected as offices typology, the same formula will use occupant value mentioned in H18 instead of using H9.
Hence, excel will first have to look what is the typology selected. based on that, it will load formula - out of that 17 different formula - specific to that typology and finally it will refer to corresponding row for occupant load and will use it in the formula to get the result.
I have a small data set (ends up with 8 columns, up to 1000 rows), that gets imported sorted and formated via a macro linked to a button. I need to be able to look at one particular column of info, which will contain different text values every time the data is updated, and create a list of all the different values that occur in that column (maybe 10 max). Then...... I need to calculate a numerical value from adjascent column, linked to the text values from the first process. Below is an example to better explain:............
View 2 Replies View RelatedI 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.
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 RelatedI work for one half of a joint venture & am responsible for planning & expediting. The other half does purchasing. The bi-weekly PO download reports I receive are less than useful. I have already written the code to delete undesired sheets & add, format, and enter headers for a "Summary" sheet.
I need code to move to the next row, and run formulas to pull data from the next sheets, and repeat until there are no more sheets.
The number of sheets will vary from one download to the next, and the sheet names will vary from one download to the next.
Following are example formulas that need to be run on successive rows while pulling from successive sheets.
I am running Excel 2013 on Windows 8.1
How can I extract rows from one sheet into another, based on certain criteria?
View 1 Replies View RelatedI'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.
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.
I'm trying to figure out a way to lookup a value based on a conditional sheet name. I'm finding it difficult to explain in words what I'm trying to do, so I'm going to try and describe it in a miniature example of my spreadsheet. I apologize if this is going against forum rules, this seems like the quickest way to get my question across:
The following is the way my spreadsheet's first page is set up:
ID Apr-08 Mar-08 Feb-08
1
2
3
The column labels depend on another table, which changes monthly.
The rest of the sheets (24 of them, labled "200804", "200803", etc) look like this:
ID $ amount Date paid
1
2
3
I have another table upon which the na
I'm trying to return the "Date Paid" value for each ID for each month. Each month, however, more data arrives, and the labels change. I have several dozen spreadsheets formatted in this way, and I'm trying to avoid having to change the vlookup (or other formula) manually each month for each sheet. Is there a way to make the vlookup depend on a table?
This is essentially what my formula looks like right now for cell A2:
=VLOOKUP($A2,'200804'!$A$1:$G$10000, 3, FALSE)
If I could replace '200804' with a cell reference, that would be ideal. I haven't been able to figure out how (if possible); I tried experimenting with index sheets but that went nowhere.
I am trying to write a macro which will extract data stored in columns C,E, F, I, J, P, Q, AC from another excel sheet to "Review Data" sheet when the "extract data" button is clicked on the "Review Data" sheet . However, the location and the name of the file should be supplied by user which will be an excel file.
View 1 Replies View RelatedI have two sheets, one sheets contains the details for ALL records on the other sheet contains a limited selection of records.
What I would like to do is to be able to filter the sheet that has ALL the records so that it displays ONLY he records on the filtered list.
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.
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!
I am looking for VBA code to extract data from each tab into master tab based on data.
I have 3 tabs (inputs)
Tab1 (Dept A-NAME) (RANGE B4:I7)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
A
2/25/2014
Customer 1
10
1
3
ABC
[Code] .....
Tab 2 (Dept B-NAME) (RANGE B4:I11)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
B
2/25/2014
Customer 3
10
1
3
RTY
[Code] .......
Tab 3 (Dept C-NAME) (RANGE B4:I7)
Is it possible to run vba code to get below result in new tab
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
Result
1
A
2/25/2014
Customer 1
10
1
3
ABC
Dept A
[Code] ......
Condition here is date 2/25/2014, is possible when running code message box pops up to ask date, when we give conditional date it extracts those dates.
Excel -2007 & Windows 7
I have some data which is copied from our system in the format below, I then need to add only parts of this information to an existing spreadsheet on the network.
This is the information (there could be lots of these entries, but all need to be added to the spreadsheet)
Device ooo.1111111111111111111111555666663333 performance has deteriorated.
increased from average value of 2041 days to 44589 days.
warning
2/6/2014 2:38:18 AM
aaa-ab-cde00.efghijklmno.ddd.kk
Device ooo.11111111111111111111115556666633356kk performance has deteriorated.
increased from average value of 2041 days to 44589 days.
warning
2/6/2014 2:38:18 AM
*aaa-ab-cde00.efghijklmno.ddd.kk*
I then need part of the above data to be transferred to in Excel in the following column headers (see attachment for layout)
DateTimeHostDeviceLow ValueHigh Value
what is the quickest and easiest way of updating this spreadsheet..... I have to update the spreadsheet every morning and it is very time consuming .
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‎
Want to be able to select data from Column C (3 data validations/drop down menus) and have the corresponding values display in the appropriate fields in column H. Not sure if this is possible?
View 4 Replies View RelatedWhat formula do I use to populate certain cells (E5:E10 and J5:10) based on match with condition (E3) with cells from and in the ranges C2:C73 and D2:D73 without creating milelong IF formulas? I am almost at the goal... past 10pm here in Thailand and still at the office
View 2 Replies View RelatedI’ve created a spreadsheet to record all transactions ( Sheet 1) at a train auction for members only (Sheet 2). Currently, the workbook I created will ‘manually’ create receipts for any buyers/sellers by filling in Member's name in Sheet 1 and then selecting ‘View ‘Member’ Receipt’ button.
Ideally, I would like a macro that will automatically generate a worksheet for any member that has sold or bought an item at the auction and insert worksheets between ‘Start’ & ‘End’ tabs (with option to refresh data) in location order (A-D). Because members come from near and far we would like to generate the members that need to travel the farthest first. Is it possible to create a receipt for only the members that have transactions?
I would like to extract from a massive list of names, all occurrences of one name and display the data on a new sheet.
For example;
Name Date Location
James 23/04 edinburgh
Jimmy 28/04 london
David 03/05 Belfast
Sarah 08/07 Glasgow
David 12/09 Birmingham
James 13/01 Manchester
So the output on the new sheet would be;
Name Date Location
James 23/04 edinburgh
James 13/01 Machester
I basically want to find where James has been and on which dates without having to scroll through 1000's of names.
Creating macro which can read data from one/or more sheet and copy and paste it into another sheet, by matching up column A & B and Row 1, between the both sheets.
I have monthly data stored in each sheet, named as May, June, July etc. I also have sheet called 'monthly' within the same workbook, where I would like to extract all monthly totals, from sheets july, June etc, into the 'Monthly' worksheet by matching up its names from Column A and B and row1 header with each sheet's names.
The june,july worksheets have date1, date2 etc, columns with data and after those columns, there are the 'bs, rm, cm, cd, cl' columns, and these are the columns, I would like to use, to extract the numbers from and paste in the 'monthly' worksheet.
My current issue, is that the 'bs, rm, cm..etc' columns are not fixed, due to the 'date1, date2 etc' columns can vary, for each month.
For this current task, I was using a formula as shown below to extract the values into 'monthly' worksheet, however due to unfixed columns for 'bs, rm, cm... etc', I could not get the formula to work for the 'july' and 'Aug' months (i.e. extract data into 'monthly' worksheet, from the june&aug sheets).
[Code] ......
I would like to request, if it is possible to use the formula below to re-create macro which can carry out this task, by extracting all the values from 'june, july, aug etc' sheet, into the 'monthly' sheet, at once.
I have attached a sample workbook with example of data layout and desired output shown in monthly worksheet.
data_extraction2.xls
Is the an easy way (ie, No VBA) that can have a form (similar to this one [URL]) and then extract the input data to a regular table look spreadsheet with the column heads at the top that match the form, Name, Hotel costs, Date, etc?
View 1 Replies View Related