Offset

B

Brian

I have several columns of data, Lets say A to F. Some of these columns have
data and some do not. Column G would be where my formula would be. At the top
of each column is a date. The data maybe in any row and cell between A & F

Based on the info above, I would like to be able to find the last entry in a
row, take that value in that cell as well as the date on that specific column
and make both sets of data, part of formula.

Since I will copy this formula down Column G on each row, my reference to
the date on each column is always changing. Is there any formula that will
allow me to find the last entry in a row, take that value and the associated
date into a formula. I am able to find the last values in a row, but can not
find the formula to use if I want to get the date from the column where the
last entry was found.

Row A B C D E F
G
1 Date 1 Date 2 Date 3 Date 4 Date 5 Date 6
Formula Result
2 1 2 3
3 & Date 3
3 5 6
6 & Date 5
4 3 5
5 & Date 4
5 7
3 3 & Date 6
6 4 4
4 & Date 5

Formula I am using forlast value in row is:

LOOKUP(MAX(A2:B2)+1,A2:B2
 
H

Herbert Seidenberg

Assuming your data looks like this:
Date 1 Date 2 Date 3 Date 4 Date 5 Date 6 Results
1 2 3 4 5 5Date 5
5 6 7 8 8Date 4
3 5 7 7Date 3
7 9 9Date 2
4 4Date 1

Tools > Options > General > R1C1 Ref Style
Select the 5 x 6 array of numbers and name it array1.
Select the 1 x 6 array of dates and name it ddate.
Under Results, enter this formula
=INDEX(array1,,COUNTA(array1 R))&INDEX(ddate,,COUNTA(array1 R))
Revert to A1 style, if desired, after you enter the formulas.
 
D

Domenic

Try...

G2, copied down:

=LOOKUP(9.99999999999999E+307,A2:F2)&" &
"&LOOKUP(9.99999999999999E+307,A2:F2,$A$1:$F$1)

Hope this helps!
 
B

Brian

Works great, thank you very much.
Is there any why to look at the second last set of data from the end and
take that data as well, no mater what coulm it is in.
 
H

Herbert Seidenberg

Assuming there are at least 2 items in each row
and no gaps between items
and your data is arranged like this:
Date 1 Date 2 Date 3 Date 4 Date 5 Date 6 Results
9 1 6 5 2 5 2Date 5
5 6 7 8 1 8Date 4
4 3 5 9 5Date 3
7 9 5 9Date 2
4 3 4Date 1

Name the 5 x 6 array of numbers array1.
Name the 1 x 6 array of dates ddate.
The Results formula (in R1C1 ref style) is
=INDEX(array1,,COUNTA(array1 R)-1)&INDEX(ddate,,COUNTA(array1 R)-1)
Revert to A1 ref style, if desired, after entering formula.
 
D

Domenic

Brian said:
Works great, thank you very much.
Click to expand...

You're very welcome!
Is there any why to look at the second last set of data from the end and
take that data as well, no mater what coulm it is in.
Click to expand...

First, define the following name...

Insert > Name > Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Ok

Then, use the following formula...

=LOOKUP(BigNum,A2:INDEX(A2:F2,MATCH(BigNum,A2:F2)-1))&" &
"&LOOKUP(BigNum,A2:INDEX(A2:F2,MATCH(BigNum,A2:F2)-1),$A$1:INDEX($A$1:$F$
1,MATCH(BigNum,A2:F2)-1))

Hope this helps!
 
B

Brian

Unfortunately my data will have voids in them. There could be data in Date2
and Date6 and nothing in Date3, Date4, Date5. Data location in the columns
will change on each row. I would want to be able to use the data in the last
used column (whatever column it maybe) as well as the second last column
(whatever column it maybe) and take these 2 data points with the date headers
and use them in a formula as I go down each row.
 
Top