Extract A Value Using 4 Criteria Inside A Table?
Sep 16, 2012
I'm working on a spreadsheet to anticipate the corrosion rates on multiple equipments, based on 4 criterias. At the moment, we type them down one by one and we search for the corresponding value inside the table we have.
Temperature
CS (carbon steel)
%Sulfur
TAN
[< 50-100]
[>100 - 150]
[>150 - 200]
[>200 - 250]
[code]....
These values aren't the real ones, but it looks like that. So, if I have a piece of equipment made of CS, I look at the specific table for CS (There are about 7 different groups of materials). I consider the %Sulfur, TAN# and the specific temperature. You also have to consider that this table is repeated with different outputs for higher level of %sulfur (>0.6, up to about 3%)
EX - Material = CS, %Sulfur = 0.4, TAN# = 0.7, Temp = 152, my value will be 9 (the bold numbers are the outputs). There is no linear relation or any sort of mathematical way to obtain the output, you have to look at the table.
In my mind, this part of my spreadsheet should look just like that :
Material
%Sulfur
TAN#
Temperature
CORR RATE
W
X
Y
Z
= Output
The excel part now, I tried working with IF() to specify a table to use as the TRUE value, trying to group multiple IF statement into one, but searching values inside multiple ranges is very tedious and I cannot cover all the possibilities... Then I tried working with INDEX() and MATCH() but again, these are not exact values, they are ranges, making the work even harder. Building an output with multiple criterias that are simple and exact is easy, I just can't seem to work it out with ranges.
View 9 Replies
ADVERTISEMENT
Mar 3, 2013
I'm trying to extract a list of names from an Excel 2010 table based on two criteria, thus:
=IFERROR(INDEX(Database[FullName],AGGREGATE(15,6,ROWS(Database[Age]-ROW('Database'!$A$2))/((Database[Age]>1.8)*(Database[Age]
View 4 Replies
View Related
Dec 14, 2012
I have around 10 buttons in a sheet and all the buttons have same macro. I am trying to extract the text inside the button which I click (and not the name of the button). How to do it using vba?
I have used the following:
buttontext =ActiveSheet.Shapes(Application.Caller).Text
buttontext =ActiveSheet.Shapes(Application.Caller).Caption
PS: When I used buttontext =ActiveSheet.Shapes(Application.Caller).Name there was no error but it gave the name of the button not the text inside it.
View 2 Replies
View Related
Feb 22, 2007
I did a data table yesterday and it worked. I tried again today and the results are incorrect. They are coming out as a constant (the same result as the original formula). Has anyone had this happen before and figured out how to fix? My spreadsheet is fairly complex. Does the data table formula need to refer to the "base" cells? For example, if the formula refers to cell F15, but in F15 the formula is +C15, does my data table formula need to use C15?
View 3 Replies
View Related
Apr 8, 2009
I have a table of values that are computed using input values from the first column (y's) and first row (x's). Each value in the table (z's) has a unique set of x and y.
In order to plot z vs x and z vs y I need to create a list of unique x,z and another of y,z. My question is how most efficiently to thus turn this table "inside out." The table will be very large, or I would do it by hand.
View 9 Replies
View Related
Aug 4, 2008
trying to find a way to insert a table into an active chart area.
unlike inserting charts as objects inside of a spreadsheet, i am trying to insert a table of the spreadsheet into the chart window.
the purpose is so that i may have a numerical reference to my data.
usually i post questions to which i have begun work on - but i have had no luck even brainstorming how this would be done!
the ONLY solution i have surmised is to insert a table into a chart area as an Object(picture)
but how would one write a macro for making dynamic range screen shotpicture files?
View 4 Replies
View Related
Nov 23, 2012
I have made a pivot table which I use each month by just updating the source data, however I was trying to have a cell in another workbook say "=A31" but it keeps going "=getpivotdata(...specific name".
My usual practice was to copy the top ten rows of my pivot table into another file but I am trying to erase this step.
View 1 Replies
View Related
Jul 24, 2008
I have made a pivot table and I dlike to identify with a macro the documents with net value over 1000. Then extract these values next to the respective sales documents in an are near the pivot table somewhere. The fields are called Document and Sum of Net value. Of course the pivot is very variable one time it has 3000 records and another 5000.
View 9 Replies
View Related
Dec 14, 2006
i have a pivot table an extract of each data contained in this table.
[img]Count of NAMdate
SERVICENAM12-oct10-déc11-décGrand Total
Commercial-lauralaura11
Commercial-laura Totalgh11
custody-jonathanjonathan112
k11
custody-jonathan Totalgh1113
settlement-ludovicludovic11
settlement-ludovic Totalgh11
SPQC-elodieelodie112
SPQC-elodie Totalgh112
Grand Total1337
View 9 Replies
View Related
Mar 23, 2009
I have a table about 35,000 rows. I just sorted the table so now you can see the data by date a time of occurance. Now there is duplicate data... like almost duplicate 1 or 2 fields are different which is ok BUT I just want to extract the first occurance so EG:
765432 Data data data etc etc maybe something different here
765432 data etc maybe different here
765432 data same thing
765449 data....
765449 data...
800000 data
898427 data
898427 data etc...
OUTPUT:
765432 Data data data etc etc maybe something different here
765449 data....
800000 data
898427 data
Notice how it just took the 1st occurance and not all... I tried Advance filter but the data is all unique so it doesnt help it takes everything... I tried recording a macro but its crazy! and I did a pivot table took the first field... tried a vlookup and thought I could drag it but its getting too complicated.
View 9 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
Nov 30, 2006
I have a raw database that contains various data all in one column and I only want to extract certain data to a new column. All I want is to extract the time, like 14:00, 14:15, 14:30, etc...... from column "A" and populate column "D" if it meets this criteria.
I have tried everything I can think of, very difficult yet it seems to be so simple.
The "date" is really a date format but the "time" is really text.
Here is what the data looks like:
Column A has....
Name1:
Name2:
Name3:
10/16/2006
14:00
14:15
14:30
14:45
15:00
10/27/2006
16:30
16:45
17:00
View 9 Replies
View Related
Jun 20, 2007
I have an excel sheet which has 200 fields and 1000 rows, now I want to make summary of them, so I need some fields to be extracted from those 200 fields to another sheet on multiple criteria, Like Category, First Name, Last Name, District, Village, Date and etc.
View 9 Replies
View Related
Apr 1, 2009
I am not sure how would I go about finding the last data entry in a table and using formula to extract information. Attached excel 2007 file to better understand. Need formulas for cell B20 and E20. You can use column A as a reference if it is easier as this column is counting the number of entries and the next value will simply be a +1 to the last value entered in column A(or 75 in our case).
For Cell B20: Need to calculate the days difference between todays date (Cell A20) and the last entry date(in this case B5, this will change as new data will be entered). A subtraction of two dates would suffice - my problem is that I do not know how to find the last entry on the table and use it. You have to remember that table gets updated and new entry will be added so then cell B20 should give the difference between A20 and the last date entered.
View 2 Replies
View Related
Mar 22, 2009
I have a table about 35,000 rows. I just sorted the table so now you can see the data by date a time of occurrence. Now there is duplicate data... like almost duplicate 1 or 2 fields are different which is ok BUT I just want to extract the first occurrence so E.G.:
765432 Data data data etc etc maybe something different here
765432 data etc maybe different here
765432 data same thing
765449 data....
765449 data...
800000 data
898427 data
898427 data etc...
OUTPUT:
765432 Data data data etc etc maybe something different here
765449 data....
800000 data
898427 data
Notice how it just took the 1st occurrence and not all... I tried Advance filter but the data is all unique so it doesnt work it takes everything... I tried recording a macro but its crazy! and I did a pivot table took the first field... tried a vlookup and thought I could drag it but its getting too complicated...
View 9 Replies
View Related
Jan 14, 2014
extracting data from a table.
team 01
team 02
team 03
[Code]....
I want to know what are the teams in which sam a member. Peoples' names should be selected from a dropdown menu and names of the teams should be displayed below the menu.
View 9 Replies
View Related
Dec 21, 2013
How can I extract rows from one sheet into another, based on certain criteria?
View 1 Replies
View Related
Jan 6, 2014
I have a table with multiple columns. I would like to extract a sublist from column A, based on 1 criteria (<94) on column J. I used the index-array-row array function, however, it does not give me the intended result. It repeats the same name.
My final intended result is to extract the whole row based on criteria on column J.
View 5 Replies
View Related
Aug 9, 2012
I have two excel sheets. Lets say one is for May and another for June month. I have some data like name of account, account number their ratings . I wish to find if the account present in June month was present in May month sheet or not and if yes, then i need to extract a rating entry( column) from it and paste it in another sheet where i have copied all the data of June month so that i am able to see if the rating has changed over the months or remain same for that particular account...
i am trying vlookup but it is not able to search the name of the account even when it is present in the sheet( like WBIDFC) ... it seems the problem may be because of the format but i am not sure.... moreover i tried to vlookup via account no. but problem is that sometimes the account number is given and sometimes not.. Moreover , the format also does not match sometimes..
View 9 Replies
View Related
Feb 13, 2014
I am trying to populate a tab with all the unique values from a data table. For example, list all the SKUs, SKU Names, Buying Groups, etc. for "Owl Filled Candles" on the "COLLECTION - SKU" tab. For reference, the "VENDOR - SKU" tab works perfectly. I want the COLLECTION tab to do the same thing as the VENDOR tab. The only difference is the VENDOR-SKU tab is pulling data based on a Vendor's name in a drop-down list in B3 and the COLLECTION tab is pulling data based on the Collection name in a drop-down list in B3.
I tried to just copy the tab and reset the reference cells but that isn't working.
View 4 Replies
View Related
Dec 30, 2009
I need to extract/copy (to another sheet) data of all 4 columns in a row for each first entry and last exit (for each person) from the attached file.
View 10 Replies
View Related
Mar 25, 2013
I would like to obtain a list that is the equivalent of the list that I get when double clicking in the cell of a PivotTable.
IF i have for example
I would like to obtain all the lines that have "d" in column 4 in one table.
Is that possible with just formulas?
I got the number of occurrence in the series by doing a countif on column 4
I am trying to understand some formulas that include indirect, address, small, if and row but don't really get them...
I got this far but it doesn't work:
=INDIRECT(address(small(if(Fahrzeugpark!$AD$5:$AD$5000=AA21;
row(Fahrzeugpark!$AD$5:$AD$5000));column()-1)+1-row(AQ1;2)))
View 2 Replies
View Related
Feb 6, 2014
I have some data which is copied from our system in the format below, I then need to add only parts of this information to an existing spreadsheet on the network.
This is the information (there could be lots of these entries, but all need to be added to the spreadsheet)
Device ooo.1111111111111111111111555666663333 performance has deteriorated.
increased from average value of 2041 days to 44589 days.
warning
2/6/2014 2:38:18 AM
aaa-ab-cde00.efghijklmno.ddd.kk
Device ooo.11111111111111111111115556666633356kk performance has deteriorated.
increased from average value of 2041 days to 44589 days.
warning
2/6/2014 2:38:18 AM
*aaa-ab-cde00.efghijklmno.ddd.kk*
I then need part of the above data to be transferred to in Excel in the following column headers (see attachment for layout)
DateTimeHostDeviceLow ValueHigh Value
what is the quickest and easiest way of updating this spreadsheet..... I have to update the spreadsheet every morning and it is very time consuming .
View 5 Replies
View Related
May 16, 2014
Say, I have 100 names , in that two names /employees joined during the week of 04/21/2014-04/25/2014 - 5 business days and one resigned during the week - Thats my first worksheet Which gives the employee name , Employee id , Joining Date , Resignation Date.
Second worksheet i am giving a summary -
In that i would like to get only the names joined during the week and resigned during the week
Is it possible ? Will the offset function can provide me the desired result ?
View 4 Replies
View Related
Feb 14, 2013
In the attached, sheet 2 has a formula that pulls the Item (column B) from Sheet 1 based on Instocks (column F) being less than the value in N2. Would it be possible to add a second criteria to the formula in sheet 2? In short, can I pull the item from column B of sheet 1 into sheet 2, where in sheet 1, column F values are less than N2 AND where column G values are greater than O2?
I'd love to just vlookup the fill rate value and then filter it down, I know at some point I will be asked to weed it down a bit via a formula instead.
View 1 Replies
View Related
Apr 16, 2012
What I am trying to do is extract all the data from a excel database based on criteria. When i specify the site and month I need to pull all the corresponding data and write it to a worksheet. Below is an example. I am not allowed to show all the data it is confidential.
Site Date Operator Month
Chicago 12/3/11 Daniel December
San Jose 4/8/12 Mike April
New York 4/8/12 John April
View 1 Replies
View Related
Apr 28, 2013
I need to return the values in Column A IF any of the adjacent cells (columns) contain a number.
Sample data and expected Results...
Sheet2 Â ABCDEFGHIJK1DateData1Data2Â Data3Data4Â Data5Â ResultÂ
201/01/20132Â Â Â Â Â Â Â 01/01/2013Â 302/01/201311Â Â Â Â Â Â 02/01/2013Â 403/01/2013Â 1Â Â Â Â Â Â 03/01/2013Â 504/01/2013Â Â Â
13Â Â Â 04/01/2013Â 605/01/2013 06/01/2013Â 706/01/2013Â Â Â Â
1Â Â Â 08/01/2013Â 807/01/2013Â Â Â Â Â Â Â Â 11/01/2013Â 908/01/2013Â Â Â Â
1Â Â Â 12/01/2013Â 1009/01/2013Â 13/01/2013Â 1110/01/2013Â Â Â Â Â Â Â Â 15/01/2013Â 1211/01/2013Â Â Â Â Â Â 2Â Â Â 1312/01/2013Â Â Â Â Â Â
1Â Â Â 1413/01/2013Â 1Â 1Â Â Â Â Â Â 1514/01/2013Â Â Â Â Â Â Â Â Â Â 1615/01/2013Â Â Â 3Â Â Â Â Â Â 17
I cannot use VBA, Advanced Filter or a Helper column, but I could use one additional cell to hold a count, so, the solution can only be a formula.
View 7 Replies
View Related
Nov 2, 2008
I am trying to use an advanced filter to extract records that meet the criteria in the blue input cells. I can't get the criteria correct to allow me to meet the 3 conditions. There are duplicate names in the list so I will need to use unique records only option....
View 9 Replies
View Related
Oct 2, 2009
I need to manipulate some data in order to make it reasonable. I'm reading up on Macro's now in order to be able to do this stuff in the future, but I think macro's are a long term thing.
Actually before I go into that, is there any book one can recommend for macro's? I do have a friend's books, How to Do Everything with MS Office Excel 2003, MS Excel Bible 2003, and MS Office - Excel 2003 Formulas. Are these good or any other recommendations? In particular I would like some data to play around w/ so that when in the book they tel me to do something I actually have some data to manipulate. I imagine learning the basics of macro's will be easy enough, but as I understand it the real power of macro's comes from programming in VB, and I dont see how that can be covered in these books.
Now for the more specific questions -
My raw data looks like the attached excel file called Raw Data, I want it to end up as the other attached excel file called Result. How would I create a macro to read where the ticker begins and ends? Is there any way to do it w/o VB knowledge?
Raw Data:
[url]
Resulting Data:
[url]
More importantly, I want to be able to order the tickers by how many times they've been mentioned. For example in the second excel file called Result, Laura has been mentioned twice as often as anyone else. When I sort this list I want to sort by who's been mentioned the most. So I'd like to see 6 lines of her data, followed by the person mentioned the second most (if they had 5 lines, the next 5 should be them etc). For this one would pivot tables do the job, or do I need macro's for this as well?
View 13 Replies
View Related
May 19, 2012
I want to extract data in table with this order C7:AA7
Sheet2 *ABCDEFGHIJKLMNOPQRSTUVWXYZAA1LetterNo
*************************2A5*************************3B15*************************4C3*************************5D2
*************************6***************************7**ABCDABCDABCABABBBBBBBBBBB
View 9 Replies
View Related