How to skip row for user input using formula?

user_9205480 0
Viewed 95

Given the following table:

data table

I was able to find a formula that adds a blank row after each row for the 'Track Name' column, which allowed me to populate the new table (see expected output) correctly with ArrayFormula into merged cells.

={"Track Name";ARRAYFORMULA(IFERROR(VLOOKUP(ARRAY_CONSTRAIN(ROW(Data!A2:A),COUNTA(Data!A2:A)*2-1,1), {IF(LEN(Data!A2:A),ROW(Data!A1:A)*2,),Data!A2:A},{2},FALSE)," "))}

Now, is it possible to then use ArrayFormula to retrieve the artists & features while skipping rows to allow for user input?

Expected output:

expected output

I could probably set this up using a script, but if I can avoid that then I think that'd be better...

array-formulas google-sheets
1 Answers

Try:

=ArrayFormula(iferror(vlookup(array_constrain(row(A2:A),COUNTA(A2:A)*2-1,1),{if(len(A2:A),row(A1:A)*2,),ArrayFormula(split(ArrayFormula(B2:B & "," & C2:C),","))},{2,3,4,5},false)," "))

Result: enter image description here

For the merged cells for the Track Name column I'm assuming you've formatted this manually.

To explain this I've used this formula from the reference link:

=ArrayFormula(iferror(vlookup(array_constrain(row(A2:A),COUNTA(A2:A)*2-1,1),
{if(len(A2:A),row(A1:A)*2,),B2:C)},{2,3},false)," "))

I replaced the range B2:C with the formula from this post ArrayFormula with TextJoin of a row of columns to join columns B and C and split them to multiple columns. I also changed the {2,3} to {2,3,4,5} (you need to change this if you are expecting more columns).

NOTE: This does not work for merged cells it is only displaying the value on the left cell if you have 2 horizontally merged cells.

Please see the reference link below if you need more information on the formula used.

Reference:

user_184002720
Related