Articles

Solved Excel reformatting and display of data.

February 8, 2013 at 09:02:59
Specs: Windows 7

I have this data in a table in excel and want to display it differently.
Date Event Attendees
2/11/2013 Off-site FT James Smith
2/11/2013 Off-site PT Roy Edy
2/11/2013 Off-site PT Andy Ron
2/12/2013 Off-site FT James Smith
2/12/2013 Off-site PT Roy Edy
2/12/2013 Off-site PT Andy Ron
2/13/2013 Off-site FT James Smith
2/13/2013 Off-site PT Roy Edy
2/13/2013 Off-site PT Andy Ron
2/14/2013 Off-site FT James Smith
2/14/2013 Off-site PT Roy Edy
2/14/2013 Off-site PT Andy Ron
2/15/2013 Off-site FT James Smith
2/15/2013 Off-site PT Roy Edy
2/15/2013 Off-site PT Andy Ron

I want to display it like this. Not sure how can this be done.

Attendees 2/12/2013 2/13/2013 2/14/2013 2/15/2013
James Smith Off-site FT Off-site FT Off-site FT Off-site FT
Roy Edy Off-site PT Off-site PT Off-site PT Off-site PT
Andy Ron Off-site PT Off-site PT Off-site PT Off-site PT


See More: Excel reformatting and display of data.

Report •


#1
February 8, 2013 at 09:52:50
✔ Best Answer

Due to the formatting of your post, we are unable to tell which data belongs in which column.

Please click on the following line and read the instructions on how to post data in this forum. Then please repost your data. Thanks.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 8, 2013 at 11:15:53

While we would still appreciate it if you would, in the future, use the formatting instructions found via the link at the bottom of my post, I think I see what you are trying to do.

I assume you have this:

       A              B             C
1     Date          Event       Attendees
2   2/11/2013    Off-site FT    James Smith
3   2/11/2013    Off-site PT    Roy Edy
4   2/11/2013    Off-site PT    Andy Ron
5   2/12/2013    Off-site FT    James Smith

I assume you want this:

          F              G              H              I              J   
1      Attendees      2/12/2013      2/13/2013      2/14/2013      2/15/2013
2   James Smith     Off-site FT     Off-site FT    Off-site FT    Off-site FT 
3   Roy Edy         Off-site PT     Off-site PT    Off-site PT    Off-site PT
4   Andy Ron        Off-site PT     Off-site PT    Off-site PT    Off-site PT 


If that is correct, try this...

1 - Insert a new Column A.
2 - In A2 enter this formula and drag it down to bottom of your list:

=B2&D2

You should see something like: 41316James Smith This is the date concatenated with the name.

3 - In Column F create a list of unique names (You can use Advanced Filter to do this)
4 - In G1:whatever, Row 1, place each of your dates, along Row 1.
5 - G2, place this formula and drag it down and across:

=VLOOKUP(G$1&$F2,$A$2:$D$16,3,0)

This will use VLOOKUP to find the concatented Name and Date in Column A and return the Event values from Column C.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
February 8, 2013 at 17:01:52

That worked like a charm. Thank you.

Report •

Related Solutions


Ask Question