Return Value From Column Based On Header Value?
Jul 16, 2013
Cells A3:A500 contain product numbers, e.g. 1001, 1002, 1003 etc.. etc...
Cells G2:N2 are column headers, e.g. North2011, South2011, North2012, South2012 etc... etc...
Cell F2 is a lookup that will return a year, e.g. 2011, 2012, 2013 etc... etc...
Against each product is a "yes" if it was sold in the north or south region for each year. This could be both north and south, one or the other or none at all.
What I would like is a formula in cells E3:E500 that looks at the year in cell F2 and if there is a "yes" in either the North or South for that year to return "yes" otherwise blank.
I am unable to upload a sample document due to firewalls.
View 5 Replies
ADVERTISEMENT
Mar 11, 2014
Requirement
Vendors
Evaluation Results
Sr.
ITEM DESCRIPTION
BRAND
UNIT
A-Z
AL AMEEN
COLOR WORKS
INDEX
OXFORD
PENCIL
SUPPLIES STORE
WAVES
LOWEST PRICE
SECOND LOWEST
Vendor Lowest Price
Vendor 2nd Lowest Price
[Code] .......
I have above table and wants to get the formula for getting the 1) Vendor Name of the Lowest Price per item 2) Vendor Name of the 2nd Lowest Price per item 3) and be able to copy down the formula for other items.
View 5 Replies
View Related
Feb 7, 2014
I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).
Here's data table:
CriteriaHeader 1Header 2Header 3Header 4
ZZZ5.0015.0050.00130.00
ZZ5.0015.0050.00130.00
Z5.0015.0050.00130.00
YYY5.0015.0050.00130.00
YY5.0015.0050.00130.00
Y5.0015.0050.00130.00
DDD5.0015.0050.00130.00
DD5.0015.0025.0075.00
D5.0015.0020.0065.00
RRR5.0015.0015.0045.00
RR2.5010.0010.0030.00
R1.503.0010.0025.00
UUU0.751.505.0020.00
UU0.751.505.0020.00
U0.751.505.0020.00
P0.751.505.0020.00
T0.100.105.0018.00
CriteriaNumberValue
DDD10.00Header 1>>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))
View 3 Replies
View Related
Nov 18, 2011
I've been trying find an appropriate formula to extract the column header from a table in a different sheet if the row header and value in that table is known.
in the lookup table the row titles (column A) are product codes, column titles (row 2, D through AX) are business names and the table values are quantity.
In a different table I have product codes in column A and in column B i have the max number/quantity of products for that code. In column C i want to put the company name associated with the product and the number/quantity.
View 4 Replies
View Related
Sep 18, 2012
I need a formula to return column header if there is data in the column, any data.But I also need to return the second, third, fourth header with data too
In column A row 2, I have =IF(M8"",$M$1,IF(N8"",$N$1,IF(O8"",$O$1,IF(P8"",$P$1,IF(Q8"",$Q$1,IF(R8"",$R$1,IF(S8"",$S$1,IF(T8"",$T$1,""))))))))
This returns the row 1 header for the first instance of data in row 2 columns M thru T But in B2, I need the second header with data in the column.
I need the results in yellow (1st, 2nd, 3rd, 4th) from the array to it's left
1St match2nd 3rd 4thColumn MColumn NColumn OColumn PColumn QColumn R
Column MColumn PColumn QData data2Data3
Column NColumn OColumn PColumn R Data Data2 data3 data4
Would use match/index but can't increment it to the second or 3rd match
View 2 Replies
View Related
Jul 7, 2014
I am looking at formula to return the column header, see example attached.
View 2 Replies
View Related
Dec 11, 2012
My file is set up like this:
Deal Jan Feb Mar....... Dec
A 0 11 0
B 0 0 23
C 13 0 12
and so on
Meaning deal A got cash of 11 in Feb and B of 23 in March
For a certain month (which could change), I want to specify the most recent month of a payment and return the month
So in April, row A returns 2, Row B March and Row C March
View 2 Replies
View Related
Jan 6, 2012
I'm working on a spreadsheet where I need to return the column header where the value in that row matches the criteria from an if statement.
The column headers are departments, the row headers are customers numbers, the values show the % of items sold in that department as a % of the total purchases by that customer.
I need to make a formula for each row so that if the % in any of the current row's values is 75%-100% then get the column header for the column that value is in.
I know it's an IF statement, but I don't know how to return the column header for the value when the if statement is true.
View 9 Replies
View Related
Mar 30, 2012
I'm trying to get all subjects that are "pass" per student #.
Student #MathScienceHistoryStatisticsPhysical EducationHealthPhysicsEconomicsTrigonometryEngineering
5200309PASSPASSPASS5200306PASSPASSPASSPASSPASS5200307PASSPASSPASSPASSPASS
5200305PASSPASSPASSPASSPASS5200257PASSPASSPASS3046109PASSPASSPASSPASS3045539PASSPASSPASSPASS
3045613PASSPASSPASSEnter Student #
If I type student number here the result will be shown below with all subject passed.Subject Passed
View 9 Replies
View Related
Oct 31, 2013
I have a table like the following where each ID can have no, 1 or multiple values associated with it:
Table1
ID>>>A>>>B>>>C>>>D
1>>>> >>> >>>10>>>
2>>>> >>> >>> >>>>50
3>>>5>>> >>> >>>>2
4>>> >>> >>> >>>>
I have another table that only lists IDs
Table2
ID
1
2
3
5
I want a VBA code such that if ID is in Table2, VBA looks for the matching ID in Table1 and if the matching ID found, it looks for ALL non-empty cells and if a non-empty cell is found in the row, it returns the value in the cell and the header to give something like this
Table3
ID>Column1>Value1>Column2>Value2
1>>>>c>>>10>>
2>>>>D>>>50>>
3>>>>A>>>5>>>>>>D>>>>>>>2>>>
View 1 Replies
View Related
Dec 18, 2009
I have on sheet1 a name "brent alpha" which I need to look up in sheet2 and return the header of which ever column it is under,
Sheet 2 consists of 3 columns with a list of data under each one. As I said all I want to do is return the header.
View 9 Replies
View Related
Apr 24, 2012
I have a rather large table that I need to return the column heading from.
Within the table I have a list of Names in the left most Column (A) and a list of Percentiles in the header row (row 2) (incrementing by 5%). By inputting the name and a cell value I am trying to return the column header.
Using an example;
5% 10% 15%
Bob 3.5 4.1 4.15
John 3.6 4.2 4.30
Chris 3.3 4.9 4.95
Sammy 3.7 4.6 4.7
Like I said, knowing the name and the cell value, I want to return the percentage value. So for example, Bob would be one input value, and another input value would be 4.1. I would want excel to return 10%.
I have found different suggestions using a combination of index/match but even using these functions I cannot get it to return the COLUMN value instead of a cell value.
View 8 Replies
View Related
Aug 1, 2014
I've got a spreadsheet with various amounts in cells A3:L5. I want to find the last non-blank entry in each row but only where the entry is in a column headed "Guaranteed PRB" (found in row 1). Then I want it to return the column header title found in row 2, which is a date.
I've attached a sample of the spreadsheet with the expected answer in column M.
I've got as far as formula:
=LOOKUP(2,1/(a3:l3<>""),$a$2:$l$2)
how to also make it look at row 1's headers too.
View 5 Replies
View Related
Feb 12, 2014
I'm currently using the formula
=IFERROR(INDEX(startmonthsp,MATCH(TRUE,INDEX(ISNUMBER(E2:AR2),0),0)),"").
The startmonthsp name refers to the months across the top of my pivot table. This formula works but I now need one small change. If the value in the columns are zero or less then I do not want the formula to return that column header (bypass it until it gets a positive value above zero).
Attached is a workbook that shows my formula in action.
Excel Forum Question.xlsx
View 2 Replies
View Related
Oct 21, 2009
I've got numerical data in columns A through AF, starting in row 4. I need a formula, starting in AG4 that I can drag down. I need the formula to look back across the row until it gets to the last posistive number, then return the column header of that column (the column headers are in row 3)
so for example
Header row
Jan Feb Mar Apr May Jun Jul Aug
Data
0 3 0 0 8 0 0 0
the formula would return May in this example as 8 is the las positive value and the column header for that column is 8. There are no blank cells in the data.
View 9 Replies
View Related
Nov 18, 2008
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.
View 5 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
Feb 9, 2009
I'm looking for a formula that will return the column header (a date) of the first instance of a number greater than 0. For example if columns A through S had dates as in row 1 (header row), and in row 2, every column contained a 0 up until column P, I would want the date (Row 1, Column P) returned? I think this might be a sort of index/match formula, but I am not very familiar with these.
View 3 Replies
View Related
Jan 17, 2014
I have this table, which can be seen as a basic custom gantt chart: KLRWo.png
And I would like to fill the A column with start dates, based on the first filled cell of the range on the same row, and the header value of its respective column (row 1). It's easier to show my expected result than write it actually:
WiMZH.png
View 6 Replies
View Related
Mar 13, 2014
I have a data set with the weeks of the year as my column headers and a value of 1 (from a pivot table) in a distinct row value (removed for privacy) showing whether it existed in a given week based on pre defined criteria. What i need to do is ID the first (from the left) non blank cell in each row and then return that cells column header. I then need to do the same for the last (from the left) non blank cell in each row and then return that cells column header. On the attached I've put values in column N and O to show an example of what I need to return using a formula.
View 7 Replies
View Related
Jun 2, 2013
I've been using a nice colorful spreadsheet to use to schedule rooms for an organization. Visually it has worked out well but now I need to automatically generate a 'report' using that data. So...
I want to scan each row of a table and create another table with that data transformed.
That is, scan each row and whenever I find any text in a cell I want to return that text (the room being used) as well as the row and column header information (who is starting to use that room and on what date). I've attached a jpeg to show the example.
Ideally, when I scan the row and find text to return the headers I'd like the row scan to also find when the fill color changes and return that column header, too as that that would be the date that the room stops being used.
View 5 Replies
View Related
Jul 11, 2014
I 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.
View 2 Replies
View Related
Apr 29, 2009
I have a spreadsheet with several columns (all column names are in row 1).
I have 2 of column names. Note that their actual column# might change so the macro would have to look for the column name and NOT the column position ...
View 11 Replies
View Related
Jun 21, 2013
I'm trying to calculate a figure in a report tab that reads off the data tab and looks for a specific column based on the header of that column. My explanation is admittedly poor but I've attached a sample file with an outline of what I'm trying to do which I hope makes things clearer.
Sample File.xlsx
View 2 Replies
View Related
Aug 11, 2009
I have a spreadsheet an excerpt of which is as follows:
K L M N
1 Doors Security Vehicle Key
2 TRUE FALSE FALSE FALSE
3 FALSE TRUE TRUE FALSE
4 FALSE FALSE FALSE FALSE
Want I want to happen is to return the column header where there for a cell which contains "TRUE" in a blank column. If there isn't a cell with the value "TRUE" then to return "N/A". In the above example in blank column P for Row 2 would be "Doors", Row 3 would be "Security,Vehicle" and Row 4 would be "N/A".
In my actual spreadsheet I have 12 columns (L to W) which have the TRUE or FALSE values.
I know it has something to do with either HLOOKUP, INDEX and MATCH but can't seem to get it right. Any ideas?
View 4 Replies
View Related
Dec 12, 2009
I am trying to create a schedule using colors. The colors will indicate the start time, break time, lunch time, break time (second break) and end time. see attached document.
Thank you for attempting to solve this puzzle using formulas and or VBA code. I am trying to automate the schedule so the numerical times automatically appear when I "paint" the schedule.
View 5 Replies
View Related
May 16, 2013
I'd like a formula that'll return the column header by matching a lookup value with a table in the second sheet.
eg: sheet 1
Name
Cell
Region
John
111-2222
[Code] .......
The formula should match the name in A2, John, with value from the table in sheet 2 and return the correct region, this case North.
View 1 Replies
View Related
Jul 9, 2013
I'm trying to copy cells from a column in one workbook to cells in a column in another workbook based on what's in a header column.
Such as
Original workbook
Mat Qty
A 1
C 3
D 2
E 4
G 5
Total 15
Target workbook becomes
Mat Qty -> Mat Qty
A -> A 1
B -> B
C -> C 3
D -> D 2
E -> E 4
F -> F
G -> G 5
My code so far (which might not be worth reading) is:
Code:
Sub CopyColumnToWorkbook()
Dim sourceColumn As Range, targetColumn As Range, sourceColumnValue As Range, targetColumnValue As Range
Dim mySourceCell As Range, myTargetCell As Range
[code].....
I'm getting a runtime error ('1004'. Application-defined or object-defined error) on the Copy statement within the If sttement.
View 3 Replies
View Related
Feb 24, 2009
I have to use several dozen pivot tables a day. I already use a couple of macros to do a lot of the tedious formatting, but if I could automate this it would save me a lot more time. I have been trying to both write a code and modify code written by the recorder but have not even come close to anything that works. What I want is to have a macro that searches through the column header for predefined names and if it comes across one of these names it will insert a specific formula, based on its name, in the first cell below the header. Then it will autofill to the last row and move on to searching for the next predefined header name. If that name is not found, it will move to the next predefined header name. One major problem is that the formula to insert under any specific header name is based on other columns in the table that aren't always in the same range. So, not only do I need it to search for columns to place a formula in, that formula has to search for the appropriate column to find the correct value to use in the calculation.
For example, one predefined column name would be Avg Price. The formula for that column would be (Sales/Qty Sold), but these 2 columns could be located anwhere in the table. How can I get it, once it has found a column that needs a formula, to locate the correct column/cell to get the correct value from the sales and Qty Sold columns?
View 9 Replies
View Related
Jun 6, 2007
I'm trying to use VBA code to delete certain rows based on a couple of criteria as follows:
1) If column header is "TERMINAL NAME" and the cell value in that column is "BONDDESK", I need the entire row deleted.
2) Similiarly, if the column header is "PRODUCT TYPE" and the cell value in that column is "CORP", that entire row needs deleted as well.
The information is coming from another source, so the columns may change from day to day, i.e, "TERMINAL NAME" may be in Column L one day but in Column O another day.....
View 6 Replies
View Related
