1

I'm looking for a way to populate every 3 rows in a column from values in every 3rd cell from in an adjacent column.

So I am trying to go from this:-

enter image description here

To This:-

enter image description here

I have thousands of rows, so I am trying to find a way to make this as painless as possible.

Thanks

Improve this question

4 Answers 4

Reset to default
2

Try:

=IF(D2<>"", D2, IF(D3<>"", D3, D4))

Best regards, Lukas

Improve this answer
6
  • It's good one +10 ☺
    – Rajesh Sinha
    Sep 17, 2019 at 9:24
  • @RajeshS thx... :-) Hope, there is not a gap greater than 2 rows... :-)
    – Muji
    Sep 17, 2019 at 9:25
  • That is perfect!! Thanks Muji
    – Vard0
    Sep 17, 2019 at 9:29
  • @Vard0, You are welcome. Please, confirm my answer. Thx. Lukas
    – Muji
    Sep 17, 2019 at 9:31
  • 1
    If cells contains spaces or any another "invisible" symbols (not empty value) the formula will fail.
    – Akina
    Sep 17, 2019 at 12:20
1

If values in column D are numbers, then:

E2=MAX(D2:D4)

Drag this formula down.

If values in column D are strings which must be treated as numbers, then:

E2=MAXA(VALUE(D2:D4))

Formula must be entered as array formula (by pressing Ctrl+Shift+Enter). Then drag it down. If some cell contains a value which cannot be converted to number type by VALUE() function then you'll obtain #VALUE error for according cells.

Improve this answer
0

Manually set the cells in the first three rows to all be equal to the first calculated value.

Then, select those 3 cells.

If there's anything in the column to the right of those cells, you may be able to double click on the square "◾" icon in the bottom right corner of the selection's outline to get it to auto-populate the remaining cells.

Otherwise, drag the square "◾" icon in the bottom right corner of the selection's outline to the last cell you want populated.

This should result in every cell having a formula with a simple reference to the desired "calculated" cell, without any indirect formulas, which should be faster for Excel to recalculate than the formulas from any of the other methods.

Improve this answer
5
  • If there's anything in the column to the right of those cells ... This works until first empty cells pair (adjacent to left and to right) - i.e. maybe not whole column will be filled.
    – Akina
    Sep 17, 2019 at 12:38
  • Otherwise, drag the square "◾" icon The same may be performed using copy-paste (drag down can be not safe when there is too many lines to fill).
    – Akina
    Sep 17, 2019 at 12:40
  • @Akina, Yes, it would stop at the first empty cell to the right. If there many cells then starting with the bottom 3 cells and dragging up would be easier. Copy+Paste is also quick, and you could use CTRL+SHIFT+Down/Up to speed up selecting the target cells.
    – Sam Hasler
    Sep 17, 2019 at 12:55
  • it would stop at the first empty cell to the right. To the right AND left. Any one of them is enough to continue copying down.
    – Akina
    Sep 17, 2019 at 12:59
  • Copy+Paste is also quick It allows select 1st cell, then freely scroll down, then Shift-click last cell - all intermediate cells wil be selected too. Then Ctrl-V.
    – Akina
    Sep 17, 2019 at 13:00
0

Put this in E2 and fill down to cover all the values in column D.

=INDEX(D:D, MATCH(1E+99, D$1:D4))
Improve this answer

You must log in to answer this question.

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