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 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 want to lookup the max value within a group of columns and return the column heading (specified a cell) that corresponds to the max value.
View 9 Replies View RelatedI have a spreasheet with partnumbers listed in column A, a safety stock value in colum B, a restock date in column c, and quantities listed by month needed in the following columns, i.e.:
A B C D E F G
Part # Safety Stock Restock Jan Feb Mar Apr
123abc 50 Feb 20 35 20 10
In the example above, it is apparent that the safety stock will be delpeted in Feb.
I am trying figure out how to calculate the month that the safety stock will be depleted
and show this in another cell.
In attached example, I have 3 columns of fractions with a final row of sumtotals (taken from elsewhere). I test to see which column has the maximum value in each row and allocate the result with the column's heading. Sometimes, two or more columns have the same max value. In this scenario, I wish to select the column heading that has the largest sumtotal.
View 4 Replies View RelatedHow do you count the number of columns in a range in order to reach ( find) a specific value in that row ?
View 5 Replies View RelatedI have inventory in 9 cities. Cities names are in B2-J2. Line items are rows 3-100
In column K, I have the max formula for that row. What I need is, in column L, the name of the location city from row 2 where the max qty is located also color the max qty in the row.
I have range like (3E:3K) in this range are only 2 different expressions. "Result and " forecast". I wanna look from left to right for the first cell with the string "forecast".
And than I want to work with the column of this cell, only this column.
How do i do that?
Edited
I corrected the coordinates. I want to look in a specific column line (Nr.3) from there the first Cell with "Forecast" and from that String the row number.
They following 4 posts were based on wrong informations by me.
Encryption algorythim.xlsx
Here is my scenario I have all this data i want to search in. In the rows there are repeating values and in the columns they do not repeat.
As seen in the attached i am able to give a column and row by title and then get the intersecting cell data from that. (Encryption lookup)
What i want to do is give the column tittle and row value and get back the row title. (Decryption Lookup)
This shouldn't be too hard seeing that the data in the columns has no repeats.
I have a number of dates (columns) and under each date there is the demand value (Rows). Also, i have a column that has the current inventory. what im trying to do is to keep on adding the demand in one row (i.e multiple dates) until the sum just exceeds the inventory. After that, i would like to return sum the date at which we stopped adding. the point of this excercise is to see at which month will our inventory deplete according to the demand. Below is an example solved by hand.
Demand
Part #
month 1
month 2
[Code].....
I have a list of names and the chores they need to do on a certain day. I need a formula that returns the chore the person needs to do when the date is filled in.
Ex: Sheet2 Col:A has names filled in A2:11 , row B1:J1 has dates that you would input.
formula goes in B2:J11
Sheet1 is the master with all the data the formula would be pulling from.
column A2:A11 has the names , row B1:S1 has the chore that needs to be done.
B2:S11 has the dates already filled in.
In 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?
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!
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 have a Meeting Place and Cities Table(too large).An example attached as a file and explained waht is wanted.
View 2 Replies View RelatedI have a data set, in which Sheet 1 have a matrix of high level showing Engineer, Program and commodity relationship.
I have to create separate sheet for each program on sheet 1, each program sheet should have Engineer name and commodity name from sheet 1.
Multiple commodities and multiple engineers should be separated by definite structure.
find worksheet with input and output (manual) as expected.
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.
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'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.
Is there a way with the following formula to tell it that if value return is = to value of cell above then find return next value?
View 6 Replies View RelatedI need find the date 1/1/2014 in Row A and from the same colum get the average from row B:z.
View 1 Replies View RelatedI have a huge data base (daily temperatures dating back to 1872), but for simplicity I have limited the values to just a month for this question. What I want to do it list the ten warmest temperatures and then return the date in which it occurred. I am using the following LARGE function to get the ten warmest temperatures (Column B - high temperatures).
=LARGE($B$2:$B$31,$D2)
Column B = High Temp in Data Table
Column D = Rank in Results Table
This works great. However when I use the following Index function to get the date in which it occurred (Column A - Date), it keeps returning the first date in which it occurred when there are multiple occurrences of the same temperature.
=INDEX($A$2:$A$31,MATCH(LARGE($B$2:$B$31,$D2),$B$2:$B$31,0))
Column A = Date in Data Table
Column B = High Temp in Data Table
Column D = Rank in Results Table
In some cases, I have more than 2 occurrences of the same temperatures. For example, the high temperature of 23 degrees occurs 3 times during the month (1/4/2013, 1/11/2013, and 1/12/2013), but only the first one 1/4/2013 shows up in my results table. I would like the other dates to show up. These tables are listed below.
Data TableResults Table
DateHigh TempRankHigh TempDate
1/2/201311271/5/2013
[Code].....
I have a bunch of domain names (including subdomains) in column B
In column A i need just the raw domain name...
example
a1 [domain.com]
b1 [domain.com]
a2 [domain.com]
b2 [mail.domain.com]
a3 [domain.com]
b3 [subdomain.domain.com]
I need a formula to put into cell B19 to Find X in the corresponding row and to return the header value
******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows .NET Server (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEF1Part NumberDescriptionSubframe BuildCut And ChopHopper BuildCabBuild2026319Assy Air Ram Auto Blanking X 3026321Assy Air Ram Rear Boom L/H X 4026320Assy Air Ram Rear Boom R/H X 5022587Assy Air Ram Side BrushX 6031159Assy Air Ram Side Brush Large DiameterX 7030501Assy Blanking Flap Valve Single Vm Minor 8034783Assy Brake Pedal Lh Vm Minor Fl X9034784Assy Brake Pedal Rh Vm Minor FlX 10036349Assy Brush Control D/S No Hp Opt Mer/Mag X 11024553Assy Brush Core 1300 Large Widesweep X 12024554Assy Brush Core 1300 Small Widesweep X 13023169Assy Brush Core Large 7000 X 14025662Assy Brush Core Large 80/85 X 15032482Assy Brush Core Magnum Poly/Wire Mix X 16 17 18 19034784Subframe Build 20023169Cut And Chop Sheet1 [HtmlMaker 2.41] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Need to convert price on transactions throughout the year to different currencies - using historic currency exchange rates.
Entered data in columns A-D:
Date1 | Value1 | Date2 | Value2 |
Required Formula to generate column E for each row individually (to 2dp using d/m/y format):
Value1xValue2whereDate1=Date2
Date1/Value1 will be prices on certain dates while Date2/Value2 will be historic exchange rates for every day of the year.
I am having trouble using teh find method. I'm using it to search for a string in a column, then give me the row number, which is fine when the string is found, but when it is not I get an error of "variable not set." I'm pretty certain it is returning void, but how to I capture that? Here's the
voucher_row = Worksheets("Table").Columns("D:D").Find(voucher, LookIn:=xlValues, LookAt:=xlWhole).Row
"voucher" is a string.
I would like to use a macro to find a number in a worksheet, other than the worksheet i'm using the macro in. The number - in this case a material code located in Column A - must be entered by the user in an inputbox, and then return (copy&paste?) the value of another cell (the price of the material - in Column D) in the same row.
I recorded a macro envoking the find function, but then it only finds the number which I used during the recording. How do I change the code so that the user can input any number, or what code must I write?