CODEWITHSUNDEEP
excelrepeat
AER
AER

Reputation: 1531

Repeating a block of text element by element in Excel

I was wondering if you could copy in Excel which allows a block of text (each new text element in a different cell) to repeat element by element.

Currently one can repeat a block of text by clicking and dragging such that ABC can become ABCABCABCABC which is repeated 4 times. However element by element 4 times would yield AAAABBBBCCCC. I'm doing a rather large block of text so manually is not an option.

This is because I wish to have two columns, one with the ABCABCABCABC and another with the AAAABBBBCCCC in a 'to' and 'from' situation such that all possible journeys between points are recorded.

Could you even use a cell-reference with a floor/ceiling rounding?

Thank you.

Upvotes: 0

Views: 486

Answers (1)

teylyn
teylyn

Reputation: 35955

A somewhat pedestrian approach with formulas.

Put the order of your series into column E, starting in E1.

Copy the cells in column E to column A, starting in cell A2. Drag down as far as desired, repeating the pattern set in column E.

Place the first value of your pattern in cell B1.

In cell B2 put this formula and copy down as far as required

=IF(COUNTIF($B$1:B1,A2)<COUNTA(E:E),B1,INDEX(E:E,ROUNDUP((ROW()-1)/COUNTA(E:E),0)))

enter image description here

Copy column B. Paste Special as Values.

Upvotes: 1

Related Questions