Repeating 2 times in two different cell

[Etax]

Hi,

I have a list of words in D4:D15.I want to repeat each two times in two different cell.

Suppose,D4 has apple,then say in anywhere as per user say Q1 and R1 it should be apple then it will repeat another text which is in D5 and so on.

[AliGW]

Please attach a sample workbook.

Administrative Note:

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

[Etax]

Pls find attachment.

[wk9128]

worksheet or tab name : Scenario-2
Cell E2 formula , Drag right

Please Login or Register  to view this content.

worksheet or tab name : Scenario-1
Cell G1 formula , Drag right

Please Login or Register  to view this content.

[Gregb11]

For Scenario 1, Enter this 1 spill formula
=TRANSPOSE(TOCOL(IFNA(EXPAND(D4:D7,,2),D4:D7)))

For Scenario 2, try this:
=TEXTSPLIT(REPT(TEXTJOIN("|",TRUE,B3:C3)&"|",3),"|")

or:
=LET(a,TEXTSPLIT(REPT(TEXTJOIN("|",TRUE,B3:C3)&"|",3),"|"),FILTER(a,a<>""))

[Etax]

Both of them worked very fine. Thanx both of u.

Since Greb was of office 365,tested it was good. However, I had office 2021 operating hence wk9128 if it could spill would have been also much better.

[Glenn Kennedy]

Etax... are you using O365? If so, please amend your profile. If not...HOW did Greg's work for you??

[wk9128]

@Etax You're Welcome. Glad to help . Thank You for the feedback.

worksheet or tab name : Scenario-2
Cell E2 formula

Please Login or Register  to view this content.

worksheet or tab name : Scenario-1
Cell G1 formula

Please Login or Register  to view this content.

- you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help
was given. By doing so you can add to the reputation(s) of those who helped.

[Etax]

Etax... are you using O365? If so, please amend your profile. If not...HOW did Greg's work for you??

I did try in other person system with O365

[Etax]

@Etax You're Welcome. Glad to help . Thank You for the feedback.

worksheet or tab name : Scenario-2
Cell E2 formula

Please Login or Register  to view this content.

worksheet or tab name : Scenario-1
Cell G1 formula

Please Login or Register  to view this content.

- you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help
was given. By doing so you can add to the reputation(s) of those who helped.

This also works very fine but at last there has found spilling 1 blank,3 times repeat means 3 times should have been restricted accordingly.As there r my some data in K column i.e 7th column in my actual file.There is spill error but when I remove data from K Column then there is no spill error.
So,similar for both scenario.

So #Post 4 works fine in my version 2021. Would love above aswell but due to spill error at last couldn't use it.