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.
Try something along the lines, assuming there is no Excel Constraints
as per tags posted, then this should work for you:
• 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:
• 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.
• 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.