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, JohnJan 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
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.
Making the world a better place one fret at a time
||||||
If someone helped you, please click on the star icon at the bottom of their post
If your problem is solved, please update the first post:
EDIT, Go Advanced button, set Prefix to SOLVED
[code]
' Enclose code in tags like this
[/code]
Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks