I have a pivot table that looks like this:Red Yellow OrangeApple .................12
Banana ......................10
Strawberry ..........8
Carrot ..................................2
I want to know the colour of each fruit, for example:
Apple Red
Banana Yellow
Strawberry Red
Carrot Orange
What formula should I have in the second column to find the colour of the fruits?
I uploaded a simplified version of the workbook I'm manipulating for reference. I'm looking for a formula where I can get the ROW heading of a table to be returned based on the max value of a certain column. My column headings in the table are months. I have separate place in the workbook where I have the Months listed in a Column (A9:A......) and in Cells (B9:B....) I want to return the row heading name from the table. The tricky part is that the column headings don't always stay in same place, so Jan isn't always in A1, sometimes it is in B1 or C1 of the table.
Essentially from my months listed is separate part of the workbook. In the cell next to each month I want to match that month with column in the table. In that column find the max value and return the row heading.
I have a 10x10 table. First row contains column titles (plain text, hardcoded). Each line below them has a 1 in *just one* cell. So, each line contains 9 zeros and one 1.
In the 11th column I want to check which column has the 1 in it and return the column's title. So, if the 5th row has a 1 in cell E5 I should get, in K5, the value of E1 (the title of the E column that is).
I have a Meeting Place and Cities Table(too large).An example attached as a file and explained waht is wanted.
View 2 Replies View RelatedIn a large spreadsheet that receives external data, I have codes (U, N or V) allocated at various times to different rows as shown in sample worksheet attached. Elsewhere in the spreadsheet, I need to display (for later export) these codes along with their respective number in a sorted list. Please review the attached:
In the spreadsheet you'll see a column of codes, the next column is the data reference number, then to the right is three columns, one for each code. As displayed in the sample book attached, each Code column is to display the data reference number (from column 2)that matches that column's code.
What formula can I use to list these numbers in the appropriate columns?
I am having a situation where I have heading of certain names or items and it is spread across 3-5 cells (merged as one) and underneath these cells there is subheading (each item occupying single independent cell). I just want a custom function that can LOOKUP the heading and then give me values corresponding to subheading of choice. I have attached relevant image for understanding. For example if in any other sheet or in the same sheet i want to lookup something FIRST by "Quantity" (main heading) and SECOND by subheading then how can i do the same?
Image is below:
I have created a simple command button for tallying that will increase the count in the output cell by one every time it is clicked. It has basic code like this:
Private Sub ProductRegistration_Click()
[D4] = [D4] + 1
End Sub
On top of that, I want the output cell of this command button to switch every day. In this case, it would move to E4 tomorrow. The columns are dated but I can't figure out how to get the output cell to automatically change with the system date so I don't have to manually change it every day.
Attached is a very small sample of a workbook that looks at community impact; before, during and after a project.
The source data (not shown) is down loaded from a system that can only supply the date in reverse order (see column A).
i.e. 20080229 = 29-Feb-2008. I've applied a conversion in column "C"....
= DATE(LEFT($A2,4),MID($A2,5,2),RIGHT($A2,2))
The 'properly' formatted date is then used to determine when on the scale of observation that given date falls.
e.g. the number of weeks before, during and after the project.
This is determined by the 18 week date 'matrix/ array' in columns F to X the red box is used to input the project start date, (& assumes the project runs to its normal 6 week time scale, it get REALLY messy if the boss prolongs or cuts short the project!)
From the result various figures and a graph using the properly formatted date are calculated, produced and presented showing....
The scale of the problem {before}
The impact of the project {during}
The lasting efftectivness {after}
You can see that I've managed a 'workaround' for the limit on the number of IF statements allowed in one formula using a rolling formula in columns; B then Z - AQ. This works OK, but!! combine that with the other formulae doing the impact calculations and 20000+ rows of data (with the acssociated workings in B + Z-AQ) the effect is to make make the file over 15Mb for 1!! worksheet, (the limit for emailing on the jobs system is 3Mb!!) & just to really make it scary sometime there are 2 projects on the go at once.
Before I created the attached the user responsible for these graphs and calculations manually counted dates & typed/copied & pasted the week number/tile, initially several days work followed by half a day every weekly update!
Referring to the post Find Min Value In Multiple Columns And Match The Header. I would like to go little bit further and see is there a way to find and match not just in multiple columns but in multiple tables (see the example)
View 4 Replies View RelatedI need to work with the run times for some equipment. This is just hours and minutes and I get the raw data as like "Pump # 1 - 35:30". This is not date/time data per se, just a raw number of hours. I thought I would format the cells as hh:mm and for 15:00 I would just enter 15 to save time. No go, that becomes 1/15/00 00:00 and displays as 00:00. I discovered that if I just leave the formatting as general and enter 15:00 it works fine and displays right but the formula bar says 3:00 PM (Excel figured to format it hh:mm). When I enter 30:00 it again seems fine but the formula bar says Excel stores it as 1/1/00 6:00:00 AM (Excel figured to format it [h]:mm:ss but I deleted the seconds).
I can just set the formatting as [h]:mm to be consistent and enter the full 15:00 but I guess that after working that out I am just curious - can Excel store hours:minutes as just hours:minutes without turning it into a meaningless date? And without converting it to a decimal number? I don't want to total 1:30 + 1:45 = 3.25; I want the total to be 3:15. Again, it appears that [h]:mm will do what I need but it just seems unelegant to me that it is stored as some weird date and I wonder if it will cause a problem at any point.
I was going through "To do list with progress tracker" template in Excel 2013. I noticed when i scroll down the page the heading column (A,B,C,D,E,F,G etc.) automatically matches with the inserted table headings.
View 1 Replies View RelatedI'm trying to find the largest number in a row and then have the column heading (text) as the result. I can find the largest number by using =max(numb1, numb2 ....) but then how do I get the heading of the column as the result. An example of what I want to happen is below
Red
Green
Blue
Orange
Yellow
Result
2
4
3
6
1
[code]...
I anticipate an issue where 2 columns have the same largest number and not sure how to over come this either with multiple answers
I have a 2 groups of column headings with a different month and year in each heading so
1st Group of columns range
Columns AJ through AX
Column Heading example "Expense Ratio February 2013......next Column over is "Expense Ratio March 2013"
2nd Group of columns range AY though CE
Column Heading example "Capital Balance February 2013......next Column over is "Capital Balance March 2013"
Each new month I need to add a new Expense Ratio column after the most recent expense ratio Column. (i.e. Find "Expense Ratio March 2013" and I need to add a column after that with heading "Expense Ratio April 2013"
Same thing for Capital Balance - add a new Capital Balance column after the most recent Capital Balance Column. (i.e. Find "Capital Balance March 2013" and I need to add a column after that for "Expense Ratio April 2013"
Because the ranges keep changing month over month, how do i do this.
Data Table including-
List of Identifying Code Numbers for customer invoices
Multiple repetitions of individual Identifying Code Numbers in list
Various data in table range including Various Values of invoices from different dates for each repetion of Identifying Code Number.
- Wish to find largest invoice for each Individual Identifying Code Number in the table without using a pivot table.
i have tried combining Max and Large functions with Vlookups etc.
How do i change this code so i can find the next available row in a table. At the moment it finds the next available row after the table. The table starts row 4 and finishes row 311 however it could grow. There is data in other columns in the Table so i need to stick to searching only column B for the next blank cell.
[Code] .....
I have data spread across 76Rx68C - this table is raw data with no logic to placement of values within .
In another sheet i have one row of values.
I need to know if each of these values appear in the data spread. - a simple YES / NO would suffice - i dont need to know where exactly are they appearing in the data table.
I'm having trouble coming up with the proper syntax to find the last (empty) row in a table. I already have the code figured out to add the row, but need to identify that cell as the paste target.
Originally, the 3 tables on this sheet were in different columns (each table is only a one column listing of part numbers). But then when I would attempt to delete something from one of the tables, it would delete the entire row, which of course affected the other tables as well.
So, I arranged the Tables so that they are all in Column A. Now a deletion shouldn't affect the other tables. But with that came another problem, the usual method (End(xlUp)) to find the last cell no longer works. I need to find the last cell in a specific table, not the last cell in the column.
Code:
the_sheet.Range("Table3" & last_row_with_data).PasteSpecial Paste:=xlPasteValues
Here is this section of code in its entirety:
Code:
Public Sub SKU_Fix()
Dim lrow As Long
Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow
Dim last_row_with_data As Long
Dim Log2 As String
[Code] .........
What formula should I use to find values in Table 2 from Table 1.
Table 1
Acct# Value
100000 12
180250 9
180300 9
180450 9
181854 15
181860 11
Table 2
180250 ?
180300 ?
181860 ?
100000 ?
180250 ?
181860 ?
Look at the Picture Below I Want that the Cell that marked with Red Arrow Returns a "Rank" Value in the Table, Which is a "Blaster" Rank. And I want That Cell Also Automatically Changed When "Blaster" is Changed to "A.A.Z Gonz" or etc. What Functions that i have to choose, INDEX? XL.jpg
View 6 Replies View RelatedI need a formula that will allow me to search a column (B1:B19989) that
contains text with the data from a column (A1:A32417) that contains a list of
part numbers and display a result(True/False) into another column (C). Column
B's text has the part numbers mixed in with the text. I need to know which
numbers from column A are found within the text of column B.
This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2)
All I get is #N/A - I know that the value in A1 exists in CATALOG
CATALOG=The name of array B1:B19989
I have one sheet (sheet 2) that contains three columns of data, a range of a low value and a high value and a corresponding value (column c)--
A B C
0 54 0
55 64 1
on the other sheet(sheet 1), i have a column(column x) in which i need to find that corresponding value (that column c on sheet 2). basically, i need to write a function that says if value x (on sheet 1, column y) is between the values in columns A and B (on sheet 2), input the corresponding C cloumn value (on sheet 2) into column x on sheet 1. i hope this makes sense. an additional snag is that i have about 60 rows on sheet 2, and i need to make it keep searching this table until it finds the range that contains my value x (on sheet 1).
I am trying to find a formula that will sum the values of two columns with the same heading (in my case the heading is "GST"). Every month this info changes, so the GST may not be in the same column all the time. I have around 1000 rows I need to sum. Example data below:
Amount
GST
Amount Total
Card Fee
GST
Card Fee Total
[Code]...
Is there a formula when I can sum two columns based on the column NAME?
In a gradebook spreadsheet, there is code to add new columns to right of existing columns. Each new column should be included or appended to a particular Named Ranges--named with the same name as the column's heading. Here is existing
Sub mcrNotes()
' This adds a column for student notes, such as daily progress notes.
' The heading for the notes column is "Notes"
' The pre-formatted columns are being copied from a sheet named "Fields".
' The columns are inserted into a sheet named "Grades".after the last data column.
Sheets("Fields").Select
Dim Ndate As Range
Set Ndate = ActiveSheet.Range("E11")
Ndate.Value = Date
Columns("E:E").Select
Selection.Copy
Sheets("Grades").Select
lastcolumn = ActiveSheet.Cells. Find(what:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column.......
I need a find the intersecting price values for any given height and width. From what I have read on the internet, I can use the "=INDEX( ),MATCH( ),MATCH( ))" formula. However, this doesn't seem to work because the height and width is separated into intervals.
View 3 Replies View RelatedI must have gone into a complete "blackout" as I am stuck with something that seems to be more than simple.
In cell F18 I need a NON Array-formula in order to return the cheapest Provider for a given Country [to be selected via D.V. in cell F14].
I manged to find an Array formula which I left in a broken state in cells F6-->H6/
In the attached file I have two tables Table A illustrates the dates with start, end and code, Table B where I want the formulas that they would find how many days a code is in the month.
From F4:I37 I manually entered the day, I would calculate automatically.
esempio.zip
I'm not sure if my requirement would be possible but I am trying to locate a text in cells on a separate table and then return a corresponding amount beside such cell to a cell beside the text I am trying to locate. I attached the excel file with arrows and everything.
View 5 Replies View Relatedwhich function to use to find the row number of a value for interpolation.If I have a table of 5rows and 5 cols, how to find those rows of those 2 numbers between which my interpolation should be done.
X Y
2.2 3.45
2.9 4.56
3.3 6.2
3.7 7
4 8.1
Now I want to interpolate for 3.1. So how to find the row numbers of 2.9 and 3.3
What I'm looking for is a formula that will search a data table on a source tab and find a value in a column. And have the ability to function like a vlookup and display the contents of cells in the same row on a different tab. The catch is the value will repeat it's self at random interval in the data table. So for example the values are ON-1 to ON-8 and MB-1 to MB-8. Each value will have it's own tab and I want the formula to search the Source tab and capture the value in order but on the next row of the spread sheet.
View 4 Replies View RelatedI have one workbook with 3 Sheets (Table1/Table2/Table3). In Table 3 are the following columns "Table1" and "Table2". I need one script which search in table1 and table2 the words from test1 to test15 and write the assign numbers in table 3 in the right columns / row! If one value is not found, this value should have the number 0 in table 3.
I have the following examples attached, file Mappe1_Test1.xlsx is the before status and Mappe1_Test.xlsx should be the after status.
