New job, new sets of data to manipulate.

The proper answer to this question is to speak to the people who control the main software and tell them to make it generate the dataset in a better way, but I'll wait a while before I start making those demands.

So, my colleague generates a big spreadsheet with quite a lot of data (CSV format, ~8000 rows). The problem is, the software generating it adds text based page breaks/headers, because it generates the report as if it is to be printed. Colleague in question manually goes through the data and removes 5 rows at a time, every ~100 rows - once a week - crazy.

The page breaks are a very regular format, and it took me 5 minutes to very nearly solves his problem with filters. Unfortunately it's not so simple.

The rows being removed are basically as follows in column A

Row 1 - (blank)
Row 2 - Regular text 1
Row 3 - Regular text 2
Row 4 - Regular text 3
Row 5 - Regular text 4

Filtering or deleting rows 2-5 is very easy. The problem is I need to remove the Blank row without removing all other Blank rows. If I filter out Blanks that removes Blank rows which are essential to the function of the data.

Can anyone suggest a way to delete all 5 rows easily?
My thought is to select Row 5 of the set and somehow delete that plus the previous 4 rows. My Excel and Google skills are currently coming up short.

Do want to filter it out or delete after the fact?

If it's the latter you could run a regex on the csv text file?

I want to remove those matching rows. I was able to filter out the 4 text filled rows, then copy the rest of the data into a new sheet. Or just delete them from the original data. Just unable to get rid of the blank row without removing all blank rows elsewhere.

Regex on the csv file is a good suggestion, rather than editing in Excel.

> ... but I'll wait a while before I start making those demands.

Every time I've decided "I'll wait till I'm less new / more settled / etc" I've later regretted it.

You make a good point. I have only been in the job for 2 weeks, and I should be having some formal training on the MRP package in question in a few weeks so I'll definitely bring it up then. In the meantime I'll look at a regex option.

MRP :'D

Fixed. I made it so all the blank rows were no longer essential. Converted some other data that wasn't required to blank, played with the set in stone formula and after a filter, a few deletions, pasting a new formula, we now have a way to save plenty of time.

We have 4 systems:
MRP
ERP
Warehouse/stock management
Invoice management

......!

Had to do a course in 4th year on supply chain management. Essentially it was a course on "look at how these companies all fucked themselves by trying to implement erp systems :'D "

Wait, why are they running all those systems? I thought the whole point of erp was to do all that shit in one place?

Honestly no idea yet. I would have thought that there would be one system that can do the job that 3 of those currently do. Or even one of the systems we currently use that can do it. I know one of them is being upgraded in a few months. I don't even think the systems have talk to each other all that well either, so some data isn't always "live".

It seems to me to be pretty standard from a manufacturing point of view so there should be a way. I can only assume someone is keeping themselves in a job supporting the lot.

Oh, but there's no fax machines, which is a huge improvement!

ERP is the big ass fix it all together solution to integrate pretty much all business functions into one system, shared databases, live data etc. Everything on your list of systems, and more, could be managed by an ERP system. In fact, one of the examples we were given in class for what it's meant to replace is people in different departments using their own spreadsheets :'D

Of course that's all big idea stuff, and mostly (according to the case studies we had to do, anyway) it doesn't work. Makes a load of money for SAP & Sage and the likes, though :D

This case study is well worth a read, if you're into economic car-crash type stories. One of the biggest pharma distributors went from multi-billion dollar company to bankruptcy because of a failed ERP implemenation :'D

http://www.uta.edu/faculty/weltman/OPMA5364TW/FoxMeyer.pdf

>>In fact, one of the examples we were given in class for what it's meant to replace is people in different departments using their own spreadsheets

The data in question is actually generated from one system, and needs all the by-hand editing to prep it for import in another :'D (fail)

Just fax it over to the dba :'D

Be serious. ERP is a process where 80% of the investment funding for a business goes to building a tiny system of entirely new work which is (1) unrelated to the day to day work of the business which continues to run on whatever under-funded IT it already had and (2) packed to the gills with very sexy acronyms and plain English words such as 'digital' and 'big'. This tiny system will 'provide new and unexpected opportunities' for the business as opposed to aiding the old and difficult problems that need to be addressed. The new and unexpected opportunities will have a dramatic impact on the career prospects of a small coterie of consultants and company staff around the senior VP running ERP. They will thrive at huge expense until the time comes to move on to a new project.

That was pretty much my understanding of it after taking that course, and I think that must have been what they wanted us to take away from it, seeing as I got a ridiculously high score for my exam paper for saying as much.

That case study is interesting though, your post on mainframes reminded me about it the other day: old mainframe system running 420k transactions per night, shiny new erp system running on shiny new hp servers managed 10k transactions per night.

An overnight job failed last night, meaning the software which forecasts demand is showing all the wrong data.