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