Conditional row copy in Excel 4

[carrr]

I've been tasked with solving a problem while at the same time having my hands tied as to how I may arrive at the solution (sound familiar?)

The starting point is an Excel sheet (sheet1) containing approx. 3000 rows of data. The desired endpoint is to populate consecutive worksheets with the appropriate rows contigent upon UNIT_NAME (i.e. sheet2 has all rows from sheet1 where UNIT_NAME = "abcdef," sheet3 has all rows from sheet1 where UNIT_NAME = "ghijkl," etc.)

My initial reaction was to let Access do the work and call it into Excel, as Excel is the desired format for distribution, but the fear factor (on the part of the requestor) set in and I was asked to keep it in Excel from start to finish.

I guess what I'm looking for is a conditional copy solution, bearing in mind that the 3000 rows may be fewer or more from week to week.

Thanks in advance for any input/nudges in the right direction...

 

[bluedragon2]

Macroless:

on sheet 2 do an IF sheet1=abcdef then sheet1 row

then do a sort to get rid of blank rows.

You know me...anything to avoind that VBA stuff



Blue

If I wasn't Blue, I would just be a Dragon...

 

[KiwiRiccardo]

Hey check out my thread just below, "Seperating data into worksheets based on a column value.

This by the looks of it is a very similar problem.

Cheers

Kiwi

 

[SkipVought]

carr,

Why are we creating new sheets???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

You could approach this a number of ways, but one comes to mind as being fairly easy to implement. Write code that:

1) (optional) writes a numeric index in the first unused column (will be used to preserve the initial order of the data if that's important).
2) Sorts by UNIT_NAME, then by index (if you made one)
3) Set variable FirstBlockRow to first data row#.
4) Loop through rows, checking for changes in UNIT_NAME. When a change is identified (or the last row is reached):
- Do a single copy operation to transfer rows FirstBlockRow - row before change to new sheet. At this point, you may want to delete any index you created.
- Reset FirstBlockRow to row# where UNIT_NAME changed, and continue loop.

VBAjedi

 

[VBAjedi]

Skip,

How did I KNOW you were gonna ask that?

[LOL]

Granted, the question is always worth asking, but SOMETIMES there is good reason to fragment data ('specially when you can do it without harming the source data)!

Hard-nosed bosses are one such reason. [ROFL]



VBAjedi

 

[SkipVought]

Well if I were wanting to make many sheets out of one table, I'd filter on each unique value, copy the CurrentRegion, Insert a new sheet and Paste.

But I still ask, WHY?



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

Blue, if I didn't know any better, I'd call you YELLOW!



VBAjedi

 

[carrr]

Skip,

District Super wants to then be able to send a sheet containing each unit's data (and only its data) to the Unit Super. I know, I know...

 

[SkipVought]

Well I would not call Blue, Yellow!

BUT...

not to make a PUN...

BUT...

I've always wondered...

WHAT'S DRAGON, BLUE?

Hope it's NOT yer BUTT!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bluedragon2]

LOL jedi....

I really am not against it, I just don't know the syntax...

I do perl and UNIX script programming and if I try to learn to much of the VBA stuff, my head may explode. Especially since I don't use it at work.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Yes Skip, its my butt thats dragon..

I am a single pop to a 2 year old...

and by the time she gets to bed, I am done for...



Blue

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

I know, VBA. There sometimes are good reasons.

But MORE OFTEN, I have found that it's a way to get a result that could be obtained without segmenting the data.

So, I almost ALWAYS ask the question. It's the analyst in me!



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[carrr]

And rightly so, Skip.
Thanks for asking that.
I sat back and asked myself the same question after you posed it.
I just racked up a routine using sort, filter, filter, and subtotal that I think will satiate the requestor while at the same time letting me keep my dignity. Thanks for all the input, everyone.

(Who needs mind altering drugs to blow the mind when you can legally partake of a sit-down with management?)

 

[bluedragon2]

I'll give you a star carrr for going against the man

man = management



Blue

If I wasn't Blue, I would just be a Dragon...

 

[VBAjedi]

carrr,

Glad you found a solution. . .

Blue,

Props from me to pops! Glad you're stickin with the kiddo - good dads are hard to come by these days (especially ones that go by cool handles like "Blue Dragon&quot.

VBAjedi

 

[SkipVought]

Blue,

I will pray for you! Dad is the MOST important job you have.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bluedragon2]

Thanks all....

She is my life...and she's a cutie (proud papa talkin)

Hope you all have a nice holiday season and with the weather change this week, I am really blue....as in brrrrrr.

Carrr, I see your name in the security forums. Is that part of your job? I am actually doing network security engineering in my current position.

The reason I am on the Office forum is because I made my network so secure, I have lots of free time

Happy holidays everyone,



Blue

If I wasn't Blue, I would just be a Dragon...

 

[carrr]

Part of my job? Sorta, but not primarily. I do alittle at work to help with overflow, but I spend more time securing my family's XP boxes than anything, so it's kind of an informal occupation...go figure.

Happy Holidays all!

 

[rdodge]

I hear you blue dragon. I started out in my Production Statistician position doing nothing but validating data and data entry, but since then, I have automated so much via formulae and code (most of the formulae has been converted to code though), I now have a lot of spare time to help others. I am currently working on putting together an Access DB program to help weed out some of the different issues that we currently have with regards to our production reporting system, but then again, the system that I put in place was only meant as a temporary system until we get a more reliable and user friendly reporting system in place. Currently as it stands, our main DB program is reliable, but as far as our operators are concerned, it's not so user friendly cause of all of the extra tasks that has to be done. My temp program is more user friendly, but there are issues involved with it thus makes it not so reliable as a long term solution, but given the time constraint I had to get something in place so as we wouldn't be doing paper logs again, I had to get something in place within 3 weeks time. Though I haven't gotten that much into the security point of view, which I do strongly support, I have been very critical on the user friendly and data validation issues. These 2 doesn't co exist too well in Access, at least not for bound forms cause it's not 100% of the time you necessarily want the strict data validation checks of a control whenever it loses the focus (basically putting it from a visual stand point of view).

I also have 3 girls. Thankfully, I am happily married. I can't imagine how tough it would be would we have been separated. I guess I could, as I have been through tough times, but I certainly don't want to have to go through it.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.