2

enter image description here enter image description here

How do I copy purple numbers from the right side when using a copy-drag? What should be the formula for it to work?

player0
  • 124,011
  • 12
  • 67
  • 124
Edvard
  • 163
  • 8
  • add more details what you are trying to acomplish – player0 Feb 04 '22 at 19:30
  • Whenever possible, try to include a [minimal example](https://stackoverflow.com/help/minimal-reproducible-example) that reproduces the issue. You can also include the expected behavior, the actual behavior, and how they differ, that would be helpful as well. Please visit [How to ask](https://stackoverflow.com/help/how-to-ask) to have some tips on how to write a question, so the community will be able to help you out in a better way – Gabriel Carballo Feb 04 '22 at 20:17
  • In the first screenshot B2=K1, B4=L1 B6=M1 B8=N1 B10=O1. When I drag the bordered block I want to get the purple in cells B15=K2, B17=L2 B19=M3 B21=N4 B22=O5. However, when I drag the formula I get data from line 14 and not line 2. – Edvard Feb 04 '22 at 20:59
  • Hopefully, it's clear enough. The idea is to have a separate block that will copy the data from the first line from the right side, then when I copy the block a second block will copy the data from the second line from the right side, and so on. But I cannot do it manually as the lists on the right side are huge and this is just an example. – Edvard Feb 04 '22 at 21:03

1 Answers1

3

B2 will be:

=K1

and for every next cell use:

=INDEX(QUERY(FLATTEN(INDIRECT("K1:O")), "Limit 1 offset "&
 SUM(COUNTIF(INDIRECT("B1:B"&ROW()-1), FLATTEN(INDIRECT("K1:O")))), ))

can be dragged but since you need it in every 2nd row you can just copy-paste it wherever you need so

player0
  • 124,011
  • 12
  • 67
  • 124