Search code examples
excelconcatenationoffset

Offset every 5th row in excel after concatenate

I am trying to concatenate two cells within the same column but then update the first concatenate part with the 5th cell down. Here is an example of what I am trying to achieve:

[Column A]  
Developer  
senior  
mid  
junior  
apprentice  
Analyst  
senior  
mid  
junior  
apprentice  
Business  
senior  
mid  
junior  
apprentice

What I would like to achieve is this:

[Column B]  
Developer-senior  
Developer-mid  
Developer-junior  
Developer-apprentice  
Analyst-senior  
Analyst-mid  
Analyst-junior  
Analyst-apprentice  
Business...

I've tried to concatenate and use offset but no luck. Here is my code: =OFFSET(A$1,(ROW()-1)*5,0) which will grab Developer, Analyst, and Business. But I am not sure where to go from here. Thanks for the help.

Solution

  • You can try INDEX function:

    =INDEX($A$1:$A$15,INT((ROW()-1)/5)*5+1) & "-" & IFERROR(INDEX($A$1:$A$15,IF((ROW()-1)/5-INT((ROW()-1)/5)<>0,ROW(),-1)),"")

    enter image description here