Comparing long text file in one excel sheet to another (for a media study)

[alliage]

Greetings from the Philippines!

I am working for a news broadcast network in the Philippines. Currently, we are working on a story on the performance of our lawmakers by looking at the quality of bills they file in Congress.

Our political system allows lawmakers to be reelected on three terms. According to experts, this political set up has allowed this phenomenon: the bills they filed from previous Congress are rehashed in the present Congress. However, empirical data are scarce, or worst non-existent. Experts base their opinion on anecdotal evidence.

It is in this light that we would like to study this phenomenon. We would like to shed light on the magnitude of this issue by being the first network or agency that would embark on an empirical study.

We have retrieved from our Congress’ database the list of bills our lawmakers filed in the 13th and 14th Congress. The bills are formatted in Excel format.

Historically, more than half (of the 200 plus) lawmakers are reelected every Congress, and roughly, they principally author more than 30 bills every Congress. This presents a major obstacle in our study.

In this regard, we would like to seek assistance from you.

We would like to know whether the bills they filed from previous 13th Congress were re-filed in the 14th Congress. We hope that you could help us in knowing what Excel command or function could make our study easier and faster.

Most of the bill titles are very long such as AN ACT STRENGTHENING THE FAMILY AND PRESERVING THE SOLEMNITY OF MARRIAGE CEREMONY REPEALING AND AMENDING CERTAIN PROVISIONS OF EXECUTIVE ORDER NO. 209 OTHERWISE KNOWN AS THE FAMILY CODE OF THE PHILIPPINES AS AMENDED.

That’s why it is hard for us to really do simple Excel IF function or manually look it up from one Excel sheet to another Excel sheet. In the past three decades, we have already 8 Congresses.

And since most of their bills are mere rehashed, sometimes lawmakers do some tweaking by changing the title to make it current. For instance, a lawmaker puts a title “Reproductive Health of 2005.” The bill was archived from the previous Congress. Because he’s reelected in the present Congress, he again refiled the bill and renamed the title “Reproductive Health of 2008.”

We hope that the command is not too strict to account for such variance.

Please do help us as we would like to shed light on this issue. Here is my email: alliage.morales[at]gmail.com

Yours respectfully,

John Alliage Morales

[ben_hensel]

Hmm... as described, this is a worthy endeavor, but it's a pretty tall order for a help forum....

Well, 200 lawmakers * at least 30 bills /each = 6,000+ bills....

And they've all got long names that changes from year-to-year?

Are they different worksheets in the same workbook or different workbooks???

I bet these excel files are pretty huge... hmn, I dunno what the text functions would do for you; you might be able to drop Sort&Filter on them to look at each lawmaker and see if you can identify suspicious folks, but that would be tedious... maybe a pivot table???

Maybe you could Write a grant proposal to get financial support to hire a computer programmer (specialist in text mining probably) to code an application?