software

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 amp; 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 amp; Date 3
3 5 6
6 amp; Date 5
4 3 5
5 amp; Date 4
5 7
3 3 amp; Date 6
6 4 4
4 amp; Date 5

Formula I am using forlast value in row is:

LOOKUP(MAX(A2:B2) 1,A2:B2Assuming your data looks like this:
Date 1Date 2Date 3Date 4Date 5Date 6Results
12345 5Date 5
5678 8Date 4
357 7Date 3
79 9Date 2
4 4Date 1

Tools gt; Options gt; General gt; 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))amp;INDEX(ddate,,COUNTA(array1 R))
Revert to A1 style, if desired, after you enter the formulas.Try...

G2, copied down:

=LOOKUP(9.99999999999999E 307,A2:F2)amp;quot; amp;
quot;amp;LOOKUP(9.99999999999999E 307,A2:F2,$A$1:$F$1 )

Hope this helps!

In article gt;,
Brian gt; wrote:

gt; I have several columns of data, Lets say A to F. Some of these columns have
gt; data and some do not. Column G would be where my formula would be. At the top
gt; of each column is a date. The data maybe in any row and cell between A amp; F
gt;
gt; Based on the info above, I would like to be able to find the last entry in a
gt; row, take that value in that cell as well as the date on that specific column
gt; and make both sets of data, part of formula.
gt;
gt; Since I will copy this formula down Column G on each row, my reference to
gt; the date on each column is always changing. Is there any formula that will
gt; allow me to find the last entry in a row, take that value and the associated
gt; date into a formula. I am able to find the last values in a row, but can not
gt; find the formula to use if I want to get the date from the column where the
gt; last entry was found.
gt;
gt; Row A B C D E F
gt; G
gt; 1 Date 1 Date 2 Date 3 Date 4 Date 5 Date 6
gt; Formula Result
gt; 2 1 2 3
gt; 3 amp; Date 3
gt; 3 5 6
gt; 6 amp; Date 5
gt; 4 3 5
gt; 5 amp; Date 4
gt; 5 7
gt; 3 3 amp; Date 6
gt; 6 4 4
gt; 4 amp; Date 5
gt;
gt; Formula I am using forlast value in row is:
gt;
gt; LOOKUP(MAX(A2:B2) 1,A2:B2

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.

quot;Domenicquot; wrote:

gt; Try...
gt;
gt; G2, copied down:
gt;
gt; =LOOKUP(9.99999999999999E 307,A2:F2)amp;quot; amp;
gt; quot;amp;LOOKUP(9.99999999999999E 307,A2:F2,$A$1:$F$1 )
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; Brian gt; wrote:
gt;
gt; gt; I have several columns of data, Lets say A to F. Some of these columns have
gt; gt; data and some do not. Column G would be where my formula would be. At the top
gt; gt; of each column is a date. The data maybe in any row and cell between A amp; F
gt; gt;
gt; gt; Based on the info above, I would like to be able to find the last entry in a
gt; gt; row, take that value in that cell as well as the date on that specific column
gt; gt; and make both sets of data, part of formula.
gt; gt;
gt; gt; Since I will copy this formula down Column G on each row, my reference to
gt; gt; the date on each column is always changing. Is there any formula that will
gt; gt; allow me to find the last entry in a row, take that value and the associated
gt; gt; date into a formula. I am able to find the last values in a row, but can not
gt; gt; find the formula to use if I want to get the date from the column where the
gt; gt; last entry was found.
gt; gt;
gt; gt; Row A B C D E F
gt; gt; G
gt; gt; 1 Date 1 Date 2 Date 3 Date 4 Date 5 Date 6
gt; gt; Formula Result
gt; gt; 2 1 2 3
gt; gt; 3 amp; Date 3
gt; gt; 3 5 6
gt; gt; 6 amp; Date 5
gt; gt; 4 3 5
gt; gt; 5 amp; Date 4
gt; gt; 5 7
gt; gt; 3 3 amp; Date 6
gt; gt; 6 4 4
gt; gt; 4 amp; Date 5
gt; gt;
gt; gt; Formula I am using forlast value in row is:
gt; gt;
gt; gt; LOOKUP(MAX(A2:B2) 1,A2:B2
gt;

Assuming there are at least 2 items in each row
and no gaps between items
and your data is arranged like this:
Date 1Date 2Date 3Date 4Date 5Date 6Results
9165252Date 5
567818Date 4
43595Date 3
7959Date 2
434Date 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)amp;INDEX(ddate,,COUNTA(array1 R)-1)
Revert to A1 ref style, if desired, after entering formula.In article gt;,
Brian gt; wrote:

gt; Works great, thank you very much.

You're very welcome!

gt; Is there any why to look at the second last set of data from the end and
gt; take that data as well, no mater what coulm it is in.

First, define the following name...

Insert gt; Name gt; 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))amp;quot; amp;
quot;amp;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!

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.

quot;Herbert Seidenbergquot; wrote:

gt; Assuming there are at least 2 items in each row
gt; and no gaps between items
gt; and your data is arranged like this:
gt; Date 1Date 2Date 3Date 4Date 5Date 6Results
gt; 9165252Date 5
gt; 567818Date 4
gt; 43595Date 3
gt; 7959Date 2
gt; 434Date 1
gt;
gt; Name the 5 x 6 array of numbers array1.
gt; Name the 1 x 6 array of dates ddate.
gt; The Results formula (in R1C1 ref style) is
gt; =INDEX(array1,,COUNTA(array1 R)-1)amp;INDEX(ddate,,COUNTA(array1 R)-1)
gt; Revert to A1 ref style, if desired, after entering formula.
gt;
gt;