Updated Formula question

[TLC]

Here is an update to my question posted last week:

I have a spreadsheet with close to 700 lines of information on it. The
current format of the spreadsheet is Matrix Data (Row 1 = Date; Location;
Account #1; Account #2; Account #3; Account #4; Account #5; Account # 6;
Account # 7; Account #8; Account #9; Account #12;
Row 2 = 3/1/09; Season Pass Window; $300; $400; $200; $0.00; $100; $200;
$300; $200; $0.00; $100; $200; $300)

I need to copy the data to a tabular format with the following criteria:

Row 1 = Date; Location; Account
Row 2 = 3/1/09; Season Pass; $300
Row 3 = 3/1/09; Season Pass; $400
Row 4 = 3/1/09; Season Pass; $200
Row 5 = 3/1/09; Season Pass; $0.00
Row 6 = 3/1/09; Season Pass; $100
Row 7 = 3/1/09; Season Pass; $200
Row 8 = 3/1/09; Season Pass; $300
Row 9 = 3/1/09; Season Pass; $200
Row 10 = 3/1/09; Season Pass; $0.00
Row 11 = 3/1/09; Season Pass; $100
Row 12 = 3/1/09; Season Pass; $200
Row 13 = 3/1/09; Season Pass; $300

This will repeat for the 700 lines on the original spreadsheet. I figure I
could end up with close to 6000 lines for a month.

I need to know the best way to pull the information from the Matrix Data Set
to the Tabular with out having to change a massive number of formulas. I
have tried a pivot table but could not make it work.

Also is there a formula that would allow me to break out each day on a
separate worksheet? I will not have the same number of lines for each day on
the original spreadsheet. The days may have anywere from 1 to 32 lines per
day. I would like to have a worksheet per day so the 1st may have 10 lines *
12 vs the 2nd day may have 25 lines *12 (12 is the number of accounts that
will be for each line on the original spreadsheet.)

If you need more information please let me know I am willing to email
additional information.

ANy help is greatly appreciated.

 

[Pete_UK]

You should make sure that the details that you post are accurate. This
is now the third variation that you have posted, and I presume that
you have an Account#10 and Account#11 in sheet1, though this is
omitted.

As before, put these formulae in Sheet 2 in the cells stated:

A2: =INDEX(Sheet1!A:A,INT((ROW(A1)-1)/12)+2)

B2: =INDEX(Sheet1!B:B,INT((ROW(A1)-1)/12)+2)

C2: =INDEX(Sheet1!C:N,INT((ROW(A1)-1)/12)+2,MOD(ROW(A1)-1,12)+1)

Then just copy these down as far as required.

Hope this helps.

Pete

 

[Luke M]

You can setup the headings (row 1) but this macro will reorganize all the
data for you. You just need to define the start and destination worksheets,
and the range size, if you ever have more than 750 rows.

'==============
Sub ReorderData()
'Destination sheet
FinishSheet = "Sheet2"
'Define sheet with data
StartSheet = "Sheet1"

Sheets(FinishSheet).Select
i = 2
'Increase Range size if needed
For Each cell In Sheets(StartSheet).Range("C2:N750")
If cell.Value = "" Then GoTo SkipMe
x = cell.Row
Cells(i, "A") = Sheets(StartSheet).Cells(x, "A")
Cells(i, "B") = Sheets(StartSheet).Cells(x, "B")
Cells(i, "C") = cell.Value
i = i + 1
SkipMe:
Next

End Sub
'=================

once you have the data in tabular form like this, you could easily create a
PivotTable which you could breakdown by day (and even allow a drill down
option, if desired).

 

[Pete_UK]

One other thing - ensure that column A is formatted as a date,
otherwise it will show as numbers.

Pete

 

[TLC]

THank you for your help on this. And yes I do have an account 10 and 11. I
am sorry that my first two posts have not been as accurate as this third one.
I did try the entries you gave me originally and they did not work. I will
try them again and update the post. Sorry if I have caused any problems with
my posting.

 

[TLC]

THank you for your help. That worked for all but one account. On the 12th
account I get a #REF instead of a $0.00.

Here is what is in the formula bar:

=INDEX(Sheet1!C:N,INT((ROW(A1)-1)/12)+2,MOD(ROW(A1)-1,12)+1)


Can you also help me with how to set up my worksheet(s) so that they will
only show one days worth of information I would like to end up with 31
workheets, each being 1 day for the month. However each day on the original
spreadsheet will change how many locations appear. And it may change for
each month also.

Say for example March & April
March 1st may have 13 locations and April 1st may have 20 locations
March 2nd may have 20 locations and April 2nd may have 13 locations?

Is there a formula or function that I can enter in to have it only pick the
information based on a date?

Thank you again for all of your help.

 

[Pete_UK]

The formulae worked for me before I posted them, so perhaps there is
something different about your data layout.

On the other problem, would you enter a date in each worksheet to
determine which data to bring across? If so, what cell would you
envisage using for this - if not, how would Excel know which date to
use?

You can send me a sample file (.xls not .xlsx) to:

pashurst <at> auditel.net

(change the obvious). Indicate in the sample exactly what you would
like to achieve.

Pete

 

[TLC]

Hi Pete,

I figured out the #REF problem. As for the separate days issue I could
enter a date for each one. I will send the file.

 

[Pete_UK]

Okay, file received, but it's getting a bit late here so I'll take a
look at it tomorrow.

Pete

 

[TLC]

Thank you. I look forward to your reply.
--
TC

Okay, file received, but it's getting a bit late here so I'll take a
look at it tomorrow.

Pete

 

[Pete_UK]

Hi Tammy,

I've worked on the file and sent it back to you.

Pete