microsoft office - Split rows into different sheets based on a column's value in Excel

+-----+----------+----------+
|  a  |    b     |    c     |
+-----+----------+----------+
| 101 | 12:13:00 | employee |
| 102 | 12:15:00 | customer |
| 103 | 12:20:00 | employee |
| 102 | 12:16:00 | customer |
| 103 | 18:15:00 | employee |
| 101 | 18:18:00 | customer |
+-----+----------+----------+

how to separate rows to different sheets according to a column values automatically

finally get three sheets:

column a values 101

+-----+----------+----------+
|  a  |    b     |    c     |
+-----+----------+----------+
| 101 | 12:13:00 | employee |
| 101 | 18:18:00 | customer |
+-----+----------+----------+

column a values 102

+-----+----------+----------+
|  a  |    b     |    c     |
+-----+----------+----------+
| 102 | 12:15:00 | customer |
| 102 | 12:16:00 | customer |
+-----+----------+----------+

column a values 103

+-----+----------+----------+
|  a  |    b     |    c     |
+-----+----------+----------+
| 103 | 12:20:00 | employee |
| 103 | 18:15:00 | employee |
+-----+----------+----------+

Answer

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 with Sheet1!$E:$E & for ID 103 with Sheet1!$F:$Fin the formula.

Adjust cell references in formula as needed.