I have a table of 10000 rows X 50 columns. The table is all in one sheet and I am planning to split that sheet into multiple sheers based on values in two columns (columns number 5 and 1, respectively for me to be specific).

EDIT: To clarify, what I am trying to do here is to split the data based on column no.5 then split the resulting sheets into further sheets based on column 1.

I have found some VBA code that was able to split data into multiple sheets based on the values in the first column here.

My question here is: Is it possible to modify that script in order to split the data into worksheets based on column number 5, then apply another level to splitting based on column 1 in 1 go? or do I need to apply the scripts of these two steps 1 by 1?

Here's some play data for your testing purposes in CSV format.

Dummy 1,Dummy 2,Dummy 3,Dummy 4,Dummy 5,Dummy 6,Dummy 7,Dummy 8
Class 1,0.6,0.4,0.4,A,0.1,0.6,0.2
Class 2,0.4,0.1,0.2,A,0.8,0.3,0.5
Class 3,0.2,0.9,0.9,B,0.8,0.3,0.9
Class 1,0.4,0.6,0.5,B,0.8,0.9,0.8
Class 3,0.5,0.3,0.3,A,0.5,0.1,0.4
Class 2,0.4,0.2,0.0,A,0.7,0.3,0.2
Class 1,0.4,0.2,0.4,A,0.4,0.9,0.9
Class 4,0.1,0.3,0.8,A,0.5,0.3,0.5
Class 1,0.5,0.3,0.1,B,0.3,0.2,0.2
Class 2,0.4,0.5,0.6,B,0.1,0.3,0.2
Class 3,0.4,0.8,0.3,B,0.4,0.2,0.5
Class 1,0.5,0.1,0.9,B,0.5,0.6,0.6
Class 3,0.7,0.1,0.9,C,0.0,0.9,0.8
Class 2,0.1,0.2,0.5,C,0.0,0.1,0.7
Class 1,0.4,0.5,0.4,C,0.0,0.4,0.2
Class 4,0.7,0.4,0.1,C,0.9,0.1,0.9
Class 1,0.5,0.5,0.0,B,0.5,0.2,0.1
Class 2,0.5,0.8,0.6,B,0.6,0.6,0.9
Class 3,0.7,0.8,0.6,B,0.4,0.2,0.0
Class 1,0.6,0.2,0.6,A,0.6,1.0,1.0
Class 3,0.6,1.0,0.1,A,0.7,0.9,0.5
Class 2,0.4,0.7,0.7,C,0.5,0.7,0.2
Class 1,0.2,0.8,0.3,C,0.1,0.6,0.4
Class 4,0.5,0.1,0.0,D,0.9,0.1,0.5
Class 1,0.4,0.0,1.0,D,0.7,0.0,1.0
Class 2,0.8,0.5,0.4,B,0.0,0.2,0.6
Class 3,0.4,0.5,0.7,A,0.1,0.0,0.0
Class 1,1.0,0.3,0.6,C,0.1,0.4,0.8
Class 3,0.1,1.0,0.0,D,0.5,0.9,0.1
Class 2,0.7,0.3,0.7,B,0.1,0.9,0.4

I apologize for this question but I am new to excel VBA and still in the learning phase. Appreciated.

  • Clarification - you want to end up with sheets A, B, C, that each contain the records where Dummy5 = A, B, C; then more sheets, A1,A2,A3, that contain all the records where Dummy1 = Class 1, Class 2, Class 3? That is, you need to keep both data sets? Or would it be enough to get sheets A1, A2, A3, B1, B2, B3? – Werrf Jul 13 '17 at 15:36
  • Hi @Werrf. I am actually trying to get the first option you mentioned, that is the maximum number of sheets possible. Sheet A, A-class1, A-class2, A-class 3...etc. One of my main concerns is the ability of my PC to actually accommodate this in terms of RAM, that's why I want to run it in sequence too. – Learner123 Jul 26 '17 at 12:20

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.