1

I have a range of data in four columns in excel as below. Column A, B are in Text format, columns C, D are generated using reference link. C(F) and D(F) are showing the formula bar values of C and D. Since Cells A10, A11, B10, B11 are empty C10, C11, D10, D11 show '0'.

I am trying obtain last 5 non zero values in such order as shown in Columns E and F, but based from Columns C and D only and not directly from columns A or B.

     A       B      C(F)    D(F)      C       D      E      F    
 1 Name     Val     Name    Val     Name     Val   Name     Val
 2 abc01    101     =A2     =B2     abc01    101   abc04    104
 3 abc02    102     =A3     =B3     abc02    102   abc05    105
 4 abc03    103     =A4     =B4     abc03    103   abc06    106
 5 abc04    104     =A5     =B5     abc04    104   abc07    107
 6 abc05    105     =A6     =B6     abc05    105   abc08    108
 7 abc06    106     =A7     =B7     abc06    106
 8 abc07    107     =A8     =B8     abc07    107
 9 abc08    108     =A9     =B9     abc08    108
10                  =A10    =B10      0       0
11                  =A11    =B11      0       0

If a row of data is added to columns A and B, columns E and F should be reflecting as below.

     A       B      C(F)    D(F)      C       D      E      F    
 1 Name     Val     Name    Val     Name     Val   Name     Val
 2 abc01    101     =A2     =B2     abc01    101   abc05    105
 3 abc02    102     =A3     =B3     abc02    102   abc06    106
 4 abc03    103     =A4     =B4     abc03    103   abc07    107
 5 abc04    104     =A5     =B5     abc04    104   abc08    108
 6 abc05    105     =A6     =B6     abc05    105   abc09    109
 7 abc06    106     =A7     =B7     abc06    106
 8 abc07    107     =A8     =B8     abc07    107
 9 abc08    108     =A9     =B9     abc08    108
10 abc09    109     =A10    =B10    abc09    109
11                  =A11    =B11      0       0
1

If all the non-zero values appear before the first zeroes, you can search for the zero and then index backwards.  Enter

  • =INDEX(C:C, MATCH(0,C:C,0)-5, 1) into E2
  • =INDEX(C:C, MATCH(0,C:C,0)-4, 1) into E3
  • =INDEX(C:C, MATCH(0,C:C,0)-3, 1) into E4
  • =INDEX(C:C, MATCH(0,C:C,0)-2, 1) into E5
  • =INDEX(C:C, MATCH(0,C:C,0)-1, 1) into E6

and then select them and drag/fill to the right, into F2:F6.  As I said at the beginning, this searches Column C for a 0, and then fetches the values from the five previous rows.

| improve this answer | |
  • May I make a suggestion? If you replace the "-5" in the first formula with +ROW()-7, then that one formula can be filled to the right and down. – Bandersnatch Oct 12 '17 at 8:52
  • 1
    Yeah, that had occurred to me. I chose to keep it simple for ease of understanding by new users. – Scott Oct 12 '17 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.