Extracting Certain Data Depending On Criteria
Sep 8, 2009
Have a perfect working spreadsheet, but now need to extract certain bits of data depending on one criteria.
Each row contains data in different columns.
What i need to do is depending on the criteria of a cell (i.e. whether its Y or N) i would like to extract certain cells onto another worksheet all within the smae work book
i.e for example
If i have columns A,B,C,D,E and F and then x amount of rows (as these can change as data is entered) if a Y is entered in F then it extracts the data out of columns A, C and D
View 14 Replies
ADVERTISEMENT
Aug 4, 2014
I have created a table of data with Serial No, Name, Age & Common Friends field. Now i need to extract all the data (complete Row item) where the condition matches a certain criteria. Basically a formula or function which will say what are the names of people along with all other corresponding fields (Age & Common friends) who have common friends as "Jill" or "Jack" (For example). Note : I cannot use filter option and copy/paste the filtered data in new sheet as i am working with thousands of data items. Due to the nature of report software/data extraction report, multiple data items appear in single cell only (Multiple friends name appear in single cell).
View 3 Replies
View Related
Nov 9, 2009
I Have want two find the dates and the data of these dates
depending on the two criteria i have
note"the two criteria would be left(b3,4) and right (b3,4)
ex
in f3"date" i want to find the dates correspond with these two criteria
and then find the data of these dates in G3 Depending on the two criteria too.
Sheet1
ABCDEFG2DateData
Criteria1Criteria2DateResult31/1/2008M111 GG1G M113
M191M1131/2/2008M191 G1GG M11341/2/2008M191 G1GG M113
1/5/2008M191 G78GG M11351/2/2008M111 GG1G M113
1/8/2008M191 G1G9G9 M11361/3/2008M141 GG5G M113
71/4/2008M118 GG6G M113
81/5/2008M191 G78GG M113
91/5/2008M141 GG5G M113
101/6/2008M111 GG2G M113
View 9 Replies
View Related
Oct 24, 2009
I'm using Excel 2007 and I'm looking for a way to extract certain records/rows from a table and to have that data copied to another area of the spreadsheet. I actually want to extract Invoice amounts according to a specific account number so that I eventually end up with all the invoices for a particular client on another tab so that they can be printed out as a kind of client statement.
I know there are ways to filter and then copy the records/rows to another tab but I want it to be automated as far as possible. What I really want to do is to "pull" the records into a new tab by using some kind of formula in the cells where I want the data rather than having to copy it there. I know how to sum data for this kind of thing but what I'm really struggling on here is being able to "Display all the records in a seperate area" first before I eventually add formulas to sum.
I don't mind the records being coppied if the process is automated but don't really want to get into macros as writing macros isn't my strong point.
I've tried using "VLOOKUP" but it will only return values for the first data item it finds (Account number in my case), whereas I really need it to continue to return values for the data item it finds if there are multiple instances of it!
View 14 Replies
View Related
Apr 20, 2009
Suppose I have a list of data in which I want to retrieve or extract specific row from the list as per operator name & relative comments (Done or Not Done) from the given list as per the operator name, now what I want that my VBA code should be able to get the operator name & the relative comment data from the list according to the operator name in a new sheet. My data keeps changing so I need a code which can work on any size of the data. I need to retrieve only operator name & Comment column in a new sheet to run my Pivot table.
My Excel data is something like in the attachment & the result I want through VBA Code.
View 15 Replies
View Related
Jul 31, 2008
I have a data table that has the following structure
Team manager Warrington 10 12 14 16
Team manager Liverpool 8 10 11 12
Commercial Manager Warrington 25 28 32 33
I need to extract the data out into a flexbible summary table where i can choose the job title, location and year.
I can use the following index / match formula to extract the salary that matches the job title and location.
(INDEX('Emp In'!$c$5:$c$130,MATCH(1,('Emp In'!$A$5:$A$130=$B3)*('Emp In'!$b$5:$b$130=$C3),0) - Where b3 = job title, C3 = location, Column C = Salaries year 1
However I cannot add the flexibility to choose a year as this formula fixes the index on a chosen column (yr 1 column C is this instance)
View 9 Replies
View Related
Sep 27, 2013
I have data and after doing some data manipulation it looks like this
Job #
Part #
Lot #
Total
Grand Total
[Code]..
What I'm trying to do is making a summary sheet that has only the first record with unique values using the Job number, part number, and lot number as the criteria. The grand total in the first record is the total for all of that job,part,lot so i need to bring that value over to the sheet as well. I have been able to do this easily with the duplicate finder, but need a formula to automatically do this.
View 2 Replies
View Related
Jan 2, 2013
I have sheet full of data containing results of multiple tests on various equipment.The sheet contains many columns of data but below are the specifc criteria i want to use to extract the data. As maintenance is carried out regularly the list is always growing. I want to create a dashboard summary of the "Machines" which i will colour using condition formatting. I will list the machines in the columns and would like the rows below each machine to be populated with the results.
Column 1 Lists the various factories
Column 2 Lists the Machine
Column 3 Lists the Part
Column 4 Lists the result.
Results can either be "ok", "warning", "Alert" only
View 6 Replies
View Related
Mar 20, 2014
I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
View 4 Replies
View Related
Apr 7, 2014
Aim - Extract every word after: and before , and put them in the appropriate headings
I have attached my desired results in column B - E. To calculate the answer, the heading of the tile is normally just before the : Meaning the Answer is after the : and before the ,
View 2 Replies
View Related
Jul 27, 2012
I have a table with several criterias (Name of supplier, date, amount paid to this supplier). This table is my inputs. Each time I have an expense, I put down the name of the supplier and the amount of the expense and the date when it occurs.
I have another table with name of each supplier in rows and the date in column. Sometimes I have 2 amount for a same supplier for the same month. I would like this amounts sum in my table. I cant find a good formula to do it efficiently.
Obviously I can do a sum
(if(A2=B2, if(c6=c8);b10)+sum(if(A2=B2, if(c6=c8);b10)+ etc...
But I can 150 expenses per month...
View 2 Replies
View Related
Dec 6, 2007
I have attached a spreadsheet.
I have a dump of about 4000 calls (I have minimised the attachment for size sake) that I need to do certain reports on.
The second sheet (dump) has the data in its raw form from our call reporting tool. The 1st sheet (Report) is a broken down version the powers that be wish to see.
My formula needs to meet certain criteria, as you can see I only have 6 columns on the (Report) sheet where as the dump has lots !
The (report) sheet needs to list the Parent Case ID only if the Summary column has BSR at the beginning, if it meets this criteria then it lists the other relevant columns in the (report) sheet.
View 5 Replies
View Related
Jun 27, 2012
I have a table such that column A has a list of names, column B shows either "Yes" or "No" depending on if that person's info needs updating. I am trying to find a way to a populate a list (prefereably in column C) that has only the names of people from column a that show a yes in column b.
I know that i could just do =if(B2 = "Yes", A2, "") and then drag the formula down, but that leaves blanks in between the cells. If column A has 10 names and only 3 are Yes then I only want the first 3 cells in column C to be populated with the corresponding names.
I think I need to use a combination of INDEX and MATCH functions but i dont know how to do that with a criteria that falls in another column.
View 14 Replies
View Related
Mar 20, 2013
I have sales rep data with column headings as Sales Rep, Sales Date, Sales Item Code and Customer ID. There are only 2 sales reps in the data, I'll call them Rep A and Rep B. I need to extract rows where Rep A and Rep B have made sales on the same day to the same customer (it doesn't matter what type of sales). I don't want to extract sales by only one rep, it must be both reps on the same day to the same customer. I have tried a pivot table but it identifies all sales. Can this be done?
View 2 Replies
View Related
Oct 29, 2013
There is no where else for me to go. My problem is: I have a table with over 30,000 rows and columns A - W. The first column A contains UNIQUE ID. Column G contains CATEGORY CODE.
Example.png
Since Unique ID (column A) can be listed more than ones, I need to select only those records (rows) that correspond to the following: Category Code (column G) is either 14, 15, 16, 17, 18 and not any other. I hope you can see the attachment, UNIQUE ID = a;does not satisfy my criteria as it does contain Category Code 14, it also contain other codes. The final result from the sample provided would be extracting data for UNIQUE ID = e (since it is the only record that does not contain any values other than 14, 15, 16, 17, 18).
View 6 Replies
View Related
Nov 15, 2013
I want to extract text from column A into column D,E,F based on condition on column B. I am able to do this by IF statement, but the data is not continuous. I want to extract continuous data into D,E,F columns without any gaps.
Condition for Column D: >1
Condition for Column E: >0.5<1
Condition for Column F: <0.5
I am attaching the sample list for reference.
View 7 Replies
View Related
Jan 3, 2009
Is there a way I can get a continuous list, ie no missing rows, from a much larger list. (New list in separate columns to the R of the list.) I want to extract data from column A, only if the data in col C matches my specified criteria - I want this info to appear as a list with no gaps, ie I can do this easily using "If" but there will be lots of blank rows doing it this way.
Furthermore, as I need two different lists from the same data block, I need to specify whether number (extract these to col N) or text (extract to col O).
View 3 Replies
View Related
Feb 15, 2012
Excel 2007. Is there a way to extract information from cells and rows that constantly move? These rows need to be able to cut & paste, copy & Paste, insert and delete. I have the formula I need to display the data, but with the cutting and pasting etc, my formulas get all messed up.
I have the excel sheet on Google docs. It displays some cells wrong because it does not recognize certain formulas.
[URL]
On the second worksheet, I want the information from columns A, H, M, N, and O to be extracted at a certain time each evening and sent to a worksheet, without sending duplicates. These rows will constantly move which is why my current setup, which is just to display the information, will not work... We decided to try to extract it into a different worksheet or workbook if possible.
View 1 Replies
View Related
Jun 15, 2013
I have attached two documents. One is called "Zone Destination" which is a template I designed. The other file is called "Schedule5_4" which gets downloaded from a work server once a week that contains all employees and their shifts for the entire week. What I am able to do so far is extracting the first row using index and match but I don't know how to extract any of the rows that follow. an employee might have several rows for one day based on a lunch or if they are working in multiple zones during their shift.
Zone Destination File -- start tab has the template in place that gets copied over when you create a new tab.
-- employeeroster tab contains the employee roster that i use to match with the schedule5_4 file. i changed the names and also reduced the amount to make it easier to read. i have over 80 employees but for this example, i only made up a handful.
schedule5_4 File -- this file contains all the data that I need to pull from. The criteria that I am using is by employee and date. I'm matching from the employee roster tab and also the date in cell a1 located in the zone destination file.
[URL]
View 5 Replies
View Related
May 7, 2008
I have a sheet of data, and i need to extract rows that contains dates containing 5 in them. Eg, 5 Jan, 15 Jan, 25 Jan... 5 May, 15 May, 25 May and copy them into a new sheet. Is there a faster way to do it via VBA rather than manually extracting them?
View 5 Replies
View Related
Jun 25, 2007
I'm trying to find a way that I could get the MAX value in a group of cells depending on a critieria. The situation is that I have a list of numbers and a list of dates parallel to it. I would like to find the MAX for, say, the numbers corresponding to the month of June. I would like to be able to change the month and get the MAX for each month on request.
Eg.
18-MAY-2007 600
01-JUN-2007 500
02-JUN-2007 400
28-JUN-2007 600
12-JUL-2007 700
17-JUL-2007 200
I need formula that will give me "600" if the month I want is June. Ideally I would not want to have to filter the data, so SUBTOTAL would probably not work for me.
View 10 Replies
View Related
Aug 10, 2009
The current spreadsheets add up each persons totals by matching the name in each tab with the name of the person who won the job located in service orders tab. BUT.....If two salesman pair up on up on a job then the formula doesn't recognize the joint name. eg Scott/Ash in row 21 & 22 (Service orders). I need the totals to half the job and add it to the salesmans total accordingly.
View 4 Replies
View Related
Jan 11, 2010
I am trying to hide or unhide columns E:BL depending if the the cell value in row 54 contains a value (Note cells in row 54 contain formulas). I have written the following code but get a runtime error on the else statement.
Sub UpdateCashflow()
Application. ScreenUpdating = False
ActiveSheet. Unprotect Password:="LD"
ActiveSheet.Select
Dim c As Integer
For c = 5 To 64
If Cells(54, c) = "" Then
ActiveSheet.Range(Cells(54, c)).EntireColumn.Hidden = True
Else
ActiveSheet.Range(Cells(54, c)).EntireColumn.Hidden = False
End If
Next c
ActiveSheet.Protect Password:="LD", DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.EnableEvents = True
'
End Sub
View 4 Replies
View Related
Nov 15, 2013
I am using Excel 2010 on Windows 7. Here is a link to the worksheet I have a question about : Example.xlsx
The only two relevant sheets here are "TestScores" and "Area 1." What I am trying to do is copy names over from the TestScores sheet to the Area 1 sheet based their test scores. It is important to know that a passing score is anything 85 or above. If you don't pass Phase 1, you move onto Phase 2, and if you don't pass that you move onto Phase 3.
Let me first explain how the TestScores sheet works. Here is a screenshot of the sheet:
I copied in column C, the names, from a roster sheet. Columns A, B, and D all VLOOKUP information from the roster as well. You can ignore columns E & F. Column G is just a test name that is manually inserted. They are all in the same order since this is an example worksheet I am doing the program on before I input the real data. Column H is also manually inserted, with data validation to only allow values 0-100 and a few different relevant terms (MS, EXT ABS, N/E). These next two rows are the formulas in columns I and J, starting in row 3.
I=IF(H3="N/E","N/E",IF(H3="EXT ABS","EXT ABS",IF(H3="MS","MS",IF(H3="","",IF(H3>=85,"N/A (PASS)","Need")))))
J=IF(H3="N/E","N/E",IF(H3="EXT ABS","EXT ABS",IF(H3="MS","MS",IF(I3="","",IF(I3="Need","",IF(I3>=85,"N/A (PASS)","Need"))))))
What these formulas do is the following: If N/E, MS, or EXT ABS is in column H, it copies those over to the next two columns. If someone scores 84 or below, it says "Need" in the next column. If they score 85 or above, it says "N/A (PASS) in the next column(s). The user is meant to write over these to insert the phase 2 and 3 scores.
Here is the "Area 1" spreadsheet I am working on that has the problem.
First off, I created these formulas by comparing them to another post and replacing my ranges and criteria. In column A, it returns all the names of Team Members who passed in Phase 1 (have a score 85+ in "TestScores" column H). The array formula is below and it works great.
{=IFERROR(INDEX(TestScores!$C$3:$C$1500,SMALL(IF(TestScores!$H$3:$H$1500>=85,ROW(TestScores!$C$3:$C$1500)-ROW(TestScores!$C$3)+1),ROWS(A$6:A6))),"")}
In column E I have a similar formula, except it returns the names of those Team Members who have "Need" in column I of "TestScores." This formula also works great. The formula in column D is just a VLOOKUP based on column E and works well, too.
{=IFERROR(INDEX(TestScores!$C$3:$C$1500,SMALL(IF(TestScores!$I$3:$I$1500="Need",ROW(TestScores!$C$3:$C$1500)-ROW(TestScores!$C$3)+1),ROWS(E$6:E6))),"")}
Here is where my problem is. In column F, I want to return all the names of people who passed Phase 2, which means they have a score of 85 or above in column I of the "TestScores" sheet. However, not only is it not returning the right names, I can't even figure out what criteria the names is returns has. The current list, in (mostly, oops) red, is incorrect. The formula, below, is almost the same as the formulas above, with only 1 difference in each case.
=IFERROR(INDEX(TestScores!$C$3:$C$1500,SMALL(IF(TestScores!$I$3:$I$1500>=85,ROW(TestScores!$C$3:$C$1500)-ROW(TestScores!$C$3)+1),ROWS(F$6:F7))),"")
It refers to column I, instead of H, compared to the formula in column A. Its criteria is >=85 instead of ="Need", in comparison to the formula in column E. Nonetheless, it still isn't returning the right names! The other weird thing, is if I replace the ">=85" with "=90" it will return the names of team members who scored 90.
View 3 Replies
View Related
May 21, 2014
I need to filter a large database using multiple OR criterias.
Database is CUSTOMER NAMES in each row with CONSUMED PRODUCTS following in each cell.
I want to filter two or more products and list customer names in sheet2. Like listing customers using salt or pepper Filter criterias will be entered into cells in sheet2 with results shown below and I want search to be active and alive just like vlookup function.
Sheet1
CUSTOMER NAMES---CONSUMED PRODUCT---CONSUMED PRODUCT---CONSUMED PRODUCT
GEORGE--------------SUGAR-----------------SALT
MICHAEL-------------PEPPER-----------------CHILI
JACK-----------------BREAD------------------SALT-----------------COFFEE
View 1 Replies
View Related
Jul 6, 2014
I have a column containing names, blank spaces, numbers, a bunch of miscellaneous things. However all I want to do is extract only names, into another sheet, and not the numbers, blank spaces, etc. Youi'll see what I mean when you open the attachment. Offset isn't an option because the names don't appear in the column in an organized fashion.
Dummy workbook.xlsx
View 3 Replies
View Related
Jul 24, 2014
I have a download from an accounting general ledger which has the following:
Column A: Category Description
Column B: Country
Column C: Department
Row 1: Months
Note: Each row contains the last 12 months worth of costs
As the categories/criteria can appear multiple times, and there are thousands of lines, I have been using SUMIFS to calculate totals my required combinations (eg. Travel expense, Germany, Sales department)
Every month, the information refreshes to show the most recent 12 months worth of data. And this means I need to manually update my formulas to correct the month column headings, as everything moves by 1 month.
Would something like a SUMIF with a SUMPRODUCT work? Eg. if current month = July, then it would sum everything from the July column automatically?
View 4 Replies
View Related
Oct 19, 2009
I want to colour 10 cells (A1-J1) if I type in yes in cell K1. Using conditional formatting Im only able to colour 1 cell. Is this possible without using VBA?
View 5 Replies
View Related
Jan 27, 2013
I use Windows Vista and Excel 2007.
So to cut to the chase :
On my spreadsheet, I have 6 main groups of columns, each one comprising 4 columns with their own automated calculations across the range.
Group 1: Cell H5, Cell I5, Cell J5, Cell K5
Group 2: Cell L5, Cell M5, Cell N5, Cell O5
Group 3: Cell P5, Cell Q5, Cell R5, Cell S5
Group 4: Cell T5, Cell U5, Cell V5, Cell W5
Group 5: Cell X5, Cell Y5, Cell Z5, Cell AA5
Group 6: Cell AB5, Cell AC5, Cell Ad5, Cell AE5
Then I have two columns following those: Col AG, Col AI
In each one of those two columns, I am trying to sum up a total value using The IF Function.
In Cell AG5 : the sum of the values of cells K5+O5+S5+W5+AA5+AE5,
ONLY IF the values of Cells H5, L5, P5, T5,X5, AB5 is equal to AG3 (which is a symbol (wingding 3 for capital P), this occurs 3 times out of 6, sometimes twice only, and at random, so I need the sum of 3 cells (or 2) out of 6, based on the above criteria.
Same in Cell AI5: sum of the values of cells K5+O5+S5+W5+AA5+AE5
ONLY IF the values of Cells H5, L5, P5, T5,X5, AB5 is equal to AI3 (which is a symbol (wingding 3 for capital L), this again occurs 3 times out of 6, sometimes twice and again at random, so again I need the sum of 3 cells (or 2) out of 6, based on the above criteria.
Using the formula: =IF(T5=AI3,(W5),0) , I retrieved the value of cell W5 into cell AI5; and when I tried the formula :
=IF(T5=AG3,(W5),0) , in cell AG5, it returned the value 0 which is correct again, as T5 was equal to AI3 but NOT to AG3.
But I cannot work out how to add all of the three values relating to each symbol, independently in each column, as they each occur randomly.
This is the way it looks in the spreadsheet:
Cell H5 Cell I5 Cell J5 Cell K5/ Cell L5 Cell M5 Cell N5 Cell O5/
Symbol (for P) Text Text Value /Symbol (for L) Text Text Value
And so on 4 more times across.
I could of course add it all manually for each line but I would rather have a formula to do it for me as all the other calculations are automated and/or conditionally formatted.
View 9 Replies
View Related
Jun 29, 2013
Criteria
Column B
Constant
Variable0
Variable1
Variable2
Variable3
2
[Variable2/Constant]
5
123
43
45
76
[Code] ..
This is my table. What formula should I use so that Column B shows the calculation depending on the Criteria column?
View 9 Replies
View Related
