0

I am trying to recreate the output I get from this formula from a google sheet in excel.

So currently I have one sheet with a list of staff names in google sheets. Then in another sheet I am using the formula: =ARRAY_CONSTRAIN(ARRAYFORMULA('2 - Staff Listing'!D9:D300), 292, 1) to pull the list of names into a column in the new sheet.

I then used the formula: =ARRAYFORMULA(flatten(B5:B100&A1:J1)) and the output resulted in a column which repeats each name from the first column 10 times. The image below shows the output in google sheets.

Picture of Google Sheet Output

However, I have not found a way to do this in excel yet as it does not have a flatten() function. I have seen some things online about using dynamic array formulas to imitate the flatten() function but the examples weren't very helpful and I couldn't figure out how to implement in for my use case.

If anyone has any ideas for a formula in excel that can accomplish the same thing without extra helper columns for something like a Vlookup I would really appreciate it!

player0
  • 124,011
  • 12
  • 67
  • 124
Brendon
  • 53
  • 4
  • Not sure if TOCOL() is rolled out to Mac. – JvdV Oct 11 '22 at 21:15
  • Looks like it [is](https://techcommunity.microsoft.com/t5/excel-blog/what-s-new-in-excel-august-2022/ba-p/3590457). Check [here](https://stackoverflow.com/a/71113545/9758194) too. – JvdV Oct 11 '22 at 21:20
  • so I don't have excel at home and I left the office but I don't see how to make the names repeat using the tocol() function it simply flattens them. Is there an argument I could add that repeats the values one in each row? – Brendon Oct 11 '22 at 21:56
  • jk it worked the same way thanks! although since its only available in the beta chanel right now not sure if it will work for me – Brendon Oct 11 '22 at 22:48

1 Answers1

1

A way:

=IFERROR(INDEX(B2:B5,QUOTIENT(ROW(AZ2:AZ200)-2,10)+1),"")

In B2:B5 have names, you can modify ranges with necessary values.

edit with SEQUENCE instead of ROWand counting names

=INDEX(B2:B5,QUOTIENT(SEQUENCE(COUNTA(B2:B5)*10)-1,10)+1)
Terio
  • 507
  • 2
  • 5
  • Awesome! I just gave this a try and it worked! The TOCOL() function worked and its so simple but as its not available outside of beta it seems this will be perfect in the meantime. Thank you so much!!!!!!!!!!!!!!!! – Brendon Oct 11 '22 at 22:52
  • @Brendon I'm glad it's useful to you – Terio Oct 11 '22 at 22:54
  • Hey, So when I tried to do it on my mac at work I it is not repeating the names. It just lists each name once. Is there something about Excel for Mac that might be causing the formula to behave differently than on windows? – Brendon Oct 12 '22 at 12:50
  • Never-mind, I just needed to fix the cell references and it worked perfectly! Sorry still learning the ropes it would seem. Thank you again for your help! – Brendon Oct 12 '22 at 13:02