what is the formula for automatically displaying the last 2 entries in
columns C and D to columns A and B. But, if the last 2 entries are the same,
only the 2nd last will be displayed. to illustrate:
1st scenario:
A B C D
5 10.50 20 10.50
10 11.00 15 11.00
5 10.50
10 11.00

2nd scenario:
A B C D
5 10.50 20 10.50
15 10.500
5 10.50
5 10.50

What if there are 6 equal last enry rows?

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"mike CBA" wrote:

what is the formula for automatically displaying the last 2 entries in
columns C and D to columns A and B. But, if the last 2 entries are the same,
only the 2nd last will be displayed. to illustrate:
1st scenario:
A B C D
5 10.50 20 10.50
10 11.00 15 11.00
5 10.50
10 11.00

2nd scenario:
A B C D
5 10.50 20 10.50
15 10.500
5 10.50
5 10.50

Does the order of the the last two items when placed in a and B matter?

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"mike CBA" wrote:

what is the formula for automatically displaying the last 2 entries in
columns C and D to columns A and B. But, if the last 2 entries are the same,
only the 2nd last will be displayed. to illustrate:
1st scenario:
A B C D
5 10.50 20 10.50
10 11.00 15 11.00
5 10.50
10 11.00

2nd scenario:
A B C D
5 10.50 20 10.50
15 10.500
5 10.50
5 10.50

A formula for A1 and B1 could be

=INDEX(C$1:C$6,MAX(ROW(C$1:C$6)*(C$1:C$6<""))+1-ROW(A1))

which will put the last entry at the top, not in the same order as the
original data.

The entry in B2 could be

=IF(AND(INDEX(D$1:D$6,MAX(ROW(D$1:D$6)*(D$1:D$6<" "))+1-ROW(B2))=INDEX(D$1:D$6,MAX(ROW(D$1:D$6)*(D$1:D$6< ""))+1-ROW(B1)),INDEX(C$1:C$6,MAX(ROW(C$1:C$6)*(C$1:C$6< ""))+1-ROW(B1))=INDEX(C$1:C$6,MAX(ROW(C$1:C$6)*(C$1:C$6< ""))+1-ROW(B2))),"",INDEX(D$1:D$6,MAX(ROW(D$1:D$6)*(D$1:D $6<""))+1-ROW(B2)))

You will need to modify it to for A2. This hides the result provided both
of the items on the last two lines match. You can not use the same formulas
on both rows nor or row 2 can you use the same formulas in column A and B.

These are array formulas and must be entered by pressing Shift+Ctrl+Enter.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"mike CBA" wrote:

what is the formula for automatically displaying the last 2 entries in
columns C and D to columns A and B. But, if the last 2 entries are the same,
only the 2nd last will be displayed. to illustrate:
1st scenario:
A B C D
5 10.50 20 10.50
10 11.00 15 11.00
5 10.50
10 11.00

2nd scenario:
A B C D
5 10.50 20 10.50
15 10.500
5 10.50
5 10.50

Your post raises several questions. Shane has posted a couple of them; I am
wondering if the last 2 cells in C are the same and the last 2 cells in D
are not (and vice versa), are they then considered different such that both
should be shown in A and B? Also, could there be any empty cells in columns
C and D?

Here is my guess as to what you want.

A1:
=INDEX(C:C,COUNTA(C:C)-1)

A2:
=IF(AND(INDEX(C:C,COUNTA(C:C))=A1,INDEX(D:D,COUNTA (D:D))=B1),"",INDEX(C:C,COUNTA(C:C)))

B1:
=INDEX(D:D,COUNTA(D:D)-1)

B2:
=IF(AND(INDEX(C:C,COUNTA(C:C))=A1,INDEX(D:D,COUNTA (D:D))=B1),"",INDEX(D:D,COUNTA(D:D)))

--
Rick (MVP - Excel)


"mike CBA" wrote in message
...
what is the formula for automatically displaying the last 2 entries in
columns C and D to columns A and B. But, if the last 2 entries are the
same,
only the 2nd last will be displayed. to illustrate:
1st scenario:
A B C D
5 10.50 20 10.50
10 11.00 15 11.00
5 10.50
10 11.00

2nd scenario:
A B C D
5 10.50 20 10.50
15 10.500
5 10.50
5 10.50

Hi,

I'm not sure if I understood your question correctly but try this. In cell
A3, enter the following formula =if(and(C3=C4,D3=D4),C3,""). Now copy this
to the right and down.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"mike CBA" wrote in message
...
what is the formula for automatically displaying the last 2 entries in
columns C and D to columns A and B. But, if the last 2 entries are the
same,
only the 2nd last will be displayed. to illustrate:
1st scenario:
A B C D
5 10.50 20 10.50
10 11.00 15 11.00
5 10.50
10 11.00

2nd scenario:
A B C D
5 10.50 20 10.50
15 10.500
5 10.50
5 10.50