Formating PageBreaks based on sequential data (sort of...)

[Wanna_Be_Guru]

I am trying to set up a macro that will automatically take a range of data starting in A4 and running horizontally to K4 with headers of:

• Account # __ Customer Name __ Address __ City __ State __ Zip __ Phone __ SalesID __ SalesManID __ Import Date __ Validation Code

and make a nice printable format for the data and take all of the codes in order and make PageBreaks after each code is complete.

The sequence I need to align the printable pages with is the Validation Code. (EXAMPLE - 1004GAS or 9095ELEC). The numbers are sequential... Well sort of. They are not EXACTLY in numerical order, but may skip integers like 1004 to 1021 then possibly to 5023 (you get my drift). I can easily make it so that I only get the raw integer by itself but I need to print the Validation Code as an indicator to what type of order it is and exactly how it will be displayed on the report at the end of the day.

I need some help getting this macro or even module to find the first line that contains the lowest validation code, copy that line (or lines if that validation code is repeated) and put it into another sheet (within the same workbook, preferably), and then go to the next highest validation code and do the same thing until it reaches the end of the data. This whole process will give me a pretty and tidy (ready to print) format for all of my orders.

So far, I am manually cutting and pasting all of this information into one large sheet that usually takes up about 1 MB of space and roughly 3 to 6 hours of my time (depending on the number of orders placed which varies from week to week). I know that there has to be a better way of doing this, so I am done fretting and now I am posting.

The way that my final layout look has this as the header row (repeated on every page):

• Account # _ Customer Name _ Address _ City _ State _ Zip _ Phone _ SalesID _ SalesManID _ Import Date _ Validation Code _ Ref Code

Then the information to be printed:

• 123456789000007 _ JOSE VITO _ 120 GRIN PL 1F _ ALBANY _ NY _ 12234 _ 5555555555 _ 800 _ 8502003 _ 8/25/2006 _ 2003GAS _ 2003

The final thought would be to put this whole mess into Microsoft Access and just run a query to give me that data back, however it's in Excel right now and I am very unfamilar with Access and can't tell you the data from the base (haha, I know... Just bear with me)

Desperately seeking sleep,

Ty (Wanna_Be_Guru)

 

[Harvey]

are the values completely sequential?
if they are you can just link them by putting some code like this in your second sheet A1:

=Sheet1!A1

and drag it down. It produces a 1on1 copy.

If they have to be ordered first, you put this line in the A1 and A2 of sheet2:

=RANK(Sheet1!A1;Sheet1!A:A;1)
=RANK(Sheet1!A2;Sheet1!A:A;2)

and drag it down. this gives an ordered list as a result, but sadly I couldn't figure how to convert the values to numbers only, so you have to insert your own formula there (I think I've been out of the running for too long ).

You can then use VLOOKUP after to get the other data from the corresponding row.
I hope this helps a bit, I know it's not complete but maybe it's just the missing piece

 

[Wanna_Be_Guru]

=RANK(Lead!L4,Lead!L4:L3181,1)

I tried that however the lead table that I use to find the value uses this type of function

=RIGHT(I4,4)

which is in Text. I tried changing the cells to Numbers, however it still didnt' give me a value (only #N/A)

Thanks for the advice, but alas, it didn't work....

Appreciatively,

Ty (Wanna_Be_Guru)

 

[Harvey]

=RIGHT(I4,4)

you can use:

=VALUE(RIGHT(I4,4))

instead to convert it to an integer, so you can then use other functions like rank.

 

[Wanna_Be_Guru]

Almost there...

I am so close! (THANK YOU HARVEY!) The Rank worked well, but I can't seem to get the VLOOKUP to work right. I guess it's all manual labor on this type of thing. I need the printable table to read the rank now and input the exact amount of lines that the rank returns, without having to input each one individually (or group of lines simply selecting them). But still, it's HOURS to get this information in the correct spots.

My friend suggested a tool in Pivot that will automatically put a PageBreak in, but I couldn't locate it. Besides, it appears that there's not way to group specific data without turning it into a number inside the PivotTable.

I guess I could design a way to get the program to read the data from one sheet and transfer it to the next table, but it seems like a Macro might work better.

Thanks again for all your help!