1

To better explain what result I need, I made a text block below as a pseudo sheet to illustrate.

  • Col A (source) is the manually entered text data. Col B and C (desired result) will have formulas so that cells from A are populated in them as per the pattern.

  • Pattern for B is: fixed number of cells from A in its order, followed by delimiter "HELLO" then same number of following cells from A again and so on. Pattern for C is: Text "SET" plus a counter of number of delimited cell sets in B. (Please note that A will have more (or fewer) number of filled cells (added/deleted occasionally), so B & C must adapt accordingly.)

  • I've been playing around with formula =arrayformula( if ( mod( row(A:A), 6 ) = 0, "HELLO", A:A) ) for B but it skips every sixth row in A, and also I can't figure out how to do C at all :(

       (source)     (  desired result  )

        A           B           C
1       one         one         SET 1
2       two         two
3       three       three
4       four        four
5       five        five
6       six         HELLO
7       seven       six         SET 2
8       eight       seven
9       nine        eight
10      ten         nine
11      eleven      ten
12      twelve      HELLO
13      thirteen    eleven      SET 3
14      fourteen    twelve
15      fifteen     thirteen
16      sixteen     fourteen
17      seventeen   fifteen
18      eighteen    HELLO
19      nineteen    sixteen     SET 4
20      twenty      seventeen
21      twentyone   eighteen
22      twentytwo   nineteen
23                  twenty
24                  HELLO
25                  twentyone   SET 5
26                  twentytwo
27
28
29
...     ...         ...         ...
Improve this question

2 Answers 2

Reset to default
0

Here's my solution in a worksheet without using Arrayformula as much as possible. I haven't found a way to avoid having to drag down column B, but that's the only thing that's not perfect. You can delete cells in column A and all the formulae stay intact, as requested:

The formula for Column B is (copy this cell into B1 and drag down)

=IF(ISERROR(MATCH("SET",C8)),"HELLO",INDIRECT("A"&(ROW(D1)-FLOOR(ROW(D1)/6))))

The formula for Column C is (copy this cell into C1 and drag down)

=ARRAYFORMULA(IF(mod(Row(A1:A),6)=1,"SET "&(FLOOR(Row(A1:A)/6)+1),""))

In order to make the formulae work if rows are deleted/added to column A, we need this auxiliary (hideable) column D (or wherever you want to put it:

=ARRAYFORMULA("A"&(Row(A1:A)-FLOOR(ROW(A1:A)/6)))

Improve this answer
5
  • Thank you very much, it's great! However, as noted in op, the number of filled rows in A will be different (fewer or more). So is it possible to to do it with =arrayformula() or such, so that one does not have to manually copy or remove formulas in B or C? Thank you so much again!
    – spcsLrg
    Sep 23, 2019 at 16:45
  • You're right, the original solution I gave didn't work if you deleted/added single cells in column A. I've adapted the solution to make it work in that case adding an auxiliary column D (which you can place anywhere or hide). I've also managed to let Arrayformula do part of the job in columns C and D - but it may be impossible to use it for column B as such. Let me know if this solves your problem!
    – Cornelius Roemer
    Sep 25, 2019 at 9:53
  • Thank you! Yes, the solution still does require manual copy pasting and removing unneeded rows from below, but I'll use it for now. Though I'd prefer a solution needing just to edit A and rest of it populating by itself. Will wait for a while if there's a solution achieving just that, before accepting it as answer. Thanks again!
    – spcsLrg
    Sep 25, 2019 at 10:28
  • Can you explain what you need by manual copy pasting and removing unneeded rows from below? All you have to do it is set columns B-D up once (it's really easy to "drag down" column B) - no need to do anything afterwards if you manipulate column A. So I'm not sure in which way the answer is unsatisfactory to you, yes it's not beautiful, but it does the job, no?
    – Cornelius Roemer
    Sep 25, 2019 at 10:38
  • 1
    Yeah, sorry, pls disregard that. (I meant there are 7 repeating "HELLO" cells in B where the sheet truncates at the bottom, and if I were to copy the range to paste elsewhere I had to remove them). But nevermind that, your solution works fine. Thanks again.
    – spcsLrg
    Sep 25, 2019 at 14:50
0

You can use the following two formulas (no extra helper columns needed):

For column B:

Place this formula in cell B1 

=QUERY(({A1:A5;"HELLO";A6:A10;"HELLO";A11:A15;"HELLO";A16:A20;"HELLO";A21:A25}),"SELECT *")

An easy way to write/extend the formula is to copy the cluster ";HELLO";A21:A25 and repeat it for as long as you need it.

For column C:

Place this formula in cell C1 

=ArrayFormula(if(ArrayFormula(mod(row(A2:A30),6))=2,"SET "& (INT(Row(A2:A)/6)+1),""))

EDIT

Here is a working sheet

Improve this answer
2
  • Thanks, but this gives me "#REF!" in B1 and repeating "SET" cells (without counter) in C? Besides, I'd prefer just to edit A subsequently for data afterwards, and nothing else, so accepted answer is working fine for me. Thanks again.
    – spcsLrg
    Sep 25, 2019 at 15:00
  • Sorry about the "#REF!". I had different columns in my test sheet. The counter works as well. Have a look at the sheet (everything working from column A, no copy down). "...accepted answer is working fine for me". Sure. No worries. :)
    – marikamitsos
    Sep 25, 2019 at 17:11

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.