automatic display of last 2 entries in tHe column to specified cel

[mike CBA]

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

 

[Shane Devenshire]

What if there are 6 equal last enry rows?

 

[Shane Devenshire]

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

 

[Shane Devenshire]

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$6,MAX(ROW(D$1$6)*(D$1$6<>""))+1-ROW(B2))=INDEX(D$1$6,MAX(ROW(D$1$6)*(D$1$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$6,MAX(ROW(D$1$6)*(D$1$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.

 

[Rick Rothstein]

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,COUNTA(D))=B1),"",INDEX(C:C,COUNTA(C:C)))

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

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

 

[Ashish Mathur]

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