find column letter

[Stuart WJG]

I need a formula that can look for a date in
a cell that matchs a date in a range and then enter column letter in the
formula cell
Formula in A1
Variable cell (date) A2
Range B1:ah6

 

[Gary''s Student]

In A1 enter:

=col_id(A2,B1:AH6)


Where col_id is the following UDF:

Function col_id(r As Range, tbl As Range) As String
Dim d1 As Date, cel As Range
col_id = ""
d1 = r.Value
For Each cel In tbl
If cel.Value = d1 Then
col_id = Split(cel.Address, "$")(1)
Exit Function
End If
Next
End Function

 

[Bernie Deitrick]

Stuart,

To use worksheet functions, you can only check one row at a time:

=SUBSTITUTE(SUBSTITUTE(ADDRESS(1,MATCH(A$2,B1:AH1,FALSE)+1),"$1",""),"$","")

and then copy down for each row you want to check.

HTH,
Bernie
MS Excel MVP

 

[JLatham]

Well, here's part of an answer. This formula will return the column
identifier based on a column number. In the example, the column number is
assumed to be in cell E4:
=IF(E4>26,CHAR(CEILING(E4/26,1)+63),"")&CHAR(IF(MOD(E4,26)=0,26,MOD(E4,26))+64)

The original source for that, I believe, is John Walkenbach. It handles
anything up to column number 702 (ZZ) which was fine for Excel 2003 and
earlier versions.

To give a more complete solution, I need to know more about where your dates
are at in that range of B1:AH6 - are they in a particular column (in which
case you'd already have the answer) or on a particular row, or is the entire
matrix filled with dates?

 

[Pete_UK]

Put this in A1:

=IF(ISNA(MATCH(A2,$B$1:$AH$1,0)),"",IF(MATCH(A2,$B$1:$AH
$1,0)>25,"A"&CHAR(MATCH(A2,$B$1:$AH$1,0)+39),CHAR(MATCH(A2,$B$1:$AH
$1,0)+65)))


Hope this helps.

Pete

 

[Pete_UK]

Sorry, I thought your range was B1:AH1 - ignore my post.

Pete

 

[JLatham]

Yeah, I was kind of thinking the same thing - or that the date would be on
one of the rows. But realized he has a whole matrix and I didn't know what
was in it. Looks like Gary''s Student has given a solution that should deal
with that eventuality although I was kind of hoping to stay away from a UDF
myself.

 

[Stuart WJG]

Hi
Understand first part but what is UDF ?

In A1 enter:

=col_id(A2,B1:AH6)


Where col_id is the following UDF:

Function col_id(r As Range, tbl As Range) As String
Dim d1 As Date, cel As Range
col_id = ""
d1 = r.Value
For Each cel In tbl
If cel.Value = d1 Then
col_id = Split(cel.Address, "$")(1)
Exit Function
End If
Next
End Function

 

[Stuart WJG]

Hi there
the dates are in the range sya b1 to z1.
say the date of 01/08/08 is in B1 in the range.
I need to able to enter a date say in A2
and then put a formula in A1 that looks at the date in A2 say 01/08/08 and
returns the column later in this case as B in say a3

 

[JLatham]

In that case, Pete_UK has a good solution (the others are also good, I just
read his a little more because he made same assumption I almost did at first)
- but change the B1:AH1 in his formula to B1:Z1 to be more exact.

Actually his B1:AH1 would probably still work, since you're going to
encounter the date (or not) by the time you get to Z anyhow and it'll never
have to look beyond Z in that case.

 

[JLatham]

See his reply to my question - looks like you gave him good solution anyhow.

 

[Gary''s Student]

UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

 

[MartinW]

Hi Stuart.

Here's something else to play with,

Put this in A3 and drag down to A8
=IF(ISNA(MATCH($A$2,B1:AH1,0)),"",MATCH($A$2,B1:AH1,0))

Then put this in A1
=ADDRESS(MATCH(MAX(A3:A8),A3:A8,0),MAX(A3:A8)+1,4)

It will return the address of the cell where the match with A2 is made,
however,
will fall down if there is more than one match.

If there is going to be more than one match then the same approach
will work but it will need some modifications.

HTH
Martin

 

[Pete_UK]

If your date range only goes up to Z1, so there will only be one
column letter returned, then you can simplify my formula considerably
as follows:

=IF(ISNA(MATCH(A2,$B$1:$Z$1,0)),"",CHAR(MATCH(A2,$B$1:$Z$1,0)+65))

Hope this helps.

Pete