sorting data

[Dundee Lad]

I have a list of names and unique numbers down columns a and b. From columns c onward I have a week eending date as the header and numbers of hours worked. What I need to do is have only 4 columns, name, weekending, unique number and hours. can anyone help?

 

[Dufus]

Dundee Lad,

Are you asking for help converting the layout of your existing data?

Dufus

 

[Dundee Lad]

Yes, I could do it manually but I have 47000 rows and will be there all year!

 

[Norie]

You have 47000 rows already?

If that's the case then I can't see this being possible in Excel due to the current 65536 row limit.

I've done this sort of thing before using Excel VBA, but that involved smaller data sets.

I've also done it using Access VBA.

Both methods are pretty straightforward, but really depend on exactly how the data is set up.

So a sample of data, and the expected result would help.

 

[Dundee Lad]

I have copied the table below for you. I will post what I would like next

R/C	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1	Name	Unique Reference	22/03/2006	29/03/2006	05/04/2006	12/04/2006	19/04/2006	26/04/2006	03/05/2006	10/05/2006	17/05/2006
2	jelly baby	4321234	3		4	2		1	2	5	3
3	mr mallow	5423123	2	1	3	1	2			12

 

[Dundee Lad]

sorry downloaded the wrong tool I think

sample.xls
	A	B	C	D	E	F	G	H	I	J	K
1	Name	UniqueReference	22/03/2006	29/03/2006	05/04/2006	12/04/2006	19/04/2006	26/04/2006	03/05/2006	10/05/2006	17/05/2006
2	jellybaby	4321234	3		4	2		1	2	5	3
3	mrmallow	5423123	2	1	3	1	2			12
Sheet1

 

[Dundee Lad]

and this is what I would like

sample.xls
	A	B	C	D
1	Name	UniqueReference	WeekEnding	Hours
2	jellybaby	4321234	22/03/2006	3
3	mrmallow	5423123	22/03/2006	1
4	jellybaby	4321234	29/03/2006	8
Sheet1

 

[GorD]

Are you trying to go from a tabular format to a list format?

I can't follow your results from the data in the first table?

It would be possible to use a reverse pivot table to create the list but i must agree with Norie, that if you already have 47000 multi week entries (rows) then you won't have anywhere near enough space

 

[jindon]

Dundee Lad

You need to tell us the clear logic.

 

[Dundee Lad]

Gord, you have got it right. It sounds as if i will need access to do this, I know this is not the place but Norrie if you see this post you said it was pretty easy?