J-Walk & Associates, Inc.

Arranging Your Data

Last week, someone sent me a workbook because he was having some problems with it. Here's a small section from the file:

It was arranged in categories, and each category had tasks below it, arrange in a row. Under each task name was date to indicate when the task was performed. Many of the date cells contained a cell comment to clarify (3,810 comments in all). Summary formulas were entered to count the dates. And each formula was hand-crafted because the layout was so haphazard.

I was very surprised to see such a poorly organized worksheet. But, after giving it some thought, this sort of thing is probably not at all unusual. The typical user, I think, probably starts entering data without giving the organization much thought. At first, it's easy makes sense. But after a few years of data entry, you end up with a complete mess.

I suggested that this person spend a day or two and copy/paste the data into a normalized table with four column headers: Category, Task, Date, and Comments. Data entry would be much easier, and the information in this table can be easily sorted, filtered, or summarized with a pivot table.

To a typical user, it probably seems very inefficient to repeat the category name and task name for every entry in the table. But it's actually the most efficient way to store data.

- Reader Comments -

Following are comments in response to this item.
The most recent comment is at the bottom.

1. By chip. Comment posted 13 March, 2010 7:59pm

   Yeah, I see that kind of thing a lot. Less in my company since we've done some pretty good training (though people new to the company find the effort to create organization annoying sometimes since it's not how they always did it.) We see the most problems with junior people who skip the training because their boss says their too busy, so they throw things together however they think best. But clients occasionally send something over for "a little help" and I usually end up writing a macro to locate the appropriate info and arrange it correctly. At first they are annoyed and concerned, until they see the power of being able to pivot, filter, extract, graph, Gantt chart etc. anything they may want.
2. By teylyn. Comment posted 14 March, 2010 12:44am

   I can confirm this unfortunate practice. I post in some of the Excel Help Forums on the web and often see questions where people want to consolidate data that has been arranged in a format that is more like a report than a data table. Over and over again, I find myself urging them to use a simple, one-row-per-record format to enter data, instead of using different columns for different months, for example.
   
   It's amazing how simple life can be if you use a normalised table for data entry and then run a pivot table for summarizing.
   
   On the contrary, you'd need array formulae and other complicated constructs to pull the data from a table that is already organised as a report.
   
   I've often wondered whether it is OK to tell people that their data structure sucks. You don't want to put them off, do you? But then again, for the learning curve, there should be two simple rules:
   
   1. Data entry on one sheet, simple table structure
   2. Data analysis on subsequent sheets
3. By Jon Peltier. Comment posted 14 March, 2010 8:42am

   "But it's actually the most efficient way to store data."
   
   ... short of building a relational database.
4. By Wendy!. Comment posted 14 March, 2010 7:43pm

   I see an unbelievable amount of spreadsheets like this in spite of years of tips, templates and sample spreadsheets. They always come to me like this except for one difference: The category titles take up to 3 rows. Sometimes row 1, sometimes 1 & 2, or 2 & 3, sometimes 1, 2 & 3. If 3 rows are not enough the space bar is used instead of word wrap. I am convinced it is a learning disorder.
   
   Job security.
5. By Joe. Comment posted 18 March, 2010 11:19am

   Why "spend a day or two and copy/paste the data", when you can write some VBA to pull the comments out into cells, and then take advantage of the limited organization to transform the data into a proper structure.
   
   Form what I can see, I would say about two hours of work to get it reshaped, conflicts pulled out to make decisions on, and ready to explore/analyze/answer questions.
   
   Copy/paste is not the best answer when you have a mass of data like that. You could compound or introduce additional errors by moving data around by hand, not to mention it is tedious.
   
   Let me know if you want additional details on how I would approach that spreadmart.
6. By chip. Comment posted 19 March, 2010 10:52pm

   Joe, I agree, but it seems like someone who'd arrange their data that way in the first place is not likely to be proficient at VBA. Or maybe even have ever used a macro.
7. By gdv. Comment posted 24 March, 2010 5:00pm

   @Joe:

   "Let me know if you want additional details on how I would approach that spreadmart"

   
   I'm a noob when it comes to VBA, but I'd sure like to learn more about how to do what you've described. I have spreadsheets I've used simply to accumulate lots of notes on various topics (e.g., solvent characteristics, computer How-Tos, ToDo lists, etc. ...even a running list of recent Excel Files because the Recent files list kept by Excel isn't near long enough), but after a while they do become pretty cumbersome. I'd like to better understand how they could be better organized and how to clean up what I already have.
   
   Not sure exactly how you could help me get started, but if you could explain a little more and/or point me towards an introductory resource, I'd sure appreciate it! A brief before and after example of a poorly organized spreadsheet that has been VBA transformed would be really nice if you have one or know of one.
   
   -gdv
8. By Frank. Comment posted 17 September, 2010 11:53am

   VBA would only work if the originals had some order in them. Sometimes there is no order, you could spend days programming excel and you would not have the desired results.
   
   VBA solution in this case is like recommending somebody to keep eating fat and then perform a bypass whenever necessary. Kind of risky.