27 Jun 2008 by Chetan Shah.

To count unique values amongst a list of values, click here.

I am copy pasting the first suggestion here only to avoid future link failures.

Let’s say you want to find out how many unique values exist in a range that contains duplicate values. For example, if a column contains:

* The values 5, 6, 7, and 6, then the result is three unique values—5 , 6 and 7.
* The values “Buchanan”, “Dodsworth”, “Dodsworth”, “Dodsworth”, then the result is two unique values—”Buchanan” and “Dodsworth”

1. Ensure that the first row in the column has a column header.

2. On the Data menu, point to Filter, and then click Advanced Filter.

3. In the Advanced Filter dialog box, click Copy to another location.

4. If the range that you are counting is not already selected, delete any information in the List range box and then click the column (or select the range) that contains your data.

5. In the Copy to box, delete any information in the box or click in the box, and then click a blank column where you want to copy the unique values.

6. Select the Unique records only check box, and click OK.
The unique values from the selected range are copied to the new column.

7. In the blank cell below the last cell in the range, enter the ROWS function. Use the range of unique values that you just copied as the argument. For example, if the range of unique values is B1:B45, then enter:
=ROWS(B1:B45)

Posted in Technology | No Comments »

21 Jun 2008 by Chetan Shah.

I cannot stress enough the importance of compression while loading html pages. One of the web applications (struts, weblogic implementation) I worked on was experiencing slowness while loading a search results page. The page used to take about 4 seconds to display (assume pure static content for the sake of this discussion) The page size was 638K! and there was no way I could reduce the amount of “visible” information.

Couple of things I observed and implemented which really helped cut 2 seconds of the page loading time. I am sure a lot is infrastructure dependent i.e : how stressed the network is etc. but there are things which are under developer’s control which we can leverage to improve page loading time.

Observation #1 :
The jsp had a for loop, which contained td style=”font-family: “Trebuchet MS”;font-size: 12px;color: #797b7a;font-weight: normal;” …. /td

I. e. : if the loop iterates for 500 times, that is the number of times “font-family: “Trebuchet MS”;font-size: 12px;color: #797b7a;font-weight: normal;” gets repeated.

Simple Solution :
Put this in css, refer that css in your jsp and change the td to . Huge reduction in generated html size. Instantly!

Point to note is : less number of characters used in the html (especially in a for loop) the better it is.

Observation #2 :
The page after it was built was not compressed. Even after the above change, the page was about 350K.

Simple Solution :
All browsers now support gzip compression. Add a filter at web.xml level and compress your html content before it hits the wire. A *huge* size savings here. The page size was reduced to 17K! while over the wire.

The effort to compress at the server level and decompress at the browser level takes much less time as compared to transmit 350K over the wire.

Many thanks to Jason Falkner for his excellent article on this. Completely agree with him to make the compression as part of a must-do for any web application

Posted in Technology | No Comments »

19 Jun 2008 by Chetan Shah.

As from my other posts on non profit, you would have noticed that I did the paperwork neccessary for non-profit (501c3) setup with IRS myself. The application was submitted in August ‘07 and was approved in April ‘08.

However, in January ‘08. I received a letter from IRS stating that they had tried to contact me at my mailing address and since I had not responded before the end date mentioned in that letter, they had closed my case. Needless to say that it was a frusterating moment for me because I had never received a letter from the IRS and I was in constant touch with them since December ‘07 and was assured that my application has been assigned and the agent is working on it.

After a couple of phone calls and snail mails my case was reopened and was finally approved in April’ 08.

So, if you get a similar letter from IRS, don’t be disheartened, you will have to just present your case calmly to the assigned agent and s/he will work with you to get it reopened and approved. The IRS agent I was working with was very helpful and guided me thru the whole process.

Posted in Foundation | No Comments »

17 Jun 2008 by Chetan Shah.

While parsing last names from SDN watchlist (or any other watchlist for that matter. BOE , OSFI etc.) , make sure that the parsing logic takes care of multiple names in last names.

For example :

Last Name : Giraldo Sarria

In this case the financial entity may have registered this person as having Sarria as their last name, in which case the parsing logic may completely ignore this SDN entry as a potential match because the last name from SDN side is Giraldo Sarria and on the financial entity side it will be Sarria.

On the same lines is the scenario of hyphenated last names like Smith-Johnson

Posted in AML | 1 Comment »

8 Jun 2008 by Chetan Shah.

Recently I bumped into an article in Money magazine (June 2008 - pg. 22) which stated that we are *not* required to show the id on being asked for one by the checkout person at grocery store (for example). Seems like a lot of the merchants don’t know about this but Visa, Mastercard and Discover actually forbid stores from asking for a id from the customer. The merchant is supposed to honor the transaction even if the customer declines to show their id.

Privacy.org has complete details from MasterCard and Visa

Posted in Personal Finance | No Comments »

3 Jun 2008 by Chetan Shah.

There are times when we need to make the data element on an excel columns to all line up in a row.

For example : Transfer the data element in B1 to A2, C1 to A3, D1 to A4, E1 to A5 etc…

Following are the steps to transpose the elements in a particular row to a given column.

1. Select all rows (single column) where you want the elements to be transposed to. So for the above example it will A2, A3, A4, A5.

2. Enter =transpose(b1:e1)

3. Hit Shift + Ctrl + Enter

4. Done!

Posted in Technology | No Comments »