-1

i have an excel sheet in which the first few columns contain titles and information and the rest are repetitive 3 columns sets (i'll refer to as col1,col2,col3), containing dates.

i added an other 3 columns set in which i want to show the last date set in that row(i'll refer to as last1,last2,last3). currently i set "last3" to look for the last non blank cell in row, last2 to look for the one before and last1 2 before. problem is, not every time i fill the information i fill the whole set, meaning i could have a situation where the set contains col1 col2 values but not col3, then last3 actually takes a col2 value and messes up everything.

is there a way (please please no VBA programming) to write an array function of some sort that looks for the last 3 col set containing any value, and copying it to the last col set? thanks!!

sn123
  • 1
  • Please clarify your question and provide what you have tried, as well as usable (textual) samples of your data that can be copied into a worksheet for testing. Suggest you read the HELP topics for [How do I Ask a Good Question](http://stackoverflow.com/help/how-to-ask), and also [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). Then, either edit your original question to improve it, or ask a new one if the original has been closed. – Ron Rosenfeld Aug 10 '20 at 09:19

1 Answers1

0

I have cooked up this formula for you. It isn't pretty and it's volatile (meaning, it will slow down your worksheet if you have a large quantity of these formula in it) but it does the job. It's the same formula for all columns and all rows.

=INDEX(INDIRECT(ADDRESS(ROW(),1)&":"&ADDRESS(ROW(),COLUMN()-1)),,COUNTA(INDIRECT(ADDRESS(ROW(),4)&":"&ADDRESS(ROW(),COLUMN()-1)))-MOD(COUNTA(INDIRECT(ADDRESS(ROW(),4)&":"&ADDRESS(ROW(),COLUMN()-1))),3)-(2-MOD(COLUMN()-4,3))+(4-1))

In this formula the number 4 stands for the first column of the first set of 3. If you have more columns to the left or fewer, change this number wherever you find it.

The number 3 stands for sets of 3 columns. the "2" in 2-MOD(COLUMN()-4,3)) is also related to this number being used here to reverse the result of Mod(,3) which will be 1, 2 or 0.

-1 generally stands for an adjustment, for example COLUMN()-1 is the number of the column to the left of where the formula resides. But in ADDRESS(ROW(),1) the "1" specifies column A.

The formula specifies a range starting from column 4 to the last column to the left of the formula and counts the number of entries in it, excluding cells of its own group. The 3 entries to be retrieved are in the range extending from itself 2 cells to the left.

If you deploy this formula, replace it with hard values when you add the same formula to the right of columns where it is already deployed. Use Copy/Paste Values (after copying the formulas to the right. This is to reduce the load on your worksheet.

BTW, I took a hard look at this thread, where they deal with columns. Some of the solutions there can be reformulated to work on rows but I failed to make them work on sets of 3.

Variatus
  • 14,293
  • 2
  • 14
  • 30