Pulling Name If Multiple Criteria Follows?
May 10, 2013
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.
View 9 Replies
ADVERTISEMENT
Aug 13, 2012
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
View 3 Replies
View Related
Mar 14, 2012
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
View 3 Replies
View Related
May 23, 2013
I 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?
View 2 Replies
View Related
Jul 2, 2014
I have a spreadsheet that has employee name and average hours worked.. The employee is listed multiple times because each row represents a pay period with number of hours worked. the last column I used to average the hours worked. So what I need is whenever there is a value greater than 30 in column g, I need to copy the row where column G has a value. It can be copied on the same sheet or on another.
View 9 Replies
View Related
Dec 17, 2013
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 Related
Jun 3, 2014
I 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 Related
Jul 20, 2009
I have 8 individual workbooks that reside on a networked drive that I need to compile into one worksheet in another workbook. The 8 source files are in a separate folder.
I got the following code from someone on this forum, but I can not seem to get it to work exactly.
Sub CopyFromWorkbooksTake3()
'Declare and define variables
Dim objWB As Workbook, SourcePath As String, wbName As String
Dim LastRow As Long, NextRow As Long, DestSheet As Worksheet, i%
NextRow = 2
SourcePath = "C:/Your/File/Path/"
Set DestSheet = ThisWorkbook.Worksheets("MasterList")
'Identify the directory of interest
On Error Resume Next
ChDir SourcePath
If Err.Number 0 Then
Err.Clear...............
View 9 Replies
View Related
Dec 8, 2009
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.
View 14 Replies
View Related
Aug 21, 2009
I'm looking for is to repeat this formula within the cell, so that it can pull the information of not only the first match, but all matches proceeding it. Right now it only pulls the first match it finds, but I would like it to add every time a row matches the criteria of the formula.
=(LOOKUP(2,1/(('Paste SFHS'!$A$2:$A$9=A2)*('Paste SFHS'!$B$2:$B$9="08-Wave/Stu. Goals")),'Paste SFHS'!$C$2:$C$9))
View 5 Replies
View Related
Jun 3, 2013
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.
View 3 Replies
View Related
May 6, 2008
Got a requet from a client that we can't do with out accounting software. Basically I have a folder of all the clients indivisual invoices for 2008. We want to put together a report that will include the employee name, invoice date, and amount. But the only way to get this right now that I see is to sift though the hundreds of workbooks we have for them, one by one. Is there something that can be set up to pull 3, 4, or 5 of the same fields say cell A1, B3, C3, and D6 our of every workbook in a single folder on our network?
View 12 Replies
View Related
May 4, 2009
I need to be able to pull the information into another section of the workbook and place it into order (ie Black 1,2,3,4) and then drop down to the next row and place Blue 1,2,3,4 etc. I am using a drop down menu to select the Course, and want the times to then populate to the right, each in its own cell.
Have tried the loop code available on a few different topics here however has not quite worked.
View 12 Replies
View Related
Aug 7, 2013
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 Related
May 22, 2014
Basically, 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.
View 1 Replies
View Related
Feb 13, 2009
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.
View 9 Replies
View Related
Feb 8, 2013
I have a work book with prob close to a thousand individual work sheets in it. I have a goup of cells that are the same in ever work sheet that I would like to pull and place into a master work sheet. I dont want to copy and paste each one, I'd be a hundred before I finished and I dont have that kind of time. Im thinking a macro might be what I need but Im not very good with useing them let alone making one to fit me needs.
View 2 Replies
View Related
Feb 26, 2013
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?
View 2 Replies
View Related
May 15, 2006
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.
View 9 Replies
View Related
Jun 16, 2014
I've already entered a variation of the formula below into 180 different columns. The only variation is where worksheet 322 is referenced. Each column references a different worksheet.
Formula:
I have at least four other tables to build of the same size, and they're each going to use this same formula with an additional IF formula housed around it. Ideally I would be able to copy the table, then run find/replace, where I could substitute = with =IF(new formula, and then run find/replace a second time and sub ))) with ))),more new formula). The problem of course is in between those steps lies a formula error preventing me from running the second step. The only alternative I can think of is to build the new formula, copy it into a word document, and run find/replace 180 times to tailor the formula for each column. That's what I did to build the first table.
View 8 Replies
View Related
Oct 21, 2006
I am trying to solve a problem. I am currently using this formula
= SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)
This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.
So the info looks like this in excel
MARKETING 200612 -10
MARKETING 200701 -25
MARKETING 200708 -50
ECONOMICS 200709 -30
The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
View 5 Replies
View Related
Mar 24, 2014
I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.
View 1 Replies
View Related
May 18, 2014
Macro for AverageIFS, with multiple criteria in the same criteria range....
View 9 Replies
View Related
Jul 30, 2008
I have a spreadsheet which lists letters issued, the date issued and the potcode. I need a formula that counts, on a weekly basis, the number of letters issued to postcodes in Sutton and Bexley.
I have a count sheet set up on one worksheet and the list of postcodes applicable to each area are listed on another worksheet. I have been playing around with sumif, sumproduct etc, but these don't seem to work as I am pointing the formula to a list of postcodes and not an individual postcode. For example, the formula for one week needs to tell me, the number of letters issed to Sutton between 26/07/08 - 01/08/08. This is what I tried -
=SUMPRODUCT(('MU63 NC'!$F$2:$F$10>=COUNT!B$2)*('MU63 NC'!$F$2:$F$10<=COUNT!B$3)*('MU63 NC'!$D$2:$D$10=Sutton!$A$2:$A$50))
But I get #N/A - if I change the last refernce to a specific cell instead of a range it works, but this will make the process very lengthy as there are lots of postcodes!
View 5 Replies
View Related
Aug 30, 2012
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.
View 2 Replies
View Related
Mar 18, 2013
I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.
I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).
The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.
As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.
The log code in column "H" Is based on this criteria:
First Letter of the Unique ID in column "E" - O, M, or L
Program Type in Column "F" - U or R
1. O-U = U
2. O-R = RU
3. M-U = U2
4. M-R = R2U
5. L-R = R
You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.
This is the area where my skill at using scripting dictionaries fails.
The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words
if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")
So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)
Here is my code so far.
VB:
Option Explicit
Sub test()
Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long
Set dic = CreateObject("Scripting.Dictionary")
[Code].....
View 1 Replies
View Related
Aug 5, 2012
I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$R1"),2*(AND("'"&$H$1:$H$43&"'!$E1">"'"&$H$1:$H$43&"'!$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$S1"),2*(AND("'"&$H$1:$H$43&"'!$G1">"'"&$H$1:$H$43&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$T1"),2*(AND("'"&$H$1:$H$43&"'!$I1">"'"&$H$1:$H$43&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$U1"),2*(AND("'"&$H$1:$H$43&"'!$K1">"'"&$H$1:$H$43&"'!$L1"))))
but it returns a value of zero each time. Clearly there is an error in the formula.
Here is some background:
-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.
View 1 Replies
View Related
Aug 31, 2012
I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
TABLE 1:
Project
Benefit Type
Delivered or Enabled
Benefit
PJ1
Financial
Delivered
Saving of $4M over 24 months.
[code]....
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
TABLE 2:
Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled
[code]....
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
Continued maintenance of hardware.
Increased capacity.
View 1 Replies
View Related
Jun 29, 2014
I have a directory which contains many files, they are all names based on their locations. eg. Burwood-File1.xls,Burwood-File2.xls,Burwood-File3.xls etc
I have a master that which will contain the branch in the first column.
I have defined the directory location in a separate sheet as well as email template.
When I click on the Send Email button I want ti to attach the files that match the Branch name.
The Branch list will constantly grow.
View 2 Replies
View Related
Feb 24, 2014
Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.
In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.
CURRENT SHEET
ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89PENS ABC STATIONERY CO LLC [code]....
The attached example file may be a better alternative to view this problem.
View 2 Replies
View Related
