Hi

Let you have the current table on sheet OldData.

Create an empty sheet, and an empty table there with structure p.e. (I
assume project code is unique for project, with project name, category and
identifier same for same project code):
Month, Manager, Project, Cost

Copy all values from columns A:C in OldData to columns B:D of new table;
Enter the first month number into first cell in Column A of new table, and
copy it down for whole table;
Copy manager and project information from first 363 rows to next 363 rows,
enter next month number into sell in column A, and copy this month number to
all 363 rows;
Repeat this until all rows for months with costs available are copied.

Copy data from all 363 rows in column F (I assume this is the column for
1st month costs) of OldData to first 363 rows in column E of new table;
Copy data from all 363 rows in column G of OldData to next 363 rows in
column E of new table;
etc until all costs are copied.

Surely you have aq lot of rows now with no value in Cost column. Set
autofilter on, set autofilte to Cost is empty, and delete all such rows (you
have to do this row-wise), or set the autofilter to Cost is not empty, copy
all filtered data to new sheet (you can do it with a single go), and delete
ptreviouis new table.

From OldData, create 2 pivot tables. In first one, count all managers - you
get a manager list. Copy it to separate sheet.
In second one, count all projects. Copy the projects list to separate sheet,
and use VLOOKUP to retrieve project names, categories and identifiers from
OldData into Projects table. replace all values with formulas.

In your new data table, use VLOOKUP to get project names, categories and
identifiers into according columns. (maybe you start with defining named
ranges here?)

Your data are converted - you can delete OldData, or move it to separate
workbook for case soething goes wrong. Further you can define named ranges,
implement data validation, etc.


Arvi Laanemets


"philc" wrote in message
oups.com...
many thanks for that reply, i have racked my brains to work out how to
do this without redesigning the current table to a normalised database
style table that you suggest, but i think that it is worth doing.
a question - how do i get data from the format i have now, (a table of
363 projects (rows), by 123 columns (months) into a normalised format?