Data manipulation question in huge excel file

  Rockarch 08:54 03 Jan 08
Locked

Hello
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 08

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 08

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

  Rockarch 10:19 03 Jan 08

Works like a dream :)

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

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

How VR is being used to simulate space

New iPad, iPhone SE & Red iPhone 7 on sale now