Excel 2010 :: Find Value In Another Table And Return Value From Same Row?
Dec 14, 2012
Excel 2010. I have two very large tables of data:
Table 1 is a long list of unique items in no particular order, each of which belongs to a unique group of items, for example:
ItemsGroup ID
apple
pear
grape
fig
banana
guava
peach
mango
Table 2 lists all the Group ID numbers and in the same row as each Group ID number, shows the names of all the items belonging to that group, one item in each column. Different Groups have different numbers of items in them. For example:
Group IDItem1 Item2 Item3 etc.
214applepeargrape
33fig
7bananaguava
59peachmango
Is there a formula I can use in the second column of Table 1 to look for each item in Table 2 and tell me what group it is in? For example for "apple" or "pear" or "grape" it would look in Table 2 and return the Group ID number "214".
View 4 Replies
ADVERTISEMENT
Apr 5, 2014
I have my data arranged in a table on sheet1 as shown below.
Columns B to H contain the hours each person normally works on the days of the week Sun to Sat. In columns I to O are the hours that each person has booked as holiday for that week.
On sheet2 I would like to be able to enter a holiday day in C1. In A3 to A15 I would then have a list of all the people who will be working on that day and in C3 to C15 have a list of the people who are on holiday so the output on sheet 2 would look like this.
Basically I'm after the formulas for A3 and C3 that can be copied down the column depending on how big my table gets. I am using Excel 2010.
View 9 Replies
View Related
Oct 5, 2012
I have sheet 1 that in cells V5:V20 is a data validation drop down box. In cells W5:W20 I have another data validation drop down box. On sheet 2 I have a table that in column A matches the data in the drop down box's in column V on sheet 1. Row 1 on sheet 2 data matches the data in the drop down boxes in sheet 1 cells W5:W20. What I'd like to do is on sheet 1 Cells Y5:Y20 have a VBA code to lookup the data in columns V and W from sheet 2 and return the value.
Windows 7
Excel 2010
View 9 Replies
View Related
May 29, 2013
I've got an old Excel sheet with Stephen Bullen's function for returning the active filter criteria (Rob on Programming: Excel: Displaying Autofilter Criteria). My status sheet may be filtered in multiple ways, and when the user is happy with the filter selections, she can create a powerpoint file with a graph and a summary of the filtered table. As we are using Excel 2010, users are very likely to select more than two filters.
Example: Range A1:E100 has the following headers: Field, Installation, Project,Type, Phase.
The controller wants to filter on:
Field equals north or south or west Phase equals completed
The manager for Field South wants to filter on:
Field equals southType equals maintenance or repair or modification Installation begins with Zeus.
As the filters are not shown when I copy the table to powerpoint, I would like to create a summary of the user's active filters that is pasted into a sheet (for subsequent copying to powerpoint). For the users in the example above, that table would look something like this:
Controller:
Active filters
Field: north, south, west
Phase: completed
Manager, Field South:
Active filters
Field: south
Type: maintenance, repair, modification
Installation: Zeus*
I've looked at various functions intended to take Stephen Bullen's code into Excel 2010's multiple criteria world (e.g. this: User Defined Function to Display AutoFilter Criteria for More Than Two Criteria in Excel 2007 / Excel 2010), but I have not been able to convert it to a functioning macro.
Any code that could be used for this sort of task, or any tips for relevant code?
View 2 Replies
View Related
Dec 3, 2013
I am trying to create a formula for a work report and I am afraid I am a bit of a novice with the more complex formulas. Here is what I am trying to do: Sheet 1 has Sales order numbers that duplicate based on the sales lines. Sheet 2 has single lines of sales order numbers and their assigned PO number. I would like to have Sheet 3 where it combines all data and duplicates and fills in the PO number to match all of the sales order numbers that repeat on the other sheet. I hope this makes sense. I can't seem to figure out if I need a VLookup or If or Match formula (or all of the above).
View 1 Replies
View Related
Mar 31, 2012
I can't find the Pivot Table icon in the toolbar. I tried "Insert", I can only see the 'table' icon, but cannot see the 'pivot table' icon. how can I find this 'pivot table' icon, or create it in the tool bar?
View 4 Replies
View Related
Jun 12, 2013
I have created a table in Excel 2010 (pls see attached table named post.xlsx).
Then copied the above table into PowerPoint 2010, using "paste link" (I tried to attach the PowerPoint file but the system says "invalid file type" and I cannot attach it).
Question:
I have received income data for another month - the new month is 13 and the corresponding new income is 100.
I typed 13 and 100 into the Excel table post.xlsx and thus extended the table by another column.
Then I went back to PowerPoint slide, then right clicked on the table there, then clicked "update link".
Specific Question:
The newly-typed column in Excel table is not get updated in PowerPoint table.
View 2 Replies
View Related
Apr 26, 2012
Recently, the boss showed me a Pivot table & chart, which consists of a list of about 30 user names in the first column. The row headings were the different items they purchased from a vending machine. & when he clicked on any name in the first column, this created a new sheet, renamed with users name, with a small table of results showing what that person purchased.
Problem is, none of us can figure out how to do this. I have created a new Pivot table & chart exactly like the original, but I cannot get the smaller sheet to generate. (Excel 2010)
View 3 Replies
View Related
Jul 9, 2012
Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).
I am using excel for windows 2010. The macro that i have so far is below.
Sub LoopTest()
Dim n
Dim V
Range("A1").Select
V = ActiveCell.Value
[Code] ........
View 1 Replies
View Related
Mar 10, 2012
Is is possible to construct a vlookup to identify that there are duplicate values in the Lookup_value and the Table_array and return the second and third values in the Formula result?
In the acutal project the Lookup_values and Table_arrays are in separate worksheets, but I think the example below represents the issue I'm working with.
I'm using Excel 2010
ABCDEFG1=SUM(IF($A$2:$A$8=A2,1,0))=VLOOKUP(F2,$A$2:$B$8,2,FALSE)
2JOHN13BILL12BILL11BOB14MARY11FRED15JOHN23JOHN16FRED11JOHN17BOB11JOHN18JOHN33MARY1
View 9 Replies
View Related
Aug 8, 2012
I have row 2 formatted as a fraction and row 1 formatted as percentage.
I am looking for way to return the percentage of row 2 in row 1. I.E. If i have 3/4 in a2, I want it to return 75% in a1. I have figured a formula to return a percentage for numbers with only 1 item to the left of the division sign (i.e., 3/4). That correctly returns 75%. I am struggling with working a formula to also return larger numbers (e.g. 12/15).
I'm using excel 2010......
View 2 Replies
View Related
Nov 6, 2013
I have the following data in column H ....
Excel 2010H2Ms L Sentinella3Mr R and Mrs A Agarwal4Dr H M Bingham and Mrs G M VersaceSheet1
I would like to return (into columns I and J) only the surnames that exist in each cell, so it would return something like this ...
Excel 2010HIJ2Ms L SentinellaSentinella3Mr R and Mrs A AgarwalAgarwal4Dr H M Bingham and Mrs G M VersaceBinghamVersaaceSheet1
Notice, there are two surnames in H4, so one of the surnames would be returned into I4 and the other one into J4.
I'm able to write formula to strip out everything and return the surname but only if there's one surname in the cell, I can't figure how to do it when there are two.
View 4 Replies
View Related
Nov 23, 2009
I am having trouble with getting a value (which is the row and column value) from a set of numbers. I have attached an example table to try and make things clearer.
What I have is a table with a set of Row Headers, and Columns Headers all numeric. e.g Columns headed 1, 2, 3, 4, Rows 30, 40, 50 etc. I want to find the 1st occurrence of a value e.g. 1.0 in the table (by 1st Up mean closest to top left), and then give me the value of the Row and Column Header that corresponds to that.
So for the example attached, I want to get the row Value 200 and Column 6 from finding the 1st occurrence of the number 1. I have tried various combinations of index and Match etc. But I can't get my head around the fact that the position of the 1st no. 1.0 can change, therefore I can't use any releative positions etc.
View 3 Replies
View Related
Mar 28, 2014
I have in column A duplicate values and in column B different responses (Sheet: Lookup). I need to look up the value in column A (Sheet: Results) and bring back all the responses in column B (horizontally).
Nittie Query.xlsx
View 5 Replies
View Related
Jan 14, 2014
I am looking up the largest value across various sheets (1 to 99) with the following formula:
=LARGE('1:99'!$B$1:$B$50;$C3)
That correctly returns the largest value in range B1:B50 across sheets 1 to 99.
However now I want to know the sheet name of the value above in a seperate cell, let say in: A3.
I'm using excel 2010.
View 3 Replies
View Related
Apr 4, 2014
I get syntax error on return statement. I am using Excel 2010.
View 2 Replies
View Related
Mar 19, 2013
On a worksheet, I created:
- a list of data
- a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
View 3 Replies
View Related
Nov 12, 2011
I am trying to use the code that can find the last used row in particular column.
Here is the code in module1
Code:
Function LastRow(rng As Range)
Dim temp, temp1
Dim col As Range
With Application.Caller.Parent
For Each col In rng.Columns
temp = Cells(Rows.Count, rng.Column).End(xlUp).Row
If temp > temp1 Then temp1 = temp
Next col
End With
LastRow = temp1
End Function
and code in the range
Code:
=LastRow(V12:V300)
My problem is, this code was working nicely but now it only shows ###.
PS I use MS office 2010
View 9 Replies
View Related
Mar 7, 2014
I need a formula or array to perform the following in Excel 2010:
I have 2 worksheets "Master Report" and "Free text comments". The master report will be shaed with individuals but the Free Text comments worksheet will not.
sample feedback report.xlsx
In cells D2 downwards on the "Master Report" I want to show the individual feedback comments from the "Free Text comments" worksheet that have been made by other people for the individual named in cell A2 on the "Master Report" worksheet. So on James' master report it would show the 8 individual comments listed for him on the Free text comments" worksheet.
I have searched the internet and tried various combos of lookups, IFs, index and match but have not come across the right formula yet.
View 1 Replies
View Related
Mar 19, 2014
in Excel 2010, whether using VBA or otherwise;
1) how can I return the oldest date in the entire workbook (or at least in the entire sheet if it's not possible to find that date in the entire workbook).
2) how about the most recent date ?
View 2 Replies
View Related
May 5, 2012
I'm looking for one function for each of these properties that will provide them with negligible overhead in all or almost all spreadsheet data cases, such as the normal spreadsheets, autofiltered data, large workbooks, etc.
By the way, by "first row", I mean the first row after the header row, given that the header row is in row 1.
View 7 Replies
View Related
Jul 23, 2014
I am using Excel 2010 and I am currently trying to find the Avg Min / Avg Max costs for 4 different groups.. Players, Accompanying, Coaches, Medical.
I need it to get the minimum values from 3 different sections ( I am using custom names for them such as PlayerReg for Player Registration costs ) once it gathers the minimum / maximum values from the 3 different sections it needs to add them together to display the Avg Minimum / Maximum for each group.
At the moment I have =MIN(Total_Discount_7_Nights + PlayerReg + FrequentFlierFare) under Minimum /w Frequent Flier although the minimum value it should detect is $948.75 it displays $1115.
For maximum I have
=MAX(Total_Discount_7_Nights + PlayerReg + FrequentFlierFare) under Maximum /w Frequent Flier although once again this value should be $1174.50
Although for some reason it displays $978.75 - which is the minimum cost. The Maximum figures for the other groups (Accompanying, Coaches and Medical) don't even display values Instead it just has #VALUE! in which I'm guessing I have something wrong with it.
View 2 Replies
View Related
Jan 30, 2014
I deal with a rather large excel database (the range is A1:AV168266) that contains customer information. Some of these various customers are affiliated with a group called "ascend" and I need to be able to filter all my list by every customer affilliated with Ascend. The trouble is that the word "ascend" can appear in 8 different columns and usually when it appears in one column it doesn't appear in the others and their can also be more text in the cell beyond the word "ascend". I want to create a formula that will simply look for the word "ascend" in any cell of a row and return a result I can filter by. I will be putting the formula in column AW and put it in all 170,000 rows of that column, that way I can just filter by a single column.
I've played around with combining COUNTIF with SEARCH and MATCH with SEARCH but nothing seems to work.
View 7 Replies
View Related
Apr 28, 2014
I had a workbook in Excel 2003 that i just moved to 2010. In the 2003 file I had 2 pivot tables, one each on a worksheet, looking at the same data, just grouped differently. One yearly, one quarterly.
Now in the 2010 workbook whenever I change the grouping on pivot table, the other one changes also. It's like they are linked together or something.
View 3 Replies
View Related
Jun 9, 2013
I have two spread sheets that I 'converted' into tables. I need to combine them into a single sheet. When I attempt to copy and paste from one to the other the formatting and formulas do not carry 'down' as they do when I manually enter a new line or record.
Is there a way to copy multiple rows of data from one tabled sheet to another tabled sheet?
View 12 Replies
View Related
Jan 27, 2014
I am relatively new to VBA and am trying to convert a range of data to a table in the same sheet. I receive the following message when I try to run the code as shown below:
"The worksheet for the table data must be the same sheet as the table being created." The code stops on the third line of the code.
Sheets("Data Forwards").Select
ActiveSheet.Range("$A$1:$U$1000").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$U$1000"), , xlYes).Name = _
"Table1"
ActiveSheet.Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium2"
I can see that the range is highlighted in the sheet before the code breaks.
View 3 Replies
View Related
Mar 13, 2012
I need to return a value dependent on a few criteria. Type will be selected via a drop down and then it must lookup the same type in the top row and then the value which will be between two values and then return the grade on the far left.
GradeG4M5M6TypeValueGradeA
160016501800M5850CB600700650780900950C7008007809109501100D8009009101040
10001250E90010001040117010501400F100011001170130011001550G>1100>1300>1150
I am using Excel 2010 on Win 7
View 6 Replies
View Related
Sep 10, 2012
I have a table in columns C:J, starting in row 7. I have a macro that selects and copies the whole table (after I filter the table) and pastes to another sheet. Recently I've realized that I can only copy/paste C:I or it will mess up my formulas during the process.
This is the code I have right now, which works beautifully for the whole table:
Code:
ActiveSheet.ListObjects("Table36").Range.AutoFilter Field:=6, Criteria1:= _
"=Alan", Operator:=xlOr, Criteria2:="="
Range(ActiveSheet.ListObjects(1)).Select
Selection.Copy
What I need is to know how to copy everything in the table except column J. I can't just copy a certain range cells because the number of rows is variable. There has to be a way to modify the ActiveSheet.ListObjects("Table36") right?
Using Excel 2010.
View 2 Replies
View Related
Sep 13, 2012
I have a pivot table in 2010- is there a way to filter the data using an external reference from the pivot table? I'd like to put the value in another cell and have the pivot update automatically when I type a new value in that cell.
View 2 Replies
View Related
Jan 10, 2013
I have an Excel table with 7 columns. I have used a formula to randomize the data in each column, and it works fine, accept that it resorts/re-randomizes the contents of the table every time data is entered anywhere in the workbook. I would like the data to be sorted/randomized only once, and then only again upon pressing F9, since I have other spreadsheet which reference the data in the table.
This is the formula I use to sort/randomize the data, and I am using Excel 2010 on a PC.
=VLOOKUP(SMALL($C$2:$C$20,B2),($C$2:D20),2,FALSE)
Column B = numbered 1 - 20
Column C = =RAND()
Column D = last of names
Column E = sorted/randomized list of names.
View 4 Replies
View Related