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

Importing XML Files to Excel

You may have noticed the PACER sites for the Circuit Courts give you the option of downloading data from the documents in XML files. These files can contain as many as 60 different fields. You can import all of these into a single Excel spreadsheet by following these steps.

1. Go to the Developer tab, and select Source.

2. Click where it says, 'XML Maps' towards the bottom of the XML Source window which opens.

3. In the new dialog box that opens, click Add . . . and then select one of the many XML files you may have downloaded.

4. You should see a window indicating that the XML source does not have a schema. Let Excel create one based on the XML file and click OK.

5. Select cell A1 on your worksheet, and then the In the XML Source pane, right click on 'caseSummary' so all of the fields are highlighted and then choose Map Element

6. The fields from the XML file will appear in the first row. Go back to the Developer tab and click on 'Import' and select all of the XML files you downloaded from the PACER site for a Circuit Court.

7. Presto! Your worksheet will load with thousands of data points for these fields:

caseNumber

dateFiled

natureOfSuit

dateTerminated

shortTitle

origCourt

type

subType

subSubType

district

division

caseNumber2

leadCaseNumber

caseNumberLink

leadCaseNumberLink

dateFiled3

dateJudgment

dateJudgmentEOD

dateNOAFiled

dateDecided

dateRecdCoa

dateSentence

leadCaseNumber4

memberCaseNumber

associatedType

dateStart

dateEnd

panelType

enbanc

panelists

dateHearing

dateComplete

dateDecision

info

prisonerNumber

type5

firstName

middleName

lastName

generation

suffix

title

email

fax

address1

address2

address3

office

unit

room

businessPhone

personalPhone

city

state

zip

terminationDate

noticeInfo

caption

dateFiled6

text

docLink