5

I currently have a spreadsheet of transactions, and I want to write a formula that will help me extract from each row a customer's most recent transaction.

Additionally, I want to then pull the column header associated with each most recent transaction, as to isolate the date on which the transaction occurred.

I have found the formula: 

=INDEX($E$1:$CO$1, MATCH(TRUE,INDEX(E2:CO2<>0,),0))

This is giving me the most recent transaction; it is searching each row for the first non-zero instance.

I need the LAST non zero instance. Or alternatively, I need to know how to make this formula work from right to left, instead of left to right.

Any help is much appreciated!

Improve this question

2 Answers 2

Reset to default
13

No need to alter your data, you can use a LOOKUP function to find the last non-zero and get the header like this

=LOOKUP(2,1/(E2:CO2<>0),E$1:CO$1)

This works because the 1/(E2:CO2<>0) part returns an array of either 1s where the cell isn't zero......or #DIV/0! errors where it is. LOOKUP then won't find 2 in that array so it matches with the last 1, i.e. the last column with a non-zero value.....and it then returns the corresponding value from the header row.

If you actually want the last number in a row (including zero) this is even simpler, just

=LOOKUP(9.99E+307,E2:CO2,E$1:CO$1)

Improve this answer
2

Based off here and here, the quick answer is you likely need to reverse you data. Excel doesn't have any functions that move right to left.

Assuming the data you wanted to reverse extended from A2 to E2:

=INDEX($A$2:$E$2,6-COLUMNS($A$2:A2))

Where 6 is your number of columns (5) + 1. If you prefer not to use a hardcoded value (6), as pointed out in the comments from the second link, you can use something like COUNTA($A$2:$E$2)+1. The formula would thus look like:

=INDEX($A$2:$E$2,COUNTA($A$2:$E$2)+1-COLUMNS($A2:A2))

Note that the formula hinges on the final value of COLUMNS (A2 to start) to derive the correct cell value. Assuming you copy and paste, you would end up with a row of cells as follows (assuming you wanted to display all 5 pieces of data):

=INDEX($A$2:$E$2,6-COLUMNS($A$2:A2))
[...]
=INDEX($A$2:$E$2,6-COLUMNS(A$2:$E2))

An example of what this looks like:

Reverese_Excel_Data

Including the formula to find the first non-zero value:

Excel_Fined_First_Non-Zero_Value

Improve this answer
2
  • Thank you for your answer! My data is currently a pivot table with 2600 rows and 80 columns. Do you know an easier way to reverse the order of the data for a spreadsheet this large?
    – user466699
    Jul 7, 2015 at 19:39
  • Add a helper row with =col() in it. Then sort the columns by that row. Or add a helper column with =row() in it and sort the rows by that column.
    – KJ6BWB
    May 15, 2020 at 18:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .