yogi_Repeat Sheet Names By Number Of Items In Respective Sheet Names In One Combined List

Google Spreadsheet   Post  #2248

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-25-2017

question by: Thomas Wu

https://productforums.google.com/forum/#!topic/docs/uL6otiR9PNs;context-place=forum/docs
Repeating value X times depending on number of entries in a range
I currently use a Filter formula to grab all the relevant entries on multiple tabs:

={filter(Fruits!A:A,Fruits!A:A<>"",row(Fruits!A:A)>1);filter(Vegetables!A:A,Vegetables!A:A<>"",row(Vegetables!A:A)>1)}

That formula above will output 1 list of items from the "Fruits" tab and the "Vegetables" tab.

It's particularly helpful because I don't know how long the list from both tabs will be as they will grow/shrink daily.

What I want to do next is to add a column that describes which worksheet each entry comes from.

I know I can do this via a formula that will check each worksheet to find the name:

If(match(key,Fruits),"Fruits","Vegetables")

But I imagine there is another way to count the number of filter entries in the first formula and repeat "Fruits" for as many entries in the first part of the formula and then vegetables for the following entries. Any ideas of how to do this? Here's a spreadsheet with dummy data:

https://docs.google.com/spreadsheets/d/1d9asz3Wu9y0Eby-9w0DQkr3M-E9DRcvsVOnJWRaT_4M/edit#gid=0

Cell A3 is where I'd like to have the formula entered. Thanks in advance if anyone has any ideas!

-Thomas