Need help transposing basically

[GoDawgs]

Here's my problem. I've got a sheet with a list of store numbers across the top, a list of every day last year down the first column, and then revenue for each store for each day. I need to get all of this data into basically a vertical list, meaning I need one column for store, revenue, and date.
Here is a very basic view of the sheet (the actual sheet is huge).
It looks like this now:

Date 401 402 403 404
1/1/02 25 10 15 12
1/2/02 14 11 10 9

I need it to look like this:

Store Date Rev
401 1/1/02 25
401 1/2/02 14
402 1/1/02 10
402 1/2/02 11

etc etc...

I am stumped on this one and it will probably turn out to be something pretty easy...and by the way, paste special/transpose didn't work...if only it were that easy. Thanks for the help.

Kevin

 

[Dreamboat]

Best bet without code:

Sort by date.

Hide 3 of 4 store columns. Select range of cells. Hit Edit-Go to, Special and choose *visible cells only*, OK.

Copy.

Paste to new sheet. Put the store number in a column and copy down.

Unhide/hide to get the next store's column....and so on.

Should take only a few minutes if you're used to the shortcut keys and stuff.

http://www.TheWordExpert.com

 

[GoDawgs]

Yeah, I thought of that, problem is I have about 500 stores and of course 365 days, so it would've been a lot of copying and pasting. I actually got a solution, somewhat convoluted, but it worked. first I listed out all the dates in a column and created a number next to it starting with 2 and counting all the way to the end of the column. Then I did the same with all of the store numbers, listing them down and adding a number starting with 2 next to it (since the first cell of data is B2). Then I used the address function to create a text cell location based on a vlookup for the date and another for the store number...and finally used the indirect function to make the text cell location into an actual reference...yeah, I told you it was pretty convoluted, but it worked, so I'm happy. Here it is:

INDIRECT(ADDRESS(VLOOKUP(C1,$J$1:$K$364,2,FALSE),VLOOKUP(B1,$H$1:$I$244,2,FALSE),4,,"det2&quot)

The ranges in the two vlookups are the two "tables" that I created and made the autonumber for. The "det2" is just the name of the sheet that my data was on.