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

  • Thread starter Thread starter mike CBA
  • Start date Start date
M

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
 
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.
 
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)))
 
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
 
Back
Top