Have a worksheet Pricelist, require to pull data from the columns to a new worksheet only if qty is more than 0, and delete empty rows afterwards. Required result is in worksheet order. Original file is about 10K rows.
Attached sample file : example.xls
Any quick way to extract data from a table. I need to extract a value from a column that meets criteria from two different columns. I thought I could get this to work with vlookup, but have had no success. Sample data below in table 1 and I would like to get my data into table 2.
elevation
type
grade
percent
weight
5000
5000
5000
5020
[Code] ..........
I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.
I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).
For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?
Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250
Payment612345678750Vacation798765432800Salary898765432250
Payment998765432100Overtime1098765432450Salary1198765432300Overtime
Sheet1
Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432
Sheet2
I have gotten a code. It goes into Sheet 1 and picks up all the Rows (entire row) in which Column A = 1.
Instead of copying the entire row, I need to just copy the row but only specific columns (i.e. Row 2, Column B,C,D,E,G).
Is there a way to specify where it pastes values in Sheet 2 (say from column D) because Column A-C have pre-filled data which do not change. And when it pastes, it has no gaps (despite a gap in the copied columns (i.e. Column F in sheet 1 was skipped)).
VB:
Sub cond_copy()
'assuming the data is in sheet1
Sheets("Sheet1").Select
Sheet2.Range("A2:G5000").ClearContents
[Code] .....
Need to create a macro?
Delete rows where multiple column meets multiple criteria.
detail:
delete rows where
Column H is less than 10000
AND
Column C is empty(blank)
Those 2 criteria have to occur at the same time..
I need create a macro to perform the following - I don't know if it needs to be an Excel macro or via VB. This is to speed a process up and eliminate any spreadsheet messing around for our "admin" staff - they are not good with excel at all!
So, I have a daily/weekly down load of a spreadsheet (exported from access).
* I want to copy the values of column AK to A
* Then delete all entries in column D that equal 19, 20 and 6
* Then delete all entries in column AL that equal "A","B","C" or "D" but not "E","F" or "G"
* Then delete entire rows where the value in column A is a duplicate within the list
I will call this spreadsheet, for the purpose of this exercise, the "Master". I now have another spreadsheet with values in that should relate to values in either (or both) columns A and B in the "Master". I need Spreadsheet 2 values to be highlighted as cross checked in the "Master" and then all those in the "Master" that do not appear in the 2nd S/S will be normal white cells thus showing that they need to be chased by our Admin staff.
I'm looking to create an automated table with formulas to populate based on multiple criteria in a data sheet. I want to pull the person with the highest SALES but meets every criteria listed.
Currently I have it set up like:
Name | Sales | Penetration | Score | Target, etc.
But each criteria has a minimum/maximum criteria that they have.
I'm wondering, how would I be able to pull the NAME of the individual with the highest sales but meets criteria such as Penetration, Score and Target.
IN column J(on sheet 1) i want it to return text (OB) if Sheet 1 column A1 equals Sheet2 Columns A1:A500. And if Sheet 1 column A1 do not equal Sheet2 Columns A1:A500 return text(IB).
View 1 Replies View RelatedI am watching 100 stocks when the stock market opens at 9:30 EST. Not all the stocks will come available to buy or sell at 9:30 but will become available at different time intervals, sometimes 10 minutes after the market opens. When a stock opens it is common for it to spike up, then spike down, then go into a "normal" trading pattern, this is called a slingshot pattern.
If I have a predetermined price up or down for 100 stocks, how can I write a macro that will look at the stock prices and if it shoots above or below a certain value it will submit a buy or sell order? (I already know how to submit the buy or sell orders, just need to get an idea of how to get the macro to constantly check the prices and if it meets my criteria to take action.)
Note: I already have a macro running at one minute intervals to collect data. One minute intervals is to long, I need it in second intervals or less to pick up the slingshot pattern. Is this possible?
I have a worksheet (Data) that lists when pupils are in for Nursery sessions during the week. If they are in they have a 3 (hours) by their name in the relevant columns.
In the AM worksheet I now need to pull through a "register" so under each daily heading I need to pull through everyone that has a 3 next to their name under Monday AM / Tuesday AM / Wednesday AM etc. from the Data sheet. However, I don't want it to copy any blank cells. I then need to do the same for the PM sheet.
the traditional count if statement doesnt return what I need. I have an array of values that need to be checked.
Column: A B C D E
Data: .25 .49 .18 (Criteria 1 Result) (Criteria 2 Result)
What I need to do is check the data for the following conditions and if it meets the crirteria I need excel to place a 1 in column D or E.
Criteria 1
If any of the coulmn data contains a value less than .5 I need a 1 placed in column D
Criteria 2
If any of the column data contains a value greater than .5 but less than 1.0, I need a 1 placed in Column E. I tried using an IF/ Count If statement, but cant seem to get it to return the result I need.
I have a large amount of data, 40,000+ rows about how students do in remedial classes including from whom they took the classes. I have pulled from it a subset of student success as relates to the separate instructors. Now I would like to extract a consolidated list of those instructors for whom the student gain on criterion based pre and post course assessments is above a specific level. I have no trouble writing if statements that will look at two types of data, the course taken and the average student increase to determine which instructors meet or surpass the criterion, but I can only do that within a list that includes all the instructors. Because there are more than an hundred such instructors, it would be easier for the dean if I could provide a short list of those that meet specific criteria. I believe that it would be an array that I should use, but I have been fooling around with this for 2 days now, and am finding myself at a series of deadends!
View 5 Replies View RelatedI have a huge listed of assets - the column i where my info is has vasrious serial numbers.... some are a simple string of letters/numbers and the others look like this for example AG-1234567
I need to somehow pull out the ones that look like that. (AG-1234567). I only need those. Now normally i would just do a filter and pull em out however, this sheet contains 73k worth of items.
what would be the formula to perform such a task?
I've attached a workbook with a description of what I'm trying to do. I'm not sure if it can be done with formulas or if it will require VBA. I can move it to the VBA section if the latter terms out to be the case. it's not that I'm opposed to copying code, I just like to know how it works with enough depth that I can fix/modify it, and I can't do that with VBA).
View 6 Replies View RelatedI would like the following columns to show on a new worksheet if column Z and AA (which show a date and time) are the same as todays date, the columns of info I would like it to pull through are Column C,D, Z, AA. The row numbers could be different daily? is this possible?
View 7 Replies View RelatedI have been trying multiple SUMIFS and SUMPRODUCT formulas to try and revamp my budget spreadsheet. My first criteria is the month being a certain number (contained in a column), if that condition is met it needs to look for the account number I am budgeting for (row heading) and if it matches both criteria I need it to sum the Column that has the row heading of the account number. I have succeeded in doing a SUMIFS and manually finding the column that has the heading of the account number I am looking for, but I would like this to be an automated process so I can copy the formula. Below is the data I'm using (a very, very small portion of it), and the results are how my budget sheet is going to look. These will be on separate tabs in the workbook.
EX: For July electric revenue (44211) I need my formula to: (if the Month column=7 & if the Account Code row heading=44211, sum the column that has the heading of 44211). So far I cannot get this to work.
DATA
Account Code
44211
44215
[Code]....
I am working on a project to automate the import of data and fixing the formatting.
The data contains the date in column A and time in column B. The time is in increments of 15 min for each day. The rest of the data is in columns C:F. Some of the dates don't have all of the times within the day (so instead of 96 rows of data per day, there maybe 80 rows). I have a second sheet of just dates and times that has the correct amount of rows for each day. What I need to do is have the rows data that matches in both the date and time column from sheet 1 copied over to the corresponding date and time on sheet 2. Blank data rows on sheet 2 are fine. I have found some stuff on .Find, but the stuff I found was for specific searches.
Example
Sheet 1
7/27/13 12:00 AM 1 2 3 4
7/27/13 12:15 AM 5 6 7 8
7/27/13 01:00 AM 9 10 11 12
Sheet 2
7/27/13 12:00 AM 1 2 3 4
7/27/13 12:15 AM 5 6 7 8
7/27/13 12:30 AM
7/27/13 12:45 AM
7/27/13 01:00 AM 9 10 11 12
I have 6 columns of data laid out as follows,
columnA columnB columnC columnD columnE columnF
XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX
XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX
I need to find data in columns B and C and add the figures from column F, which drop down one line. I have written the following formula which is giving me far higher figures than I should be getting
= SUMIF($B$2614:$B$2640,"bhree*",IF($C$2614:$C$2640,">=01/07/2006",( OFFSET($C$2614:$C$2640,1,3,1,1))))
I have a folder which contains a number of Workbooks which were all built using the same template, therefore the layout and formatting is identical in each one. Cells A1:B15 of these workbooks contain information which I am attempting to extract.
I have been asked to build a worksheet with two cells for user's input. The first cell is the 'Directory location cell' and the second cell is the 'Policy Number Cell.' The policy number is found in Cell A2 of every workbook.
The idea in mind is that the user inputs the directory location in the first cell, the policy number they are searching for in the second cell and then excecutes the macro. The Sheet would then return all of the valuable information from whichever workbook contained it.
I think I need to compose a macro which trawls through the folder specified in the 'Directory Cell,' opened every xls until it found the policy number which matched the value in the 'Policy Number Cell' and then returned the cells A1:B15 into the new Sheet.
I am trying to use the following formula to pull out data from multiple excel files in a folder called "Certificate".
='D:SSR Sec and Techcertificate[STUDENT 2.xlsx]Student Record'!$B$10
='D:SSR Sec and TechcertificatePath
[STUDENT 2.xlsx] File Name
Student Record'!$B$10 Sheet name and Cell reference
The formula works without any problem. I want to replicate the formula to extract the same data in multiple excel files. In this case only the second part of the formula needs to change to "STUDENT 3", "STUDENT 4", "STUDENT 5" and so on. I have created a column in excel with those values. I am trying to use the cell contents in the above "formula", but I cannot seem to replace this value in the formula. Needless to mention that I tried to drag the formula, but it does not work. I am attaching the two excel files.
I have a workbook with 4 sheets one is called final and the other 3 are data1, data2, and data3. The data sheets have the actual data I am needing to pull from. Each has two rows of data: data1 has employee number in column A and employee code in column B. Data2 has employee code in column A and employee name in column B. Data3 has employee name in column A and employee email in column B. Now I need to pull the info form all three sheets into the sheet names final. So the final sheet needs to contain 4 columns for employee number, employee code, employee name, and employee email. The kicker is the data sheets don't line up with each other within each sheet it does but the first one in data1 is not the first in data3 and so on.
View 2 Replies View RelatedBasically, I run a DJ Management company, arranging bookings for 6 artists. What I'm trying to do, is have data be auto populated from the Master sheet, into separate sheets. The aim, is to create 1 master with ALL the dates for the month in, and the each of the separate sheets to only populate data relating to that artist.
For example!
Artist
Event
Location
Fee
Date
Tony
Tomorrowland
Boom, Belgium
5,000
01/05
[code]....
Now, I need to send the artists their individual schedules for the month, but I do not want to copy and paste individual rows to their new sheets as this is a pain. I know i can sort by artist and copy it easier, but really i just want to put in the info once and not multiple times.
So the master is sheet 1, on sheets 2,3,4,5,6,7 (each one assigned to a particular artist) i need the artists gigs for that month.
I am creating a spreadsheet for mutiple clients, which has around 5 columns. This spreadsheet will have 7 sheets on it. 1 sheet for each client, and the 7th will be to display all of the information together.
Basically I am looking to find out if this is possible?
I will try and explain a bit more, as above is just beifly what I am loking for.
I Have Client.xls
On my Tabs I have
Client1, Client2, Client3, Client4, Client5, Client6, All Clients
In Each individual spreadsheet, I have the following Columns
Incident Reference; Description; PMDB number
Each Day data will be entered into each client sheet, and I would like this information to be populated into the All Clients Tab. I am not sure if this is possible, and if it is, would I then be able to put it into the All Clients tab, and have it auto sort by the incdient reference column? As this is an autogenerated reference for all of our clients.
I am having problems getting a formula to calculate the max if it meets 2 criteria. MAX of time listed by 1/past a certain date. 2/are of a specific client.
Here is my formula...
{=MAX(IF(All_Tickets_07012014!$D$2:$D$155685>1/1/2014,IF(All_Tickets_07012014!$O$2:$O$155685=$A2,All_Tickets_07012014!$G$2:$G$155685)))}
The data...
$D$2:$D$155685 - is the list of dates per ticket.
$O$2:$O$155685=$A2 - Are a variety of clients, where $A2 is a name of one of the clients.
$G$2:$G$155685 - Being a range of time stamps.
The formula runs, however it only shows the MAX of all entries, not within the specific date range...
I am building a workbook for small group of people in my office to use. I have 7 tabs, the first one is called Main Sheet and the other tabs are the names of each person using the excel workbook. Every tab looks the same, I have the columns labeled: date - job number - job name - contact # - comments
My desire is that each person will be able to input their data on their tab as they receive new jobs, and the main sheet will be a compilation of everyone's sheet. So to clarify, as new information is added to one tab this entire row of information will automatically be added to the next available spot on the main sheet. We are all on the same server so we can all work off the same file. Is this possible to accomplish?
I have an identical worksheet for all the days of the month - i.e. the May workbook has 30 identical worksheets. I am wanting to pull only certain rows from each worksheet onto a master. Something like - copy row from all worksheets where that rows cell "D" has any value other than " ".
I have used the following macro to pull all the data from every sheet onto a master - but i don't want all the data - just the qualifying data.
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "ALL"
I am not sure what the range "A1" is - i am thinking i can modify it to only grab certain rows.
In theory I know what I should do, but I don't know the syntax. So here it is:
There are 1450 unique records in my XLS, every record contains 45 different rows(these are the phases) with their position(1....45). Every row has a status (Not Started, In Progress, Complete)
COLA(uniqueid)....COLi(it is a number, it is the position).....COLN(status)
id1....1......status
id1....2......status
id1....3......status
.
. .
. .
id1 ....45.....status
id2.....1.....status
.
.
.id(n)
basically I would like to get the last "in progress", If not found, the last "Complete, If not found then the first "Not Started". and put a "Y" right next to the row to a new column for all the groups(45 rows)
s/s has 325501 rows. Column C contains names of people (whether present or not -I enclose small attachment to illustrate).
Column J contains scores (if present). I need column N to list the last row number where each column C name scored points (not just when heshe was last present). I think I need macros which I can fill down both columns (??).
In the attached Exel work book I have work sheets named
Material Usage – Usage of materials
Estimate - calculate the Total material cost for a job
Material Cost – defines the material cost for each material type
In “Estimate” worksheet Job number is repeated but sub jobs falling under a particular job number is unique. Materials used for each sub job is different.
Once the job number is selected from the list box , I need to calculate the total material cost for each job. I tried sumif function but I don’t know how to get it to look up for each material type and get the sum .
I have a spreadsheet with multiple data tabs feeding a few summary tabs.
For this question I will deal with one summary tab and two data tabs, one data tab for sales and one data tab for service. On the summary tab, I want to look at vehicles sold during a certain time period-(looking to the sales tab). For each of the records in the sales tab that match the date range, I need to count the repair order activity on the service tab. The key between the summary and sales tab is the sales date, and the key between the sales and service tabs is the vehicle serial number.
I currently have multiple columns on the sales tab to perform the counts from the service tab, thus allowing me to pull the information to the summary tab. Problem is that the spreadsheet has over 2 million calculating cells and tends to take excessive amounts of time to recalc.