Split rows into different sheets based on a column's value in Excel
How it works:
You need to create few Helper Columns in Sheet1.
Write following formula & fill down:
D2 = =IF($A2=101,1+MAX($D$1:D1),"")
E2 = =IF($A2=102,1+MAX($E$1:E1),"")
F2 = =IF($A2=103,1+MAX($F$1:F1),"")
Write following formula in cell A2 of Sheet2 and copy this cell both across and down::
=IFERROR(INDEX(Sheet1!A:A,MATCH(ROWS($1:1),Sheet1!$D:$D,0)),"")
You get all records from Sheet 1 has ID 101.
Note,
- Use the above written formula For ID 102 & 103 in Sheet3 and Sheet4 with small modification.
- For ID 102 replace
Sheet1!$D:$D
withSheet1!$E:$E
& for ID 103 withSheet1!$F:$F
in the formula.
Adjust cell references in formula as needed.