I know the column number (57) and want the formula to return the column name (BE) Auto Merged Post Until 24 Hrs Passes;not using vba that is! or if so , a custom function so macros dont have to be run.
View 3 Repliesi want to sort a column in such a way that it starts with those cells having the highest number of letter. For example:
before:
AA
AAA
A
AAAAAA
AAAA
AAAAAAAAAAA
I want it to look like:
AAAAAAAAAAA
AAAAAA
AAAA
AAA
AA
A
Ofcourse the real list doesnt contain only "A"s. It contains of words and sentences.
How can i sort columns A as mentioned? The order of column A with other columns should not be destroyed be the sorting process.
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),)))
I'm creating a macro that creates a co-ocurrence matrix from variables that are adjacent to each other.
In order to proceed, I need to know how to return the column number of the first (leftmost) column in a range that the user selects.
I am trying to compare two colums. They both contain numbers mixed with letters. I am wanting to match only the numbers in both not the letters. Example:
column a = m454 column b = fsh454-1
m543 fst998-2
m998 fsm434-1
my match is m454 and fsh454-1, m998 and fst998-2. The items can be in any order in the column. The end result I want to indicate the match by putting an X by column a item that matches column b.
how to find text from a row in column, where is a lot of text and return row number, of located text?
View 15 Replies View Relatedi have 2 sheets one called "IS" and the other called "AS" in cell a2 of "IS" is a number that i need to have excel look up in column a of "AS" once it find that number i need it to return the number thats in column e of that row to sheet "IS"column d. summary: a2 of "IS" looked up on sheet "AS" and returns the number in column e to cell d2 of "IS"
View 3 Replies View RelatedI have a column of cells, some blank, some containing just numbers, some containing just letters, some containing numbers preceded by the the letter 'p'
E.g.
frt
34.2
36
p34.5
In the cells containing the number preceded by the 'p' - i would like to remove the 'p' leaving just the number, with all other cells remaining unchanged.
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
Is there a VBA command to get the letters, instead of the numbers, of the column of a selected cell?
I have to letter a list whihc means setting up a loop using character codes.
I may have to go into double letters so I am working on how I would set up the loop for if and when it gets past 90 and starts on double letters. so far the highest is the letter "U"
of course the easiest would be to pick up a column value as a letter
NT values do not get a number
A_____ _____NT###
B_____1_______C####
C_____2_______RMK###
D____ _______NT####
i am guessing the loop might involve some arithmetic test between the count and the character set 65-90. or maybe a mod thing.
Can column() return a letter instead of a number? I am planning to use it with INDIRECT? Is that possible?
=INDIRECT(row() & column())?
How to return column number (not letter)?
View 5 Replies View RelatedI have a column with zeros, positive numbers, and negative numbers. I need to find the last (bottom) positive number in the column.
Ex:
Column A has -
$50
$0
-$10
$22
$0
-$14
I want a formula or function in B1 that would return $22. However if I add a new row under the -$14 which contains $37, the formula should then return $37.
I have been googling all night, and I can't make this work.
I have a Range variable that points to a particular cell. If I wanted the address, I would do, cellName.Address. This returns, for example, "$F$3". If I wanted to have the column "F" as a number (column F would be the 6th column, so 6), how could I do that without storing some kind of array of letters and number equivilants. It would also have to work past the 26th column when the column addresses switch to, like, "AA", "AB" etc.
View 3 Replies View RelatedI 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.
I'm using the LOOKUP function to find the largest number and then return the name from a different column, it looks like this:
=LOOKUP(LARGE(round1!$F$2:$F$65,1),round1!$A$2:$A$65)
but all I get is hashN/A?
I am trying to see if there is a way using count or another option, of using one formula to look in a column and then spit out how many of each item are there:
i.e: lets say it says a, b, c, d etc...
Can a formula go through and say you have 3 of a, 5 of b and so on
I understand the basic of count sum iserror etc, just not sure if excel can do what I am wanting or if i should use access.
I have the following two rows that I'm looking to return a single column number that maxes two criteria. In the example, 70, 30 would return column 7. I'm not looking to create a dummy row the combines the two. Reason for using MATCH or HLOOKUP is that the two values may not be the values in the given table.
View 9 Replies View RelatedI have a got a table ( 10x30) with random numerical values.
How can I return a column number with maximum value?
Values will be changed periodically so the maximum number could be in different column each time
Example:
A-B-C-D-E-F-G
1-2-3-4-5-6-7
2-6-8-18-1-1-8
2-5-6-9-12-1-7
The results will be 4.
I am looking for a formula to return the column number of the array when a specific text is found, in this case the text is "Yes"
The Array will only ever be 5 cells beside each other in a row
for example, M4:Q4, will be as follows - No No No Yes No
I want a formula to look at these 5 cells and return the number 4 as that is where the "Yes" value is
I would like a flexible Formula to Return the Column Number of individual
Numeric Labels and their Numeric Value. I have a 2-Row by many Columns
chart/ grid layout. My 1st Column, Column Number "1" is Excel Column Letter
"F".
Numeric Label 17 is housed in Column Number "12" per my chart , its Numeric
Value 5 is housed on the next Row - directly below the Numeric Label.
Sample Data: ...
Scenario:
To Return the correct Column Number: locate the Numeric Label 17 and its
Numeric Value on the Row below, Numeric Value is 5. The Numeric Value is to
be increased by a Value of 1 (one) - NEW Numeric Value = 6. The Column
Number Returned should reflect Numeric Label of 17 remains the same but the
Numeric Value is increased by one.
To Return a Column Number representing the Numeric Label 17 and its original
Numeric Value +1 (plus one): the Numeric Label is still 17 but the Numeric
Value is NOW 6. From the oringal Column Number that housed Numeric Label 17,
that is Column Number "12", SEARCH in Ascending order: moving LEFT from
Column Number "12" and using the Numeric Value as the 1st (first) search
order and the Numeric Label as the 2nd (second) Ascending search order.
SEARCH the Numeric Value Row until the first Numeric Value either equal to 6
or more than 6 is found. Then Return the Column Number of the Column to the
Right of that Numeric Value.
Required Result:
Return the Column Number based on the Numeric Label 17 and the NEW Numeric
Value of 6, search in Ascending order - First search order based on Numeric
Value 6 - Second search order based on Numeric Label 17.
Returned Result should be Column Number 10.
Using a Dynamic Named Range "Data", I would like a Formula to return the Row
Number of the "LAST" instance of a duplicate numeric value - repeating twice
consecutively in the same column.
I need a formula that returns the Nth number from a single column that contains a small number of values within a span of 1000 cells. E.g., within 1000 cells there are 36 with numbers and the remaining cells are blank. I need to obtain the first occurrence of a number in the column, then the second, third... to the thirty-sixth. The end result would be a column with 36 numbers with no blanks in between without loosing the correct order of the numbers.
I have excel 2003 and have been tooling around with the index and lookup functions with no success.
I have a formula that I use to tally data and return the number of times a particular value is present in column C.
IF(COUNTIF($C$2:C2,C2)=1,SUMIF(C:C,C2,E:E),"")
I copy this formula down the sheet until arriving at the last input in column "C".
Can someone tell me how to write this in code. All I would like visible in Column "F" is the output not the formula.
I am trying to use some vba match function code to return the column number of the matching date. The date will be stored in a date variable.
Every time I run this code I normally get a match error even though the date is in the worksheet and the variable matches that date.
See below:
[Code] .....
on one sheet we have a summary of the main list, which includes totals of money recieved, totals of all the different sources (ie, where they heard about us from), the totals of the frequencies they pay (ie, how many donate monthly, quarterly...) ... etc. on the next sheet we have the "main" list of donors, their IDs, amounts, frequency, source ...
the totals on the first sheet are updated manually, but i want to change that as there are a great number of errors.
I have a column of domains.
I need to add "http://" to the beginning of these domains. How can I do this?
EX. I have column A with about 27 cells of domains "website.com". I want to add the "http://" to the front of them.
Using =column() in cell A1 returns "1" as column A is column 1 with b being 2 etc.
How can I get it to return "A".
I am using the Column() Function for referencing purposes... what I want this function to do is return the "letter form" of the column position rather than the "number Form"... is there anyway to change this without switching to R1C1 form?
i.e. Column(B4) yields "2"
I want it to give me "B"
I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.
The range containing all the data
A
B
C
1
Cat 1
January 1, 2014
John
[Code] ..........
I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.
For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4
The best try I had for the formula in C7 was
{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}
This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.
I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.