CODEWITHSUNDEEP
excelexcel-formula
Jorz
Jorz

Reputation: 358

Excel (Non-VBA): Limit formula on a certain row and continue on the next column

I have this sample data:

enter image description here

Assuming I have the formula to combine values of A & B in a new cell (starting from cell E1), how can I limit the formula on the 5th row and continue on the next column (and so on) like on the sample above?

Upvotes: 0

Views: 62

Answers (2)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

Following formula can be implemented in Cell E1 and copied down and across

=IFERROR(INDEX($A:$A,(ROWS($E$1:E1)/(ROW()<=5))+((COLUMNS($E$1:E1)-1)*5))&INDEX($B:$B,(ROWS($E$1:E1)/(ROW()<=5))+((COLUMNS($E$1:E1)-1)*5)),"")

In principle, it is the same formula concatenated for two columns.

INDEX($A:$A,(ROWS($E$1:E1)/(ROW()<=5))+((COLUMNS($E$1:E1)-1)*5))

which basically does the following:

  • Return results from each set of row 1 to 5

  • Returns error if formula row exceeds 5 (ROW()<=5) portion

  • Keeps increasing the base counter as formula is copied rightwards (COLUMNS($E$1:E1)-1)*5

Upvotes: 2

JvdV
JvdV

Reputation: 75870

Try in E1

=INDEX($A:$A,ROW()+((COLUMN(A1)-1)*5))&INDEX($B:$B,ROW()+((COLUMN(A1)-1)*5))

Upvotes: 0

Related Questions