Extracting Data From A Table - Multiple Criteria
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
ADVERTISEMENT
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
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
May 6, 2013
I am attempting to populate multiple rows in one column with data from another table. I need to get the correct street address using multiple values, i.e. first name, last name and city, as some of the names double up.
Is there a way to do this? I have pasted below an example of what I need done as reference.
Last Name
First Name
Address
[Code]....
View 4 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
Aug 27, 2012
I have a problem whereby I have a list of data that has a date, a transaction and a balance. I want to be able to rearrange this data into multiple tables, one for each month.
The pictures below explain the situation better, on the left is the list of data I need to sort, and on the right is how I'd like the data displayed. So for the May columns I would like to display all transactions that happened in May and, depending on whether it is an income or an expense, the amount in the corresponding column.
i.e. so the finish result looks something like this:
View 9 Replies
View Related
May 14, 2013
I have a table that looks like the following, only it's actually much larger:
_Red Blue Green Blue
A 2 4 2 3
B 5 2 1 1
C 3 1 2 5
D 2 3 4 2
As an example, I'm trying to sum all cells that match Blue and C. The answer should be 6, but I always end up with either zero or #VALUE.
View 4 Replies
View Related
Jun 3, 2014
I would like to summarize information from multiple tables (across multiple worksheets) into one table using a formula.
I have Attached an example: Book6.xlsx‎
The 'option plan 1' tab shows multiple summary tables for different types of clothing. (the type of clothing is text in the top left hand side of each table just under the month i.e. "jersey tops"). This tab is an example of 6 similar tabs that come from 6 different workbooks...
For each clothing type (i.e., Jersey Tops) I would like to extract the TTL OPTIONS no. and the TTL UNITS no. for each store and show these on the table in the "front Sheet". against the clothing type.
Is there a formula that can match the Clothing type from column A in Option plan 1 tab with Column C in the the front sheet tab and subsequently Match up the TTl Units and Options for each store on the front sheet tab.
I have manually keyed in what I would like the formula to show for 'jersey tops' on the Front sheet tab.
View 5 Replies
View Related
May 6, 2014
I have a set of data (assume 2 columns, one with a long name and the second with a time). The names contain variables that must be used as criteria (a single entry may contain "Blue" and "On"/"Off") and times vary, based on when the Name turns "On" or "Off" [in minutes: 25 (On), 47 (Off), 89 (On), 100 (Off) and 137 (On)]. I need to create another table that automatically inserts values for all times, to include the missing times (0 min thru 24 and 26 thru 46, etc.). EX: If the first entry is "Blue-On" at "25 minutes", the cells from 0 minutes to 24 minutes are each "0" and become a "1" at 25 minutes - the following cells are "1" until 47 minutes (where it is turned off). (1 and 0 represent "On" and "Off", respectively)
How can I created a formula to insert the correct numbers into the correct places? I'm willing to have multiple cells with formulas and simply hide the columns that are doing the calculations.
View 1 Replies
View Related
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
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
View Related
Jun 18, 2009
I have table that has a list of agents (Colum B) and then corresponding appraisal dates; 3 Month (Colum C), 6 month (Colum D) and annual (Colum e).
What I would like to be able to do it to have something that looks at the table and tells me all the 'appraisals' that are due the same month we are in, and the date that it is due, as well as the 'agent' that is due the apprisal.
I have played with the INDEX MATCH formulas and also pivot tables but have not found a solution, is it something that is going to require VBA or is there a simpler solution?
View 8 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
Oct 9, 2008
As per the example, I have a spread sheet comprising 4 pairs of columns headed Option and Grade (numbered 1 to 4). Col A is the Name of a person, cols B to I contain option (subject name) and grade data. Not every option and grade pair is used and the distribution is random.
In each used pair the grade is always one cell to the right of the subject. For each person name, I want to extract all of the grade data and place it into a separate table having separate columns for each subject i.e. who got which grade for the range of subjects.
By using the lookup function, I am able to identify where the subject and grade pairs are for each row. I have been unable then to move one cell to the right to extract the grade letter. I have thought of combining OFFSET with LOOKUP but can’t make it work.
View 2 Replies
View Related
Feb 10, 2014
I have some data, in a xlVeryHidden sheet, with multiple headers. An example is given below.
I would like to auto-generate a table on a different sheet with only revelent headers and filter by source and month/year. Basically, I want to write a macro that would make this table for, say, January and source1 only, without headers 1, 2 or 3.
(Very similar to the Pivot table, only without combining the data)
--------------------------------------------------------------------------------------------------------
Source
Type
Ref
[Code]....
View 1 Replies
View Related
Oct 25, 2013
I have a pivot table whose 'output' (databodyrange?) is a nice, rectangular, 2D array. The table has many filters, each with many possible selections.
I'd like to write a function, whose parameters are the filter selections and the pivot table/data, that outputs the corresponding 2D array.
e.g. I'd call it with something like myFunction(args that determine the filter selections, myPivotTable)
Is this possible without calls to a macro? If so, how? If not, where should i look?
View 9 Replies
View Related
Nov 29, 2007
I work in my Client's office and assist in settling construction disputes. Part of this work is to browse/search their server for documents that may assist in strengthening their case.
During this review I have found an excel document which is a text-only version of a pivot table, ie someone has done a copy, paste special, values into this sheet. I need to extract the original source data from this table back into the list format, as the original source of the data cannot be located
The row titles on the left are activity descriptions, the column headers are dates and the data in the body of the table is hours. As an idea of size the data is spread over 213 columns and 45 rows. Their are more blank cells in the table than entries.
What I would like to do is create the data in it's original form ie
Column A; Date
Column B; Activity Description
Column C; Hours
and have a separate row for each instance of an entry of hours from the pivot table.
View 9 Replies
View Related
Aug 8, 2012
I there a macro out there that will extract data from the same range oif cell in of the sheets in the workbook
For Example
Range A3:B7
From every sheet and put in a new sheet
View 9 Replies
View Related
Mar 31, 2014
I'm trying to create an online order form using google forms. In my response spreadsheet I have an cell containing:
22" - Size 0 (£52.50),
24" - Size 2 (£60.00)
I desperately need to be able to extract the amounts within the brackets across to the next cell and in an ideal world add the amounts together. There could also be more than two sets of brackets, depending on the order being placed.
Is there a formula that can achieve this? All the ones I have tried will only extract from one set of brackets or will do both but then also include the string between the brackets too.
View 4 Replies
View Related
Feb 15, 2013
I have data stored in mixed cell that i need to extract to different cells.
Q/K code: AZVR Name: "AzVR" Ltd Nominal: 0.1 USD
R. Number: AZ3570011 Category: ABC
So i need each of AZVR, "AZVR" Ltd, 0.1, AZ3570011 and ABC in different cells. The characters could be in different lengths. Is there any formula that can find Q/K code in text then write characters after it until space or Name starts?
View 2 Replies
View Related
Feb 15, 2013
I have data stored in mixed cell that i need to extract to different cells.
Q/K code: AZVR Name: "AzVR" Ltd Nominal: 0.1 USD
R. Number: AZ3570011 Category: ABC
So i need each of AZVR, "AZVR" Ltd, 0.1, AZ3570011 and ABC in different cells. The characters could be in different lengths. Is there any formula that can find Q/K code in text then write characters after it until space or Name starts?
View 2 Replies
View Related
May 31, 2014
how to extract data in the following case: I have the following columns:
Employment Name
code1
code2
cost1
[Code]....
how can I do this using formula ? For one sheet and for extracting this data from multiple sheets into a new one in the same workbook?
View 6 Replies
View Related
Jul 4, 2008
I have circa 500 sheets (all with identical layout) in the same workbook. All the data is displayed in Columns "A" and "B". The entries in Column A are variable (5-6 digit numbers) B contains numbers between 1 and 200.
I would like to be able to add together the sum of the numbers in B against an entry in A- I have a list of all the entries in column A.
The data in column A is in ascending order but may not always be in the same cell reference.
E.g. "123456" and appears in 3 sheets
(sheet2) A9 ="123456", B9= 5
(sheet 234) A111="123456", B111= 7
(sheet 456) A87 ="123456", B87=3
"123456", total 15
View 9 Replies
View Related
May 1, 2013
I have a table of data from which I would like to extract the information which is listed horizontally.
Column A Column B Column C Column D
Code Name Address Locality
SMITJOHN John Smith 123 Bell Rd Perth
On the 2nd worksheet I have a form which I have designed to which I would like to extract the information from the 1st worksheet.
I am hoping that it is possible to start entering a code based on some reference on the Name. Eg SMITJOHN
Once the code is true or correct it will populate certain cells on the 2nd worksheet automatically.
I work in transport and am looking to streamline some of the paperwork and am trying to do it within the means I have available to me.
The format of the 2nd worksheet is;
CODE: SMITJOHN
NAME: John Smith
ADDRESS: 123 Bell Rd
LOCALITY: PERTH
View 3 Replies
View Related
Sep 22, 2009
I'm trying to synthesize a fair amount of averages response time data from about 300 separate worksheets into one master sheet and I'm wondering what the best way is to do it using Excel's macros.
In each subject condition-worksheet there are 24 cell items that need to be pulled and put into the master worksheet; there are 2 conditions, for a total of 48 line items.
In each subject file, the subject number is in cell A1, my first desired average is in M7, then M15, M23, M31, M39, M47, M55, M63, then it repeats back to M9, M17, M25, etc..
How can I create a macro that creates a new line for each subject (starting with row 2) first with their subject number (A1 in all subject files), then M7, M15, M23, M31, M39, etc. moving across the row?
View 9 Replies
View Related
Apr 27, 2014
The link to my file is: [URL] ........
I am using excel 2003.
My query goes like this:
I need to find out "total lifted quantity"(i.e. the sum of 'first state lifted quantity'+'second state lifted quantity'+'third state lifted quantity') for each "state" for each "size" and each "grade".
However when I am applying a pivot table (as seen in Sheet1) it is not giving data in lucid form. I don't want to split the data for three states in three different tables (as seen in Sheet 4).
View 1 Replies
View Related
May 17, 2013
I have attached a demo spreadsheet to define the problem.
I wish to populate column K (Risk Rating) with data retrieved from the table, based on the corresponding information from Columns I & J.
e.g. I5 + J5 = Short term illness or injury + Unlikely. This corresponds to cell E9 (11) in the table so required response in K5 would be 11.
Help Book1.xlsx
View 1 Replies
View Related
Nov 10, 2009
I have been working on a variation of this for a long time and I cannot get it to work.
I am trying to depending on the textbox selection get the proper ROW from DEALLIST to show up.
View 2 Replies
View Related
