Any Excel gurus out there?

[Sysman]

hear hear....
On a good contract I can get 450 a day...seldom less than 350 and whilst I've got a computer science degree and programming experience all I really need is Excel and Access. It's not the tools, it's what you do with them and in 8 or so years of doing roles often focussed around data I can tell you now there are a lot of people who may know their way round access or a spreadheet but far far fewer who know where to look for bits that need fixing or investigating and how to go about that.

The last time I had a huge pile of software licences to check against systems, Access was an invaluable tool.

Having said all that I get very very nervous when it comes to look for a new contract and I realise the only skills I can really remember are front end use of excel and access and when you search for that on Jobserve you get a load of £140 a day numbers!

At one client they had a policy of paying everyone the same rate, whether they were a VB code monkey or sysadmin or webpage wallah. It actually worked well and people were free to move between projects, pick up new skills etc.

I suspect the Oracle DBAs escaped that rule, but they kept their traps shut.

[Sysman]

This was the same guy who made me install Office 4.3 extra bits, Professional instead of Standard was it? Anyway - 32 floppy disks cos he 'needed' Access. Installed it for him, we fired it up and he says 'Right, what does it do?'

Does Office 2007 / 2010 still come with Extras which aren't installed by default? Back in Office 1997 days* you needed to load the Extras stuff to get better options for import/export of text files. Those aren't there on my locked down work PC (and to boot some clown has set it to output CSV files using semicolon as the delimiter, which isn't exactly useful when you are dealing with AD import/export tools).

* believe it or not, Office 1997 was the last version I bought myself

[Olly]

hear hear....
On a good contract I can get 450 a day...seldom less than 350 and whilst I've got a computer science degree and programming experience all I really need is Excel and Access. It's not the tools, it's what you do with them and in 8 or so years of doing roles often focussed around data I can tell you now there are a lot of people who may know their way round access or a spreadheet but far far fewer who know where to look for bits that need fixing or investigating and how to go about that.

Having said all that I get very very nervous when it comes to look for a new contract and I realise the only skills I can really remember are front end use of excel and access and when you search for that on Jobserve you get a load of £140 a day numbers!

[Pondlife]

Out of all of the skills that I know excel has to be one of the skills that has earnt me the most dough. Not becuase excel is a particularly hard skill set to learn or in much demand but the ability to analyse numbers is such a under represented skill set.

WHS.

Many a project has been saved from the brink by being able to analyse and fix master data before it gets loaded.

[Sockpuppet]

Out of all of the skills that I know excel has to be one of the skills that has earnt me the most dough. Not becuase excel is a particularly hard skill set to learn or in much demand but the ability to analyse numbers is such a under represented skill set.

[stek]

I bet it doesn't

I do bet however that Lotus 123 for DOS still does 90% of what you want Excel to do

That's right!

All I want it to do is take away my expenses from my income!

I remember in my old support days, this user had an issue with Excel - 'stek, I've found a bug in Excel, cell C22 doesn't work, doesn't get added in the sum formula...'

He was right too - he'd formatted it as text.

This was the same guy who made me install Office 4.3 extra bits, Professional instead of Standard was it? Anyway - 32 floppy disks cos he 'needed' Access. Installed it for him, we fired it up and he says 'Right, what does it do?'

Seventeen years ago I think!

[Spacecadet]

Bet Lotus 123 for DOS still does 90% of what Excel does now....

God I sound like my dad.....

I bet it doesn't

I do bet however that Lotus 123 for DOS still does 90% of what you want Excel to do

[stek]

I remember in my accountancy days in late 80's/early 90's we used Lotus 123 for DOS, slash, file, open, still fresh in my mind.

Then we got Windows and totally couldn't see the point! Still can't...

Bet Lotus 123 for DOS still does 90% of what Excel does now....

God I sound like my dad.....

[Jefferson]

=vlookup([thing_searching_for],[cell_range],1,0)

That should do it and put the machine name next to the searched for name for comparison.

Thing searching for is a single cell = "C2" the cell range is the thing your looking for. Put all machine names in one column and just set that to the column letter e.g. C:C so it searches the whole column.

Thanks for the reply Sockpuppet.
Found a couple of sample formulas very similar to yours & now up & running - Thanks for your help.

[Sockpuppet]

=vlookup([thing_searching_for],[cell_range],1,0)

That should do it and put the machine name next to the searched for name for comparison.

Thing searching for is a single cell = "C2" the cell range is the thing your looking for. Put all machine names in one column and just set that to the column letter e.g. C:C so it searches the whole column.

[Jefferson]

Funnily enough I'm doing a very similar thing across AD, Meta directory and NT domains as part of a clean prior to a big Identity and Access Management piece going in. I won't say it's stretched my data analysis abilities to the limit but it's been tricky at times.

You going to suspend the accounts first to check if they're in use?

Yep - Going to disable the accounts, move them to a seperate OU and then see if anyone shouts!
Shouldn't be too many issues as got reports from WSUS, Symantec Management console etc so can cross reference results.

[Olly]

Funnily enough I'm doing a very similar thing across AD, Meta directory and NT domains as part of a clean prior to a big Identity and Access Management piece going in. I won't say it's stretched my data analysis abilities to the limit but it's been tricky at times.

You going to suspend the accounts first to check if they're in use?

[Jefferson]

Yes, VLookup is what you want. Easiest if you consolidate your 3 lists into a single column (probably with another column alongside it, stating which list each row came from) - that way you can get everything in a single range, and do your VLookup against that.

Thanks for the reply - I'll give it a go

[thunderlizard]

Yes, VLookup is what you want. Easiest if you consolidate your 3 lists into a single column (probably with another column alongside it, stating which list each row came from) - that way you can get everything in a single range, and do your VLookup against that.

[Jefferson]

I need to tidy up an active directory environment and want to delete old computer accounts from AD.
I've got 4 lists of computer names from different scripts and reports which I have now got in Excel on 4 different worksheets.
I have a list of 1200 machine names which I want to search against the other 3 lists to see if the names appear within any of these other lists.
Can anyone give me any ideas as to how to achieve this within Excel or anything else.
I'm thinking of pivot tables or VLookup?
Any ideas gratefully received!