How do i split data into multiple excel files based on columns in excel?
At times, in data analysis, you may need to split the contents of an Excel worksheet into multiple Excel workbooks according to a specific column. Now, in this post, we will teach you 2 quick ways to get it. Many users frequently need to split an Excel worksheet that contains huge rows of data into multiple separate Excel workbooks based on a specific column. For instance, here is my sample Excel worksheet. I would like
to split this sheet’s data on basis of the “Price of single license (US$)” column into multiple workbooks. In general, you’ll tend to use the following Method 1 to manually filter and copy data. But, it will be quite tedious and stupid if there are too many filter options. Therefore, here we also show a much more convenience way – Method 2, which uses VBA. Now, read on to get them in detail. Method 1: Copy Contents to Separate Excel Workbooks after Filter
Method 2: Batch Split Contents into Multiple Excel Workbooks via VBA
Sub SplitSheetDataIntoMultipleWorkbooksBasedOnSpecificColumn() Dim objWorksheet As Excel.Worksheet Dim nLastRow, nRow, nNextRow As Integer Dim strColumnValue As String Dim objDictionary As Object Dim varColumnValues As Variant Dim varColumnValue As Variant Dim objExcelWorkbook As Excel.Workbook Dim objSheet As Excel.Worksheet Set objWorksheet = ActiveSheet nLastRow = objWorksheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row Set objDictionary = CreateObject("Scripting.Dictionary") For nRow = 2 To nLastRow 'Get the specific Column 'Here my instance is "B" column 'You can change it to your case strColumnValue = objWorksheet.Range("B" & nRow).Value If objDictionary.Exists(strColumnValue) = False Then objDictionary.Add strColumnValue, 1 End If Next varColumnValues = objDictionary.Keys For i = LBound(varColumnValues) To UBound(varColumnValues) varColumnValue = varColumnValues(i) 'Create a new Excel workbook Set objExcelWorkbook = Excel.Application.Workbooks.Add Set objSheet = objExcelWorkbook.Sheets(1) objSheet.Name = objWorksheet.Name objWorksheet.Rows(1).EntireRow.Copy objSheet.Activate objSheet.Range("A1").Select objSheet.Paste For nRow = 2 To nLastRow If CStr(objWorksheet.Range("B" & nRow).Value) = CStr(varColumnValue) Then 'Copy data with the same column "B" value to new workbook objWorksheet.Rows(nRow).EntireRow.Copy nNextRow = objSheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row + 1 objSheet.Range("A" & nNextRow).Select objSheet.Paste objSheet.Columns("A:B").AutoFit End If Next Next End Sub
Comparison
Prevent Excel Data LossThough MS Excel is becoming more and more advanced and sophisticated, it still tends to crash from time to time due to miscellaneous factors, such as malicious third party add-ins or human errors and so on. Since Excel crash can directly lead to Excel corruption, so as to avoid Excel data loss, you have to back up your Excel files on a regular basis. Otherwise, you need to apply an Excel repair tool, such as DataNumen Excel Repair to fix corrupted Excel files. Author Introduction:Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including SQL Server repair and outlook repair software products. For more information visit www.datanumen.com How do you create multiple Excel workbooks based on filtering of a certain column?Create multiple excel file based on column values. Read the excel and store it in dt1.. use assign activity,get the rows count,count=dti.Rows.count.toString.. use Read Range and in range give as" A1:A"+count,store it in dt2.. use remove duplicate Rows activity and remove duplicate.. use for each row.. How do I split data in Excel with criteria?Try it!. Select the cell or column that contains the text you want to split.. Select Data > Text to Columns.. In the Convert Text to Columns Wizard, select Delimited > Next.. Select the Delimiters for your data. ... . Select Next.. Select the Destination in your worksheet which is where you want the split data to appear.. How do you split an Excel spreadsheet into multiple files based on columns in VBA?Step by Step guide on how to split the excel sheet:
Step 1: Press Alt + F11 to open VBA editor. Step 2: Insert a Module from Insert module. Step 3: Copy the below code and paste in the code window. Step 4: Press F5 to execute the below VBA code.
|