Reformatting a table help

**dunda1985** · 07-14-2011, 08:39 AM

Hi I'm wondering if any could help me to reformat an Excel database.

At the moment I have 3 columns of data:
Column A is the NAME of the entity
Column B is the DATE of the entry
and Column C is an information field relating to the combination of the above two columns.

So for example

Column A: Mary
Column B: Mar-2010
Column C: Information relating to Mary in Jan-2010.

Now I want to convert it so that I get a panel data where the Names are now read across in one single row, the date acts as an index down one column, and the information is the intersection inbetween the two. So for example:

Jack, Mary, John

Jan 10 #N/A #N/A #N/A
Feb10 #N/A #N/A #N/A
Mar 10 #N/A Info #N/A
Apr 10 #N/A #N/A #N/A
May 10 #N/A #N/A #N/A
Jun 10 #N/A #N/A #N/A

So, I'm looking for something similar to a pivot table, except instead of counting or summing the amount of observations or whatever, I would like to bring up the actual data.

Is this possible? Many thanks

**6StringJazzer** · 07-14-2011, 10:37 AM

One way to do this without using a macro is to add a "helper column" to the left of your original data. This helper column provides a list of unique keys for each row of your row. It is simply the name concatenated with the data. Then there is a grid with the names across the top, and the dates down the left side. For each cell in the grid, create the key by concatenating those two values, then that key is used to look up the info field using VLOOKUP.

See attached.