Help with Formula to organize information / date fields

[LauraM]

Hi, I'm hoping you can help me with a problem. At work I work with a
database of information ( the database is locked, so I can't just add a
report for the info that I need..)

What I have is a list of information - with names, roles, and years. What I
would like to do is find an easy way to have the date information in multiple
columns so we don't print off a new line for each person.

Existing Data

Col A Col B Col C

Laura 2006 Job 1
Laura 2007 Job 1
Bob 2006 Job 2
Bob 2006 Job 1
Bob 2007 Job 1


Ideally I'd like the data displayed as follows (but with years going up to
2009).

Name 2006 Job 1 2006 Job 2 2007 Job 1
2007 Job 2

Laura X
X
Bob X X
X


Any suggestions or tips on how to do this? In my list of data there are
some names repeated up to 30 times, if htat helps.

 

[Dave Peterson]

You may be able to use a pivottable.

Add headers to your data if you don't have them already.

In xl2003 menus:
Select the range (A1:c###)
Data|pivottable
Follow the wizard until you get to a step with Layout on it.
Click that Layout button

Drag ColA header to the row area
Drag ColB header to the Column Area
Drag ColC header to the column area
Drag ColC header to the data area (yep, again)
If it doesn't say Count of ColC, then double click on that icon and change it to
Count.

And finish up the wizard.

Because you have two fields in the column header, you'll see a subtotal for each
field.

If you don't like that, you can hide the ColB subtotals.
Rightclick on any of the 2006 in the column headings and choose field settings.
Change the subtotals to none.

I sometimes just weasel out and add a new field (column D) that combines my two
fields into one:

=B2&"."&c2
(and drag down)

Then add the header in row 1 and use that in the Column area and data area.

 

[Dave Peterson]

ps. The pivottable will show a count -- not X's. But that's usually ok for me.

If I want X's, I'll copy|paste special|values (so the pivottable is gone and
it's just data). Then I'll select that range and change the numbers to X's.

 

[Daryl S]

Laura -

You can use a pivot table to do this. Since you want the column headers to
be a concatenation of the year and role, you will want to add a column to
your data that does that (e.g. cell D2 would be =B2 & " " & C2). You will
want column headers in your data such as A1 = Name, B1 = Year, C1 = Role, D1
= Year Role.

Then use the pivot table wizard to select the entire range you want,
(including the headers). Put the 'Name' in the row section, and the 'Year
Role' in both the column section and the data section. This should default
to Count, which will show you a '1' instead of the 'X' in your example, but
the same data. You can turn off the totals column and row if you don't want
to see them.

Hope that helps!

 

[Paul C]

If you rearrage your headers slightly like this
Col A Col B
1 2006
2 Job 1
3 Laura X

This formula is B3 then copied over and down should to the trick.

=IF(SUMPRODUCT(--(Sheet1!$A$3:$A$7=$A3),--(Sheet1!$B$3:$B$7=B$1),--(Sheet1!$C$3:$C$7=B$2))>0,"X","")

Just change the references Sheet1!$A$3:$A$7, etc to whatever your actual
data range is. The abosolute references are critical to allow you to copy
over and down.