Pull Data Based On Two Criteria?
Nov 14, 2012
i have a sheet that i have been creating to pull information based on two things. The criteria is an emplyee # and the second is a date.
The data needs to be organized onto Sheet4. The Employee # will already be on Sheet4, so i have been trying to use that with Vlookup / hlookup with no success as i can't seem to get it to find the correct Date and place the data correctly.
Sheet4 layout:
Completed (with respective data from Sheet2)
A
B
C
D
E
1
[code]....
the data is located in Sheet2 (Emplyoee # is always in Column A, and the Date is always in Column B).
the data needs to be pulled into Sheet4. Essentially making the horizontal data of Sheet2, Vertical on Sheet4 under the respective date column.
Sheet2 Layout:
A
B
C
D
E
F
G
H
I
J
[code].....
View 3 Replies
ADVERTISEMENT
Feb 8, 2014
I have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...
First I was using a msgbox function to get the info:
HTML Code:Â
For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b
But it can be up to 30 different new employees... and that is time consuming.
I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....
HTML Code:Â
Private Sub CloseButton_Click()
Unload UserForm1
End Sub
Private Sub ComboBox1_Change()
[Code] ......
View 2 Replies
View Related
May 27, 2014
I'm trying to pull in the Median based on two different criteria, I've attempted to do so with the following formula:
{=MEDIAN(IF((($J$5:$J$2951=$AO3000)/($AP$5:$AP$2951=$AO$2998))*($AL$5:$AL$2951""),$AL$5:$AL$2951,""))}
This is only returning an error value, is there a discrepancy in this formula?
View 5 Replies
View Related
May 31, 2014
I am trying to pull a value from a report based on two criteria. I want to get the most recent date that has a value in Col K
1
Col F
Col G
Col H
Col I
Col J
Col K
2
106
ET
Product A
14-May-2014
[Code] ........
I use
{=IF($K$2:$K$60000>0,(MAX(IF($F2:$F$60000=C2,$I$2:$I$60000))))}
On another sheet (I left the sheet names out of the formula to make it easier to read) with C2 as the matching Col F value and it will return the most recent date, May 19th, not the most recent date with a value in Col K, May 18th. The report itself is thousand of lines long and has a couple hundred unique values for Col F.
View 3 Replies
View Related
May 15, 2013
I'm trying to use DGET to pull coordinate pairs from a list and match them up to a label based on certain criteria. I can't seem to get it to work. I'm also thinking that the way I have it set up, the coordinate pairs might not stay together and that has to be the case. My criteria is multiple cell based too and I don't think DGET can handle that.
For example, In order for a coordinate pair to be labeled "B" the x coordinate must satisfy a condition and the y coordinate must satisfy a different condition. Can I use DGET with the AND function?
View 6 Replies
View Related
Jun 16, 2014
I have a spreadsheet with 12 tabs (one for each month of the year). What I need is a macro/function that on execution will pull all rows from each sheet that has the word "overdue" in cell E from E9 down. I need the whole row of data being taken into a new sheet.
So for example, in each sheet there could be the word overdue appearing in 30 out of 500 rows I need those complete rows (A to Y) being put into another sheet for ease. At the moment I am filtering each sheet and copying and pasting into a new sheet for each bloody sheet (LOOOONG way).
The worksheets are titled: Jan 14, Feb 14, March 14, April 14, June 14, July 14, Aug 14, Sept 14, Oct 14, Nov 14, Dec 14
View 6 Replies
View Related
Jul 24, 2014
Looking for a formula to accomplish the following:
I'm trying to populate cell A31 on a worksheet titled "VolumeTotals" with the data in Cell E23 from a worksheet titled "CurrentCustomers" if the merged cells F3-F22 on worksheet "CurrentCustomers" are equal to the word "Contract".
View 6 Replies
View Related
Apr 23, 2014
I am trying to have my spreadsheet pull data from one tab and cell if they match another.
Tab "Ag Orders" column H, I enter data in lets say cell H37. Whatever I enter, if it matches tab "reference List" C:C, then "ag orders" column G (specifically G37) should enter the data in "reference List" D:D
So since in H37 I entered 34000 - 314620, G37 should pull in Farm Operations since on the referene list tab that string of numbers (C11 matches up with D11)
a little confusing but I hope it makes sense.
13-14 budget-AG.xlsm
This isn't the exact formula I used but something like it and it wasn't working. It wouldn't match the data up correctly on a few of the department names
=IFERROR(LOOKUP(H35,("11000 - 311010","11000 - 311020","11000 - 311510","11000 - 311610","11000 - 312010","17800 - 311010","17800 - 311510","17800 - 312010","34000 - 314610","34000 - 314620","34000 - 314640","34000 - 314660","34000 - 314680","34000 - 314690","81008 - 840008","81009 - 840009","13863 - 312040","13864 - 312050","71060 - 521620"),
[Code] ..........
View 1 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
Apr 22, 2014
I'm compiling data from field reps that comes in a big spread sheet. I want to pull the rows out that fall into a certain date range. For example, if it falls between January and March. How would I do this?
Company 1
3/5/14
$54,000
Q1
Phase I
10%
Company 2
2/1/14
$16,000
Q4
Phase II
80%
Company 3
12/1/13
$18,000
Q2
Phase I
20%
View 4 Replies
View Related
Jul 10, 2014
I have a bunch of different worksheets that I need to pull a single cell from (lets say A1). On my summary sheet (first sheet) I have a list of all the worksheet names. Is there a macro that can pull data by referencing their names in the summary sheet.
Example: Column A has a list of all the worksheet names in my workbook. I want to extract cell A1 from each worksheet based off the name in Column A and be put into Column B. Otherwise, you would have to go through each tab and link it (I am working with 100+ tabs).
Column A Column B
PIN
CID
SIP
View 9 Replies
View Related
Nov 8, 2006
I have 2 worksheets (Sheet1 and Sheet2). Sheet 1 contains around 3000 records with multiple fields (columns). Column A contains the ID number. Sheet 2 contains some records with different fields except for the ID number which is common.
I need a macro to retrieve information from sheet1 and copy it to sheet 2 based on the ID Number; i.e the macro needs to get the ID number from sheet2, locate it in sheet1 and copy the data from column D in sheet1 for that particular record and paste it in sheet2.
View 7 Replies
View Related
Feb 4, 2013
Here is what I have and am trying to do:
I entered the date and day in this format in multiple sheets in workbook 1 (named 0101, 0102, 0103) from the start of the year to last night (B2:C4):
Date:January 01, 2013
Day:Tuesday
I then entered all the data needed below that (B5:E26):
TimeTransactionsTotal SalesAverage Sales
12:0039 $352.97 $9.05
13:0053 $416.98 $7.87
14:0044 $339.42 $7.71
15:0043 $304.96 $7.09
etc.....
In workbook 2, I have sheets named for each day of the week. I would like to take the information from the previous three weeks for each corresponding day from workbook 1 and put the averaged data into another table with the data all formatted the same in workbook 2. This way, each week when I go into the aggregate file (workbook 2), I can look at the chart I'll create and see the average of the past three weeks transactions, total sales, and average sales for each hour of the business day and be able to staff accordingly (assuming the trend continues).
View 1 Replies
View Related
Jun 15, 2006
I am trying to make Excel pull data out of a closed file based on a date range. The closed file is a log where people input data and enter the day they are doing it. I need to pull this data in my file and make it print into my worksheets so I can pass a report on to others with only the data they request from the log.
The range would be for 7 days and would have to be able to choose a few columns off this report.
View 9 Replies
View Related
Oct 17, 2006
I'm trying to make a floor plan which shows what seats are available depending on the time it is. So I made sheet1 = to the map (where the seats are located), sheet2 - rows = seat #'s, columns = Time (broken down by half hours). Now i have conditional format to show "t" in red (which means taken), "f" in blue (which means free), and "n" to show in gray (which means not working).
What i need is example - Seat # 200 in sheet 1 to show red, blue, or gray depending on the value in the row that corespond to this seat and the column that corespond to the current time.
View 9 Replies
View Related
Apr 28, 2008
I want to pull data from closed workbook based on cell values of open workbook of column B and the source file name is on cell J1. Actually I save monthly files and opening balnce of current month should take vakues from previous month file.
Suppose current month is May 2008. Then Column Column D for May month shold take value from column G of April 2008. For simplicity the previous month’s name and thus source file name will be placed on cell J1.
The code should loop from column B of source file and current May 2008 file and should pull values for only those items which are in the current file in the Column B. Thus those products which are deleted or newly added item in the current item should not copied. Though for new item no name will be thre in the source file but for deleted items the item might be there in the source file but the code should ignore those value.
View 4 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
Aug 8, 2014
I use my workbook to track sales data from one store to the next. I use my workbooks to compare data from year to year. Each year's data is displayed on a separate worksheet. '2013' has 2013's data, '2014' has 2014's. On the 2014 sheet, I have a Prior Year's Sales that pulls data from the previous year's spreadsheet using a formula which I just drag down each day I enter sales. I would like to automate this process and have the VBA code check for today's date and automatically pull the previous year's sales data from the '2013' sheet and put it in the appropriate cell on the '2014' worksheet. I hope I have explained this well enough to understand. I've included a link to my workbook for reference.
I had to use dropbox since I can't post a file over 1 MB. The file size is around 1.25 MB.
View 5 Replies
View Related
Aug 25, 2009
I'm having a little trouble figuring out what formula to use for situation. I have 5 columns of information.
Column A = School # of current school
Column B = School 1 Name
Column C = School 2 Name
Column D = School 3 Name
Column E = School 4 Name
What I want to do is in Column F to look through a row of information and find the current school.
So:
If A="1", pull from B
If A="2", pull from C
If A="3", pull from D
If A="4", pull from E
I tried doing a VLOOKUP table, but I think I'd have to do one for every row, but I could be wrong. I also thought of INDEX(MATCH), but I couldn't quite figure it out.
View 2 Replies
View Related
Sep 13, 2012
I need a Macro which pulls the data from different sheets of excel (which is not formatted properly) to Main Sheet. Also some of the columns will not have the same names, so macro should handle this exception as well.
View 8 Replies
View Related
Sep 15, 2005
Item Quantity
A 2
S 7
D 3
F 6
I am looking for a ranking formula that will pull the data from the
first column based on the ranking of the second column
so that the end result will look like this
1 S
2 F
3 D
As S has the largest quantity, F 2nd largest etc
The list I will pull this from is variable in length but in the
hundreds.
View 9 Replies
View Related
Dec 22, 2013
I would like to pull all rows and columns from a table and copy them to a separate sheet. I would like to pull the data if a certain value is present in a particular column. Essentially, it is the same effect of filtering the data but I don't want to have to keep filtering and copying specific cells and pasting them into a different sheet if I need to change the raw data.
Example: I would like to copy all ids, store, and partner information IF the partner is "X". I would like to put this information in Sheet A.
Capture.PNG
View 2 Replies
View Related
Aug 31, 2006
Hi, have a question regarding dynamic charts, specifically I am trying to create several dymanic charts based on data in a single column (So i can only say my first set of data will be starting a a set location in the chart, the rest has to shift down based chart data above it). This data should organized in charts depending on the data in several other columns. For instance, need to pull out values from column based some ID and some PartNumber. A sample excel or VBA would be much appreciated. I know VBA but do not know Excel VBA much.
View 9 Replies
View Related
Jan 11, 2010
I'm trying to fix a report that I created but can't seem to figure out a way to display the information for a single student.
I've attached a mock report up so that maybe it would make more sense.
So the first tab, 'DIR', I managed to find a way to pull up only the information for a single student that was selected in the drop-down D1 cell. For that I used a simple INDEX/MATCH combo. The student's name would only appear once in the 'Paste DIR' tab, so it was fairly simple to create these formulas.
Now for the 'Paste Outreach' tab, a student appears multiple times in the chunk of information. Right now, the way that I have the report is so that it would just pull the information from each row, regardless of the student. What I would like to do is find a formula similar to INDEX/MATCH, but find every instance when the criteria is met for the INDEX/MATCH. I would like to be able to log each instance a student has an Outreach log entered. Then if I switch the student selected in D1 of the DIR tab, then it would only populate their Outreach Logs.
So if Student Name & Outreach # matches D1 ('DIR' tab) & "90" in "Paste Outreach, then pull row info into rows 7-11 in Outreach.
Find the next instance Name & # matches, and pull that into rows 12-16, etc.
View 4 Replies
View Related
Jun 26, 2013
So I've got a vertical list of dates in M/D/YYYY format, and I'm trying to come up with a method to pull certain dates that are greater than a given benchmark and paste those values in another column. For instance, if I've got a list with 3/3/2008, 5/5/2010, 6/6/2011, and 7/7/2012 I want to pull only the dates beyond 12/31/2010 (so would be the 6/6 and 7/7 ones).
I'm assuming this isn't doable with one formula, so I would need a macro. But I'm having trouble just even coming up with the logical operator for just one date. I must be doing something wrong here. If my date in B2 is 1/1/2009 and I have a formula as IF(B2>12/31/2009,"True","False"), I just get TRUE, which shouldn't be the case.
View 6 Replies
View Related
Jun 28, 2010
I have a data sheet with 20 columns and about 300+ rows. In the results sheet there are 3 criterias (from column A, B, H in datasheet) which are in the form of dropdown in cell A1, A2, A3.
I would like to extract the data from the data sheet based on the criterias selected from dropdown list, to results sheet starting from row 6, with only 12 headers out of 20 (non-continuous, e.g. columns A-C, E, G-K etc.)
View 3 Replies
View Related
Dec 19, 2012
generating a formula that takes into account a range of values (an entire row) and from this row, I would like the formula to select, for example anything greater than 80%. After the formula selects anything greater than 8, I would like for it to select cells that are above or below the cells that have values greater than 8.
1
2 JLKNSTTP
3 85934942
4
5
For example, in the above datas, I would like a formula to select anything greater than 8 in row three and select cells above it. In this example it would be j, k, and t.
=if((3:3>8),offset(A3,-1,1,1))
View 1 Replies
View Related
Jul 16, 2014
I need to populate data based on criteria.
The criteria has been attached as follows.
Sample Test.xlsx
View 2 Replies
View Related
Feb 16, 2009
I need a data validation to prevent entries when they are > 50, but only if the value of another cell in col A is "Payment". The value of 50 is an example, I have another formula to get this number using VLookup.
The idea is that if the value of the cell in col A is "Income", I don't want the entry to be rejected. But if the value in col A is "Payment" or "Transfer" and > 50, I want the entry to be rejected (if I only have $50, then the payment cannot be > 50).
View 7 Replies
View Related
Feb 18, 2012
I have a problem and I hope that it can be solved using a macro. Basically, I have a set of data in column A and B in sheet(1):
A
B
1NameDate
2JOY12/09/11
3MAR12/12/11
4DAVID12/12/11
5
I have another set of data in Sheet(2):
A
B
C
D
E
1NameDatesalary
CHARACTERRATINGS
[code].....
basically, I would take into consideration the names and dates in sheet(1) and if it matches sheet(2) then it would then paste their salaries, characters and ratings into sheet(1).
View 9 Replies
View Related
