Data manipulation question in huge excel file

  Rockarch 08:54 03 Jan 2008

I am hoping someone can help with a solution for something I am trying to do in Excel.

I'm working on a spreadsheet that has sales figures from a group of shops in it.This is the output from an access database where they are gathered. I am planning to do some analysis on the sales figures and am organising the data at the moment. The company financial year runs April - March which means that meaningful year on year comparisons are difficult as Easter moves from year to year, some years have 2 Easters, some 1 & some none. I have reorganised the data so I now have a spreadsheet with sales by calendar year. Each sheet has over 25000 lines.

My difficulty is that the week numbers are from the financial year so January starts with about week 40 , goes up to 52 in March and then starts with 1 again in April and I want to keep the sales figures in calender order. What I am looking for is a quick way to renumber this column so 40 becomes 1 , 41 becomes 2 etc.
I do also have individual dates in it if there is a way of using those.

The ways I can think of are:
Find & replace - which I'll have to do 208 times for the 4 years of data
Some kind of lookup function
An 'if' function but don't think I'm up to writing one with 52 variables in it

Does anyone have a more elegant solution?
As always, thanks in advance for help

  VoG II 09:15 03 Jan 2008

I would use WEEKNUM to calculate week numbers based upon dates rather than trying to manipulate the numbers that you already have.

However beware the pitfalls of week numbers in Excel click here

  Rockarch 09:41 03 Jan 2008

Thanks VoG - this looks like just what I need.
Will get started & come back to you if I have any trouble

  Rockarch 10:19 03 Jan 2008

Works like a dream :)

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Lightwell software lets you create mobile apps without using code

Best value Mac: Which is the best £1249 Mac to buy

Comment désactiver les programmes qui s'exécutent au démarrage de Windows 10 ?