Excel help, please!

[potatolord]

I have one sheet with a single project reference number per row.

What want is a formula that will pull each project reference number to a separate sheet on 3 consecutive rows. I can then sort their other stuff with indexmatches into a proper table.

I know I can dumb paste rows, but I want to formula it, so it updates as the other sheet does.

Like this
P1
P1
P1
P2
P2
P2
Etc

*yeah, I know, it was another one of those "I wouldn't start from here" conversations.

 

[Mercenary Keyboard Warrior]

If I get it you have a list with P1 in row 1, P2 in row 2, P3 in row 3?

If so the easiest way to do this as a one off

Cut and paste all the projects into a column, go to the bottom do it again, and a 3rd time
Then sort

Edit, ignore me, saw you wanted it to update.

 

[slinxy]

your best bet is to use something like bbedit, find and replace the character for space to \n which is for new line.
That way all the reference number will be on a new line then copy and paste that in to excel

Edit: if they haven't got a space, and the project names all start with P.. find and replace P with \nP and that will put a new line.
if your using linux, you can parse it with sed or awk
if you're using windows, then I can't help; but I think notepad++ has some tools that are the same as BBEdit

 

[shifty_uk]

If you copy and paste your entire post into chatgpt, you’ll get your answer

 

[potatolord]

If you copy and paste your entire post into chatgpt, you’ll get your answer

That did indeed work. Thanks!

 

[Diddums x]

That did indeed work. Thanks!

Excellent.

 

[GreatAuk]

Good that you've now got a solution, but fwiw my simple approach would be something like
=INDEX(range on sheet 1 with the numbers, ROUNDUP(ROW(current cell)/3),0))

 

[darkgen]

What solution did chatgpt throw out?

 

[potatolord]

Something pretty similar to greatauk's suggestion, above.

 

[[FnG]magnolia]

just LOL if you don't =SUM(A1:A2) all the time every time

e: noobs.

 

[CooK1E]

I feel personally attacked