putting data for panel in an efficient way

[Emilia_Maria]

Dear Community,

I want to create a panel for 5 firms, let's call them A-E, for very large time span. In reality I have daily data from 2006 till now. The first column contains date and the second column should contain a company name. I want to have in the second column a company name A from let's say 01.01.2000 till 26.07.2000 and than again from 01.01.2000 till 26.07.2000 name B for company name. And than for C till E. In reality it is very time consuming and error yielding to use Ctrl+Space to fill in the names. The time span is for more than 4000 rows, so it is no way to me to copy and paste. I would be very grateful for any hints how to do it efficiently

I attached the sample of my data

[Glenn Kennedy]

Use:

="01/01/2000"+MOD(SEQUENCE(1040)-1,208)

and

=INDEX(I1:I5,INT(SEQUENCE(1040,,1,1/208)))

where I1:I5 contain company names. Column H explains where 1040 and 208 came from and can be deleted. In D, you may need ; instead of ,

[Emilia_Maria]

Thank you Glenn. On Office 365 it works. However at my job I use Excel 2016. I didn't want to abuse your kindness and tried to find the solution on my own. However, I wasn't successufl

[Glenn Kennedy]

D2:
="01/01/2000"+MOD((ROWS(D$2:D2)-1),208)

E2:
=INDEX($I$1:$I$5,1+INT((ROWS(E$2:E2)-1)/208))

both copied down.



It's always a good idea to state the Excel version you want it to work for IF that version is NOT what it says in your profile.