Macro to Split Up Worksheets

Here's a simple little, easy to run macro in Excel which will split up individual worksheets into separate Excel files named with the original workbook name and the worksheet name. This can be an important tool when you're processing lots of Excel files and need to run other macros on them that will on function on individual worksheets.

Sub SplitSheets() Dim W As Worksheet For Each W In Worksheets W.SaveAs ActiveWorkbook.Path & "/" & Trim(ActiveWorkbook.Name) & "_" & W.Name & ".csv", FileFormat:=xlCSV Next W End Sub

This macro is available on the site of Mr. Excel, at http://www.mrexcel.com/forum/excel-questions/73111-split-workbook-into-new-file-each-worksheet.html

You should not have to edit this macro in any way.

We begin with a spreadsheet containing three separate worksheets, "Teams"; "HallofFame"; and "Sheet2".

Press ALT + F11 to go into Visual Basic and right click on the workbook name and insert a new module. Paste in the SplitSheets macro

Go to the View ribbon, Macros . . . View Marcos and select & run the SplitSheets macro.

As you can see in the folder in which the original spreadsheet was saved, new .csv files will be generated with the original workbook name, and then the worksheet name. However a flaw in the macro adds in the names of any preceding worksheets in the workbook. If necessary this can be remedied with a REN batch file, or the method employing Bulk Rename Utility profiled on Amy Bowser Rollins' Litigation Support Guru site.

Determine the Number of Unique Values with One Instance of Value from Adjacent Column

THIS IS THE ONE YEAR ANNIVERSARY OF LITIGATION SUPPORT TIP OF THE NIGHT. TIPS HAVE BEEN POSTED TO THIS BLOG EACH NIGHT SINCE APRIL 10, 2015, ONLY WITH THE EXCEPTION OF TWO NIGHTS WHEN I WAS KEPT ALL NIGHT AT WORK.

This night's tip is an extension of the Excel formula descripted in the Tip of the Night for April 8, 2016 to get the number of unique values listed in a column. Tonight's formula shows how many unique values in one column have at least one occurrence of a specified value in another column. Say you have a spreadsheet which lists file numbers in one column and document types in another column. You might want to be sure that each file has at least one contract in the file. In the example below, we enter this formula in order to determine how many players with the Baltimore Orioles went at least on season without committing an error.

=SUM(IF(FREQUENCY(IF(J2:J1877=0,IF(B2:B1877<>"",MATCH("~"&B2:B1877,B2:B1877&"",0))),ROW(B2:B1877)-ROW(B2)+1),1))

This is an array formula, so after typing it in, press CTRL + SHIFT + ENTER.

See a discussion of this formula on this site: http://www.mrexcel.com/forum/excel-questions/421198-count-unique-text-values-based-condition-another-column.html

Excel Formula Count the Number of Unique Values

Anyone who has worked a number of Excel spreadsheets produced in electronic discovery has dealt with worksheets with repetitive entries in hundreds or thousands of cells. If you want to know the number of unique values in a column you could simply use the remove duplicates tool on the Data ribbon, but if you don't want to disrupt the data on the worksheet or bother copying it to another draft spreadsheet there's another option. Enter this formula referring to the complete range of data in the column you want to get a count of unique values in.

=SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1

Be sure to enter the specific numbers of the beginning and ending rows rather than just referencing the whole column as 'A:A'.

In the example below we want to know how many seasons are referenced in column D of this spreadsheet showing fielding data for major league baseball players. So we enter the formula this way:

=SUM(IF(FREQUENCY(D1:D59885,D1:D59885)>0,1))

. . . and as you can see the result is that 124 seasons of baseball are recorded on the spreadsheet.

Microsoft provides an explanation of the formula on this web page:

https://support.office.com/en-us/article/Count-unique-values-among-duplicates-7889942d-824e-4469-893c-191d1efde950