Expert Coders: Is this possible or conceivable? Coding votes into binary

D

dguenther

Board Regular
Joined
Jun 15, 2011
Messages
75
Excel 2007 on Windows 7

Hi,
I don't know if what I am about to ask is even possible. I kind of think not. But, Mr Excel people have done amazing things for me so far.

Here is the situation:

I have hundreds of files like this one:
https://my.syncplicity.com/share/bbxisyzjvs/WV_macro_applied.xlsm

The first sheet is a bunch of legal case information, spread out over hundreds of rows in column A.

The second sheet is always called "Report". It has taken the information from the first sheet and nicely put every case into its own row, separating data into it proper column. Super kudos to Rick Rothstein and Sektor for those codes.

One further transformation is necessary. I have resigned myself to thinking I will have to do this by hand, but maybe someone can surprise me. The last column of the second sheet contains the voting records of the judges on the case. Those need to be coded into 1,0, and 9.

A 1 means that the judge voted in the majority (or concurred)in the case.
A 0 means that the judge voted in the minority (dissented) in the case.
A 9 means that the judge did not vote or did not participate.

In a separate sheet, needs to be the following:

The list of docket numbers (always going to be column B from the "report" sheet) transposed to go on row A of the new "Binary" sheet.

Then magic occurs. The judges’ votes (always in column E of the Report sheet) in each case need to be put into the 0,1,9 category. There can be no other information in the "binary" sheet (per the requirements of the statistical program later used to process this stuff).

Problem is, the formatting is not beautiful. And they vary state by state. Here are three example files where I translated by hand, the judges’ votes from the “Report” sheet to the “Binary” sheet I created. (On two of them, I only did the first 5)

https://my.syncplicity.com/share/6xwkn0vp31/FL_cases_macro_applied.xlsm
https://my.syncplicity.com/share/gbe5eiem8e/VT_cases_macro_applied.xlsm
https://my.syncplicity.com/share/bbxisyzjvs/WV_macro_applied.xlsm

They can look like anything from the typical (I would venture to guess 75% of them look like this):

Adkins, C.J., and Roberts, Overton and Hatchett, JJ., concur. Boyd, J., dissents....

{So, obviously Adkins, Roberts, Overton, and Hatchett would all get a 1. Boyd would get a 0.

- to a little more complex

England, Justice. Boyd, Overton and Sundberg, JJ., concur. Adkins, C.J., dissents with an opinion, with which Chappell, Circuit Judge, concurs. ...


{Here, Boyd, Overton, and Sundberg get 1s, while Adkins and Chappell get 0s.}

- to really complicated

Adkins, Chief Justice. McCain, J., and McCrary and Lee, Circuit Court Judges, concur. Overton, J., concurring in part and dissenting in part with opinion with which Drew (Retired), J., concurs.

{Here, Adkins, McCain, McCrary and Lee get 1s. Overton and Drew get 0s. Even a partial dissent is to be coded as dissent.}

=====

So from those example files you can see what an end product needs to be. 1,0, and 9s. Deceivingly simple sounding.

I will update this post with my thoughts and experiences as I try to apply a good solution to save me weeks, possibly months! of time.

Even anything that can automate or help part of this problem would be extremely appreciated. Even just thoughts about how to go about approaching it in the most efficient way would help.


Questions, thoughts, encouragement:
Reply to this post and/or
dsguenth [at] wustl.edu
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
So, I have been doing some thinking.

I ran into a word macro a while ago that gives you word counts and frequency. It can also exclude some words in it's search.

So, I copied all of the Column E judge vote info into a blank excel worksheet and ran this macro while excluding words like [the] [a] [and] [judge] [dissent] [concur] etc.

What it popped out was essentially the last name of all of the judges. They occur the most often and as long as all the other irrelevant info does NOT pop up, then you have the judges.

Now if I could do that macro in excel or something, then I could have the judges last names put into the first column which would at least be a start. Figuring out which ones are dissenters and which ones concur is still a puzzle.

-Danny
 
Upvote 0
Not sure if I'll be able to help (actually not too likely I'll be able to help), but the problem is intriguing. In the linked data file, the structure is a bit different from that in your post, but there were still some things that might be used to guide a macro.
A few questions:
Are the concurring justices always first?
What is Per Curiam?
Is it more or less unique to West Virgina cases that the justices' names are all upper case?
In the WV cases, the info is contained in full sentences. Is that the case in other states as well?

If you post the Word macro code, I can probably convert it to Excel(no guarantees)

Hope to help,
Cindy
 
Upvote 0
Thanks Cindy,

Are the concurring justices always first?

I'm going to spend today looking through all 50 states' formats, but I want to tentatively say yes. I had thought about using the period that separates concurring from dissent to signify who should be a 0 and who should be a 1, but the problem is many times a justices first name is abbreviated and there is a period there.

Per curiam means that the ruling was kind of made by everyone and not assigned to a particular judge. To have been included in my list, though, means that there was dissent. So, from the West Virginia file

The Opinion of the Court was delivered PER CURIAM. JUSTICE WORKMAN dissents and reserves the right to file a dissenting opinion. ...

every judge would get either a 1 or a 9 (I will have to think about that and consult my colleagues) except for Workman who will get a 0.

Is it more or less unique to West Virgina cases that the justices' names are all upper case?

Great catch! I think being uppercase could be another really helpful delimiter of sorts. North Carolina also has judges' names all uppercase.
Across the board, judges' last names are capitalized or in all caps.
Thing is a couple of other words are capitalized too- I'm trying to write down a list of all words that are not judges last name like "Judge" or "Justice" "Per Curiam"

In the WV cases, the info is contained in full sentences. Is that the case in other states as well?

I guess it depends on what you mean by full sentences. In every instance I have seen so far, concurrences occur in one "sentence" with a period and dissent in another "phrase" which ends in a period or a "..."

I will PM you the Word Macro as well as this Excel macro that does a word count but does not have the exclude words function which is the critical part. Maybe a frankencode could be created from the two...

-
I am going to spend today going through the formatting quirks of all 50 states (52 courts!).

I am feeling good about possibly using the word frequency chart with non judge words excluded to find the judges and list them in the A column. Even just that will save me bundles of time.

Thanks,
Danny
 
Upvote 0
Hi Danny,

Havent access to your link, but can you adapt this udf:
Code: 
Option Explicit

Function VotingResult(ByVal Name As String, ByVal VotingRecord As String) As Variant
Dim iPtr As Integer, iNamePtr  As Integer, iVotePtr As Integer
Dim sChar As String, sVotingRecord As String, saVotingRecord() As String

Name = Trim$(LCase$(Name))

'-- convert all non-alphabetics to spaces --
For iPtr = 1 To Len(VotingRecord)
    sChar = LCase$(Mid$(VotingRecord, iPtr, 1))
    If sChar = UCase$(sChar) Then sChar = " "
    sVotingRecord = sVotingRecord & sChar
Next iPtr

VotingResult = 9
If Len(Trim$(sVotingRecord)) <> 0 Then
    saVotingRecord = Split(WorksheetFunction.Trim(sVotingRecord), " ")
    For iPtr = 0 To UBound(saVotingRecord)
         If saVotingRecord(iPtr) = Name Then
            For iNamePtr = iPtr + 1 To UBound(saVotingRecord)
                Select Case saVotingRecord(iNamePtr)
                Case "concur", "concurs", "concurring"
                    VotingResult = 0
                    Exit Function
                Case "dissent", "dissents", "dissenting"
                    VotingResult = 1
                    Exit Function
                End Select
            Next iNamePtr
         End If
    Next iPtr
End If
End Function

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Voting Records</td><td style="text-align: right;;"></td><td style=";">Adkins</td><td style=";">Boyd</td><td style=";">Chappell</td><td style=";">Drew</td><td style=";">England</td><td style=";">Hatchett</td><td style=";">Lee</td><td style=";">McCain</td><td style=";">McCrary</td><td style=";">Overton</td><td style=";">Roberts</td><td style=";">Sundberg</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Adkins, C.J., and Roberts, Overton and Hatchett, JJ., concur. Boyd, J., dissents</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">England, Justice. Boyd, Overton and Sundberg, JJ., concur. Adkins, C.J., dissents with an opinion, with which Chappell, Circuit Judge, concurs.</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Adkins, Chief Justice. McCain, J., and McCrary and Lee, Circuit Court Judges, concur. Overton, J., concurring in part and dissenting in part with opinion with which Drew (Retired), J., concurs.</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=VotingResult(<font color="Blue">C$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=VotingResult(<font color="Blue">D$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=VotingResult(<font color="Blue">E$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=VotingResult(<font color="Blue">F$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=VotingResult(<font color="Blue">G$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=VotingResult(<font color="Blue">H$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I2</th><td style="text-align:left">=VotingResult(<font color="Blue">I$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J2</th><td style="text-align:left">=VotingResult(<font color="Blue">J$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K2</th><td style="text-align:left">=VotingResult(<font color="Blue">K$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L2</th><td style="text-align:left">=VotingResult(<font color="Blue">L$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M2</th><td style="text-align:left">=VotingResult(<font color="Blue">M$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N2</th><td style="text-align:left">=VotingResult(<font color="Blue">N$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=VotingResult(<font color="Blue">C$1,$A3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=VotingResult(<font color="Blue">D$1,$A3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=VotingResult(<font color="Blue">E$1,$A3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">=VotingResult(<font color="Blue">F$1,$A3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G3</th><td style="text-align:left">=VotingResult(<font color="Blue">G$1,$A3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H3</th><td style="text-align:left">=VotingResult(<font color="Blue">H$1,$A3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I3</th><td style="text-align:left">=VotingResult(<font color="Blue">I$1,$A3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J3</th><td style="text-align:left">=VotingResult(<font color="Blue">J$1,$A3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K3</th><td style="text-align:left">=VotingResult(<font color="Blue">K$1,$A3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L3</th><td style="text-align:left">=VotingResult(<font color="Blue">L$1,$A3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M3</th><td style="text-align:left">=VotingResult(<font color="Blue">M$1,$A3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N3</th><td style="text-align:left">=VotingResult(<font color="Blue">N$1,$A3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=VotingResult(<font color="Blue">C$1,$A4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=VotingResult(<font color="Blue">D$1,$A4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">=VotingResult(<font color="Blue">E$1,$A4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F4</th><td style="text-align:left">=VotingResult(<font color="Blue">F$1,$A4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G4</th><td style="text-align:left">=VotingResult(<font color="Blue">G$1,$A4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H4</th><td style="text-align:left">=VotingResult(<font color="Blue">H$1,$A4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I4</th><td style="text-align:left">=VotingResult(<font color="Blue">I$1,$A4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J4</th><td style="text-align:left">=VotingResult(<font color="Blue">J$1,$A4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K4</th><td style="text-align:left">=VotingResult(<font color="Blue">K$1,$A4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L4</th><td style="text-align:left">=VotingResult(<font color="Blue">L$1,$A4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M4</th><td style="text-align:left">=VotingResult(<font color="Blue">M$1,$A4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N4</th><td style="text-align:left">=VotingResult(<font color="Blue">N$1,$A4</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
Slightly more correct:
Code: 
Option Explicit

Function VotingResult(ByVal Name As String, ByVal VotingRecord As String) As Variant
Dim iPtr As Integer, iNamePtr  As Integer, iVotePtr As Integer
Dim sChar As String, sVotingRecord As String, saVotingRecord() As String

Name = Trim$(LCase$(Name))

'-- convert all non-alphabetics to spaces --
For iPtr = 1 To Len(VotingRecord)
    sChar = LCase$(Mid$(VotingRecord, iPtr, 1))
    If sChar = UCase$(sChar) Then sChar = " "
    sVotingRecord = sVotingRecord & sChar
Next iPtr

sVotingRecord = WorksheetFunction.Trim(sVotingRecord)
sVotingRecord = Replace(sVotingRecord, "concurring in part", "dissent")
VotingResult = 9
If Len(Trim$(sVotingRecord)) <> 0 Then
    saVotingRecord = Split(sVotingRecord, " ")
    For iPtr = 0 To UBound(saVotingRecord)
         If saVotingRecord(iPtr) = Name Then
            For iNamePtr = iPtr + 1 To UBound(saVotingRecord)
                Select Case saVotingRecord(iNamePtr)
                Case "concur", "concurs", "concurring"
                    VotingResult = 0
                    Exit Function
                Case "dissent", "dissents", "dissenting"
                    VotingResult = 1
                    Exit Function
                End Select
            Next iNamePtr
         End If
    Next iPtr
End If
End Function

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Voting Records</td><td style="text-align: right;;"></td><td style=";">Adkins</td><td style=";">Boyd</td><td style=";">Chappell</td><td style=";">Drew</td><td style=";">England</td><td style=";">Hatchett</td><td style=";">Lee</td><td style=";">McCain</td><td style=";">McCrary</td><td style=";">Overton</td><td style=";">Roberts</td><td style=";">Sundberg</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Adkins, C.J., and Roberts, Overton and Hatchett, JJ., concur. Boyd, J., dissents</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">England, Justice. Boyd, Overton and Sundberg, JJ., concur. Adkins, C.J., dissents with an opinion, with which Chappell, Circuit Judge, concurs.</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Adkins, Chief Justice. McCain, J., and McCrary and Lee, Circuit Court Judges, concur. Overton, J., concurring in part and dissenting in part with opinion with which Drew (Retired), J., concurs.</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=VotingResult(<font color="Blue">C$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=VotingResult(<font color="Blue">D$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=VotingResult(<font color="Blue">E$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=VotingResult(<font color="Blue">F$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=VotingResult(<font color="Blue">G$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=VotingResult(<font color="Blue">H$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I2</th><td style="text-align:left">=VotingResult(<font color="Blue">I$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J2</th><td style="text-align:left">=VotingResult(<font color="Blue">J$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K2</th><td style="text-align:left">=VotingResult(<font color="Blue">K$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L2</th><td style="text-align:left">=VotingResult(<font color="Blue">L$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M2</th><td style="text-align:left">=VotingResult(<font color="Blue">M$1,$A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N2</th><td style="text-align:left">=VotingResult(<font color="Blue">N$1,$A2</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
Hi al_b_cnu,

{EDIT}

Ah, I see that this is a function not a macro. Let me try again.
--

Sorry, I am having trouble figuring out how to run your code.

Should I have a new sheet with the judge votes down the A column and the judge names across the 1st row like your Excel 2003 diagram shows?

And do I need to put the formulas in there somewhere?

-Danny
 
Last edited:
Upvote 0
Wait but it...but how could?...And the..but how is it?

(mouth gaping open) :eeek:


How on Earth did you channel the Excel gods to bring such brilliant code down from the great macro enabled Excel spreadsheet in the sky?? :confused:


You, sir, are incredible. I kind of asked the impossible, yet you made it happen.

I am going to pour over your code to see if I can decipher the magic, and then see how I can tweak it to work on even more instances where the judge votes are kind of weird.

You have made me incredibly :biggrin:

Thanks,

Danny
 
Upvote 0
Hi Danny,

It was fairly straightforward really - the function lets you do the hard work of supplying the Judges name , splits the voting record into an array looks for the name, then looks for 'Concurs' or 'dissents' after it and scores accordingly, with a twist that it replaces 'Concurring in part' to 'dissents' beforehand.

We need to define what to do in the case of Judge Drew, which it got wrong :¬/

Do you still want a macro to extract the Judge names?
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,542
Messages
6,179,421
Members
452,913
Latest member
JWD210

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top