Excel here

Monday, October 30, 2006

Document Management Using Excel

Consulting assingments typically come with myriad view points and tonnes of information. It becomes critical to sift through the entire information to get a clear overview of the problem at hand. Sadly, nobody has the casestudy written. Everyone has their own share of information /data/documents.

Recently we had a client sharing with us an elaborate list of documents as part of one consulting engagements. There were more than a hundred documents (excels, ppts, docs and loads of zipfiles as well) and we had to go through the same along with meetings and conf. calls. During this I have realized that excel can help greatly in processing these documents in an organized way and help in gathering views from multiple parties.

So this post explains how it can be done. [Download the excel Document Management Using Excel]


  1. First I got a list of all the document names in the target directory in a textfile. To get it; go to CMD window (start->run->cmd) and then go to the target directory (cd \[target directory path]) and then finally type (DIR /B/S > filenames.txt). You will have all the file names piped in to that folder.
  2. Then I copied the contents of that text file to an excel sheet.
  3. Then I created an user defined formula for reversing the contents of a cell. (read How to reverse cell contents)
  4. Then, I have added Doctype, Location, Comments? and Reversed columns to the sheet next to filenames column.
  5. In "Reversed" column (Column G), I have typed the following formula,

  6. Now I need to know the type of document each line is having. This can be dervied the from the extension of the file (or lack of it would mean a dirctory). Extension of the file is the substring after the last "." in the filename. How do we know where is the last dot? Well, we dont, but we do know where the first dot is in the reversed string [or anystring, just write, find(".","somestrng.stritir.fdkfjsdk") and it will return the first dot location]. So, the document type formula is,
    =IF(ISERROR(FIND(".",RIGHT(B2,8))), "Directory", RIGHT(B2,FIND(".",G2)-1))
    Remember, column G has the reversed Strings.
  7. Next is the Location of the document, The formula for this would be,
    =IF(C2="Directory",B2,LEFT(B2,LEN(B2)-FIND("\",G2)))
    "Column-C" has the document type.
  8. Once this is done you will have a structure like this,

  9. Now share the excel sheet with others [Tools -> Share Workbook ...] and let everyone add their comments on each file. Managing the collective understanding of the documents is easy now.

Type: Document Managment, Data Analytics, Macro, String Functions
Applicability: In Consulting engagements, Better management of time, effort and knowledge.
Excel Knowledge Required: Medium
Ease of Implementation: Medium

9 Comments:

  • Hi,

    Once again after crash Nifty has started going up. Now we suggest all rises should be used as an opportunity to exit old long positions.
    This bull run will continue for few more days. Overall market is in bearish mood as in medium term its just a small rally due to short covering
    and result season.


    Happy Trading,

    ShareGyan

    By Blogger share, at 9/27/2008 12:05:00 AM  

  • Stocks extended gains Wednesday in line with Asian peers. Strong gains in high-beta sectors like capital goods and metals led the up move.

    Bombay Stock Exchange’s Sensex was at 13979, higher by 125 points while National Stock Exchange’s Nifty rose 78 points to 4052.45.

    “We had a party Tuesday in the markets. Sensex rose just 3%, scores of derivative and midcaps surged like there is no tomorrow. Average gains in 183 derivatives http://www.puntercalls.com/stock%20trading.html were about 5%. Markets will need to digest such heady gains before it moves further. 14200 is next resistance and 13600 is support for the Sensex
    Read more detail click on
    http://puntercalls.com/news/positive-start-for-equities-2.html

    By Blogger www.PunterCalls.Com - GET Stock Market Sure Calls Directly From Punter Groups, India, at 7/15/2009 02:04:00 AM  

  • Hi

    I want to ask , in column we put ReverseVell UDF and it will give the reverse of the content in column A. But what formula to put in column G. Its given for column G its stringreverse(B4) etc. But what is the code for stringreverse UDF. I need it. Another thing here its given to get Doc Type as Durectory the formula is given, but what if File like Txt, PDF, DOC is there, what will be the formula for Doc Type to return all these.

    By Blogger rani, at 3/03/2010 10:46:00 PM  

  • Jack is my friend and he asked me about excel problems. I offered him to use a tool, which was found on a soft blog. It helped me some times and probably would one of the best resolutions for such problem as well as mine - microsoft excel restore old copy.

    By Blogger Alex, at 11/03/2010 03:51:00 AM  

  • nice information,I will surely use it for my personal use.

    FundsM

    By Blogger Intact, at 5/14/2011 07:44:00 AM  

  • Hi,
    Lot of global tensions is going on at this time. Japan is expected to pull out its money from the global market as they want to revamp their country now. In current scenario anything can happen in the Share market Investors are advised not to panic and stay invested only safe traders and investors should exit their long positions on every high and one can use every decline as an opportunity to enter market again.
    Regards
    SHARETIPSINFO TEAM

    By Blogger SHARETIPSINFO, at 5/24/2011 12:24:00 AM  

  • After getting butchered in last two sessions, the market has opened in green territory. The Sensex is up 92.65 points at 20321.70, and the Nifty is up 29.85 points at 6028.90.

    Moneycontrol
    Moneycontroltips

    By Blogger PraniShah, at 11/21/2013 09:38:00 PM  

  • If you use excel documents you also may use Ideals virtual data room for management of these documents. I think it helps a lot to find any document very quick.

    By Blogger Mary Gooven, at 2/22/2016 02:12:00 AM  


  • Its very useful to me. Wonderful blog.. Thanks for sharing informative Post.

    Installment loans in alabama
    Payday loans in alabama
    Title loans in alabama
    Cash Advances in alabama

    By Blogger Philips Huges, at 7/05/2017 05:10:00 AM  

Post a Comment

<< Home