Extracting Row And Column Header Coordinates From Value Search?
Mar 6, 2014
If the could be longer I would have included "when multiple cells can contain the same value"
I have a very large dataset that I would like to apply the following searches to, but I will include a smaller sample that gets at the point.
For example; in the following sheet:
Cat Dog Lizard
Red 1 2 3
Yellow 4 5 6
Green 7 8 1
If I search for "2", I would like to get back "Red" and "Dog". I was able (by looking at other posts) come up with a series of formula that allow me to obtain the sheet coordinates and convert them into column and row headers (using SUMPRODUCT Row/Column); however, when multiple cells contain the same value, the output becomes the sum of that coordinate and is no longer a reference to a specific header, so a search for "1" would not give me "Red" and "Cat". Ideally, if I searched for "1" I would get back "Red" & "Cat" as well as "Green" & "Lizard", but I would be satisfied with a formula that even just gave me back one of the two header pairs.
JordanSample Workbook.xlsx
View 5 Replies
ADVERTISEMENT
Mar 13, 2014
This is the script I have now:
[Code] ......
It works, unless there is a column before the "Categories" column that has the word "Categories" somewhere in its text. So basically if there is a column that has "Unit Categories" it finds that column when I really want it to find a column that has exactly "Categories" as the text and nothing else (but I don't want it to be case sensitive).
How do I force an exact match using this script?
View 1 Replies
View Related
Aug 22, 2013
i have a large table and i need separate x and y coordinates(to plot on a graph) of a particular repeated value that is known to me in a large table of numbers. how can i do this?
View 4 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
Apr 5, 2004
I am trying to search a range for all of the nonblank cells, when I find one, return the column header and the value in that column. Vlookup won't work on this. It will only return a specified column.
Employee performance
ID area one | two | three
12947 23 | | 19
12948 32 | | 20
12948 |17 | 52
What I am trying to do is create a communication sheet. Employee ID 12947, your performance in area one= ______, in area two = ______ and so on.
View 3 Replies
View Related
Apr 25, 2014
For column "B" count all the 1's if column header is equal to name in column A.
For Column "C" needs to be checked if a value was filled in column "I" if yes then check if in column "L" has a value, count all these values.
View 5 Replies
View Related
Jul 25, 2014
I am trying to write a formula where the column header of the row in which a value other than 0 exists, will display for each instance (row) where a value exists in an array spanning 3 columns. So the result cell could be any of the three column headers, or a combination thereof.
I started the formula in P2 of the GL Detail-2012 tab. File attached.
Here is what I started: =INDEX($M$1:$O$1,SUMPRODUCT(COUNTIF($M$2:$O$67756,))). Not working.
View 7 Replies
View Related
Jul 22, 2014
I would like to display the column header of the row in which a value other than 0 exists in a cell for each instance that value exists in an array spanning 3 columns. So the result cell could be any of the three column headers.
I started the formula in P2 of the GL Detail-2012 tab. (FILE TOO BIG)
Excerpt:
Resin Sand Eliminated Segment
- (12,896.65)-
- (6,570.85) -
- (11,503.80) -
- (13,188.50) -
If there is any value other than 0 in Resin, Sand or Eliminated columns, return that column's header under Segment.
Here is what I started: =INDEX($M$1:$O$1,SUMPRODUCT(COUNTIF($M$2:$O$67756,)))
View 2 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
Mar 3, 2014
[Code] ......
This works, UNLESS "My Column Header" is the last column, or second to last column, then it jumps left two columns, instead of landing on the correct column.
The purpose of this script is to select a cell directly in that column that I was searching for.
View 2 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
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
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
Mar 11, 2013
Macro for a repetitive task. I have data in every 3rd column (max of 11 columns) that I want to extract and append the data in column A in sheet 2. The data contains some blank cells and I'd like to remove them also.
I've attached an example in this workbook : tractdata.xlsx
View 4 Replies
View Related
Jan 31, 2013
I would like to add the column header to each of the row item and price, we maintain masters in the matrix format, butthe application supports only the row item mapping with the customer and pricing. Attached excel file
View 2 Replies
View Related
Feb 9, 2010
I need a formula to lookup and retrieve data from a table of values. Given an EMP-ID in column A of the row that contains the formula and given a column name in row 1 of the column that contains the formula, I need to do a lookup. So, in effect, I am doing a lookup based on two values .. the column header and the row header.
I have attached a small model to illustrate what I need. Cell C-3 in the first worksheet will contain the formula to lookup and retrieve the value in cell C-7 in the second worksheet, based on the values in cell C-1 (column header = “Database Col 2”) and in cell A-3 (row header = “257”) in the first worksheet.
View 2 Replies
View Related
Sep 14, 2007
Is there a way to reference a column depending on its header? Is there a way of doing this without the need of surveying one-by-one all the column headers with a for loop? I need this cuz if I have a very dinamic program which moves columns arround and depending on the state of the program the position of a column can change.
View 2 Replies
View Related
Jan 15, 2008
Have a set of dates as column headers across a worksheet. Rows are product names. When a product is delivered, the cell is marked "delivered" for the date (in the column header) it was delivered. Need a formula that will look across each row (product), and return the date of the first cell with the value "delivered". I have: =INDEX(F19:AS19,1,MATCH("delivered",F19:AS19,0)) <for row 19. right now, but its just giving me "delivered" as the value and I cant figure out why.
View 2 Replies
View Related
Jan 24, 2013
I have software (SAS Add-In for Microsoft Office) that loads external data into Excel. The data has a title, header, and rows/columns. It automatically creates a named range such as Table1. The named range does NOT include the headers.
Here is an example:
Code:
SASApp:SASHELP.CLASS
Name
Sex
[Code]....
As above, can I dynamically derive the column number based on the header row of the named range, where such named range refers to a table?
On a related topic, can I derive the column name given the column number? For example, in pseudocode:
Code:
For currCol = firstCol To (firstCol + lastCol - 1)
' Assume a function called ColumnName
Debug.Print ColumnName(currCol)
Next
View 1 Replies
View Related
Jun 23, 2008
Is there a way to link a header or a portion of a header to a specific cell?
View 14 Replies
View Related
Jul 7, 2014
I am looking at formula to return the column header, see example attached.
View 2 Replies
View Related
Jun 23, 2005
Can I change Coulmn Header Names... What I mean to say that , We Have Generally Column Names A, B, C, D, ... I would like to Change these names what ever I Want..
View 11 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 28, 2013
How do I lock a column header so that it will always stay at the top as I scroll down the page?
View 4 Replies
View Related
Jan 29, 2013
I would like to map each item in the row with the column header and the price for that item for the customer, similar to the below one, go through both the work sheets, i want the output in the rowitem view.
SKuIDItemNameCustomerRate
11682Item 1Cust 1312.56
11682Item 1Cust 2411.27
11682Item 1Cust 3411.27
[code]....
View 2 Replies
View Related
Mar 30, 2013
I created a Excel table with column headers in C15:H35 and the headers I want ONLY are in C15 (Item), F15 (Qty), G15 (Per Unit), and H15 (Total). In column D15 and E15 have Column 1 and Column 2 in them, I do not want them to be there how do I remove them without deleting the column?
ItemColumn1Column2QtyPer UnitTotal
View 8 Replies
View Related
May 13, 2013
I have a spreadsheet with a number of columns from a database export, where values in the column are either "TRUE" or "FALSE," Depending on whether or not the box was checked in the database. I am wondering if there is a way to populate a new cell with the text from the column if the value is "TRUE."
For example:
BOY
GIRL
John Doe
TRUE
FALSE
Is there a way that I could have another cell populated (I guess via function or macro) with "BOY," since the option for boy is true?
View 8 Replies
View Related
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
View Related
Mar 23, 2014
How can I change the font size on Excel Worksheet column and row headers... Not cell fonts but the Row and Column numbers outside of the table itself?
View 1 Replies
View Related
Mar 12, 2009
I have this spreadsheet, I have a title in cell A1, then the following column headers alternate betweens entries I want and those I don't (in this case A in Cell B1, an irrelevant value in C1, C in Cell D1, an irrelevant value in E1, D in Cell F1 etc...).
What I want to do is take the original value, so A from B1 and make it Aa, then make C1 contain the same A, but this time Ab.
It should be very obvious what I'm after from the spreadsheet.
View 3 Replies
View Related