Search code examples
excelexcel-formulaexcel-2013

How can i split a column in more columns based on a specific value in certain cells

enter image description here

I have a column with many agents that all have different results, i need to split that column in multiple columns that show all the info until next agent - as in the picture.

Thanks in advance.

I'm new to excel and couldn't find a solution myself.
Agent: Martin
10
223
33
125
2
Agent: Joe
2
23
Agent: Mark
1
23
111
444
21
32
Agent: Sasha
3
222
121
654
32
98
76
Agent: Martin Agent: Joe Agent: Mark Agent: Sasha
10 2 1 3
223 23 23 222
33 111 121
125 444 654
2 21 32
32 98
76

with the new formula in C1 it stops importing results when it hits a cell with text.

enter image description here

Solution

  • Try something along the lines, assuming there is no Excel Constraints as per tags posted, then this should work for you:

    enter image description here

    • Formula used in cell C1

    =LET(
     a,TOCOL(A:A,1),
     b,SCAN("",a,LAMBDA(x,y,IF(ISNUMBER(y),x,y))),
     c,UNIQUE(b),
     DROP(IFERROR(REDUCE("",c,LAMBDA(d,e,HSTACK(d,FILTER(a,e=b)))),""),,1))

    Or, you can use this as well, one step less:

    =LET(
     a,TOCOL(A:A,1),
     b,SCAN("",a,LAMBDA(x,y,IF(ISNUMBER(y),x,y))),
     UNIQUE(DROP(IFERROR(REDUCE("",b,LAMBDA(d,e,HSTACK(d,FILTER(a,e=b)))),""),,1),1))

    Alternative approach for Non-MS365 users:

    enter image description here

    • Formula used in cell C1

    =INDEX($A$1:$A$24,AGGREGATE(15,6,(ROW($A$1:$A$24)-ROW($A$1)+1)/(ISTEXT($A$1:$A$24)),COLUMN(A1)))

    • Formula used in cell C2

    =IFERROR(INDEX($A$1:$A$24,AGGREGATE(15,6,(ROW($A$1:$A$24)-ROW($A$1)+1)/
 (LOOKUP(ROW($A$1:$A$24),ROW($A$1:$A$24)/(IF(ISNUMBER($A$1:$A$24),"",$A$1:$A$24)<>""),$A$1:$A$24)=C$1),ROW(A2))),"")

    Note that, the formula for the header needs to be filled right while the formula for the numbers needs to fill down as well as filled right accordingly, also changes cell reference and ranges accordingly as per your data. One more caveat, one needs to hit CTRL+SHIFT+ENTER while exiting the edit mode.

    Since OP has not updated in their original post and have some anomaly with their data here is an updated solution.

    enter image description here

    • Formula used in cell C1

    =INDEX($A$1:$A$24,AGGREGATE(15,6,(ROW($A$1:$A$24)-ROW($A$1)+1)/(ISNUMBER(SEARCH("Agent",$A$1:$A$24))),COLUMN(A1)))

    • Formula used in cell C2

    =IFERROR(INDEX($A$1:$A$24,AGGREGATE(15,6,(ROW($A$1:$A$24)-ROW($A$1)+1)/
 (LOOKUP(ROW($A$1:$A$24),ROW($A$1:$A$24)/(IF(ISERROR(SEARCH("Agent",$A$1:$A$24)),"",$A$1:$A$24)<>""),$A$1:$A$24)=C$1),ROW(A2))),"")

    All prior caveats are applied based on ones excel version.