EXCEL spreadsheet expert wanted. D you take, cash!

[ACEparts_com]

What with the credit crunch etc I figured there's probably a chance that someone can make a few quid:

I have some data, lots and lots of data on various spreadsheets, just basic stuff like brake pad and disc fitments for different vehicles that I need to merge together, which isn't a problem BUT I think that i'm viewing the data within the boundries of my capabilites and I believe that someone who really knows there excel may be able to do something completely different with it!

Bascially, I don't know what it is I don't know, but I know there are things that I don't know.

msg me for the job & to discuss!

Alan

[Silent1]

You want to use Access not Excel, it sounds like you're wanting a parts fitment database?

[merc_man]

Alan,

I deal with data warehouses for a living so if you could just impart a little more detail as to:

1. What exact data you have
2. What you want to do with it
3. How you want to maintain it

Then I might be able to help you.

[Andyuk911]

You need to create a pivot table .. I might give you a call in a minute

[camgear]

This definitely sounds like a job for Access rather than Excel, I'm more than happy to take a look at the data for you and offer advice

[just me]

Make sure it can be accessed online...

[ACEparts_com]

Access, yes, that sounds more like it! Pivot tables - yup, whatever that is.

Please mail me - i've put a sample of each spreadsheet in to a workbook and i'm in the process of throwing together some examples of the extractions i'm aiming for, plus whatever else can be done!

[sday12]

Two good options you have there,

I would do it for you FOC, but I'm going into rehab tomorrow (after doing this stuff for far too long!)

Best of luck


Simon

[Eric Mc]

It's what I don't know I don't know that upsets me.

[Romanymagic]

They do say ignorance is bliss!

[camgear]

sday12 said:

Two good options you have there,

I would do it for you FOC, but I'm going into rehab tomorrow (after doing this stuff for far too long!)

Best of luck


Simon

Yep I'm more than willing to take a look FOC as well I'll send you a mail.

[Nobody You Know]

sday12 said:

Two good options you have there,

I would do it for you FOC, but I'm going into rehab tomorrow (after doing this stuff for far too long!)

Best of luck


Simon

Too much cake?

[Jasandjules]

If you want it to go from excel into access then convert each spreadsheet into a .csv file then it can be imported into access fairly easily.

[camgear]

You'll just end up with a massive spreadsheet in a database though, the data will most likely need splitting out into relevant groups, normalizing, and linking.

Edited by camgear on Thursday 5th March 17:20

[Silver993tt]

You need to develop a logical data model (LDM) first that reflects your business and gives you a single version of the truth. After that you need to select which physical platform is most suitable (probably Access in this case) and create a physical data model (PDM). You then need to map the logical entities & attributes from the LDM to the physical objects defined in the PDM. Next step, after a little ETL (or maybe ELT depending on your architecture), load the data into your PDM. Set up a maintenance process(es) and off you go querying your data to further your business compared to your competition

Edited by Silver993tt on Thursday 5th March 17:29

[Dupont666]

Where are you wanting to go with this?

If just basic look at Access and if further a proper database maybe of use.

Just get something like a free copy of SQL Lite FOC and then export the data in CSV format and then import it into SQL and then query the DB when you need to do something.

Then your can go as far as you want with a GUI interface, etc.

If you want i can have a look at it as i specialise in this and im bored at the moment and have loads of free time.

[benoli]

Dupont666 said:

If just basic look at Access and if further a proper database maybe of use.

or a free DB from OpenOffice

Dupont666 said:

Just get something like a free copy of SQL Lite FOC

or MySQL ?

When the data is in a structured table format, it can be moved to any DB of your choice, including my personal favourite Ffenics, and a pretty front end can be built over the data

[2something]

Not wishing to muddy the waters, but depending on the numbers involved it's sometimes just as easy to keep the data in excel or upgrade to excel 2007 if you need over 65k rows of data on one sheet.

Databases are all well and good and will do pretty much anything you might ever need, but if you're the only using the sheets then you might just be able to consolidate them and use some pivot tables / advanced filters etc.

[cyberface]

Can't believe that anyone is seriously recommending Access. Either design and build a proper database on a proper DBMS or do something clever (and maintainable) with Excel.

80% of the work is usually data cleansing anyway. Some of that can be semi-automated with fuzzy logic techniques, but when a client says 'we've got a load of data in spreadsheet templates' it usually means that the 'templates' aren't quite as identical as they think, and the data is in different locations...

And yes, I have extensive and specific experience in this area... for example, getting 20 years' worth of performance analytics out of spreadsheets which evolved over the years was an interesting project The client said it couldn't be done and was going to hire 40 temps to re-key the data, I did it with 4 temps and 6 PCs running fuzzy-logic extract code in a distributed array.

That was 10s of thousands of spreadsheets, all slightly different in format as the users had made alterations on the fly as required month-on-month, year-on-year - this sounds like a lot less data.

[anonymous-user]

Silver993tt said:

You need to develop a logical data model (LDM) first that reflects your business and gives you a single version of the truth. After that you need to select which physical platform is most suitable (probably Access in this case) and create a physical data model (PDM). You then need to map the logical entities & attributes from the LDM to the physical objects defined in the PDM. Next step, after a little ETL (or maybe ELT depending on your architecture), load the data into your PDM. Set up a maintenance process(es) and off you go querying your data to further your business compared to your competition

Edited by Silver993tt on Thursday 5th March 17:29

Yep, what he said...