Excel database help...

[MaLicE]

right ok i have been asked to do some database rubbish by my mum...

basically she has 5 databases (name, address, number and comments) each database is only 1 sheet... they are all in alphabetical order

now she wants all of the databases brought together and made in to one but each on seperate sheets which is easy to do, just copy paste on to a new sheet...

however she then wants each database compared with each other to see if there are any duplicated fields across them... then need them highlighted etc...

so basically i need to know if there is a way to have a formular to compare sheet 1,2,3,4,5 top to bottom and if there is a duplicate entry to highlight it.... is this possible? anyone know how to do it? been years since i used excel and i would rather do it this way then spend hours doing it manually lol...

any help appreciated.

 

[JoedotwasT5phy]

so basically you wanna know if each sheet is identical?

Probably wrong section

 

[MaLicE]

not completely identical... just if there are duplicates from one sheet to another...

 

[JoedotwasT5phy]

not completely identical... just if there are duplicates from one sheet to another...

Right - so you wanna know if Mrs Smith is on sheet 1 & 2?

 

[mace¬]

Probably be easier to import the spreadsheets into Access format and then do some simple SQL to select duplicates?

 

[JoedotwasT5phy]

Alright - f**king know it all....

What if they can't access Access??

 

[mace¬]

Get fooking me, I'm probably wrong anyway.

But if you can't Access Access I'd go with an abacus and a ruler.

 

[chris_182]

Don't need Access. Just put an extra column in each spreadsheet stating the number of the spreadsheet. Then lump them all together into one long list and sort alphaspaghettically. Use an IF statement to compare each name to the previous on the list and label with a 1 or something if a duplicate. No point *****ing around in Access

 

[mace¬]

Pah!!! That was option number 2

 

[JoedotwasT5phy]

Don't need Access. Just put an extra column in each spreadsheet stating the number of the spreadsheet. Then lump them all together into one long list and sort alphaspaghettically. Use an IF statement to compare each name to the previous on the list and label with a 1 or something if a duplicate. No point *****ing around in Access

Good plan,

Or just stick it all in a pivot table - that will list any duplicates

Edit: See mace - Roll ya nek in boi!!

xx

 

[chris_182]

well I'd do it in Access too tbh

 

[mace¬]

Excel is for Girls and Secretaries.

Fact... so get your skirt on T5 your my b**ch tonight.

 

[JoedotwasT5phy]

Don't side with Mace - he'll bleed you dry if he thinks he's in.

Mace - Crouchless chaps again?

 

[Rhino172]

I'd go with Chirs' option...I think you could probably do simular using conditional formating...but prob won't be quite as acurate...if this cell doesn't = that cell make it bold kinda job...

oh and excel is great for keeping stats, making graphs etc but access all the way for a database!

 

[JoedotwasT5phy]

Who asked you?

 

[Rhino172]

pmsl

I can't use access for s**t tbh

 

[MaLicE]

Right - so you wanna know if Mrs Smith is on sheet 1 & 2?

yes thats pretty much it, i want a formular to search sheet 1, 2, 3, 4, 5 starting at A and compare them all to see if there are any duplicates, so if mrs smith is on sheet 1 and also on sheet 4, i want it to highlight her on sheet 1 and 4...

 

[JoedotwasT5phy]

pmsl

I can't use access for s**t tbh

Lol - who really can

Excell FTW

 

[mace¬]

Don't side with Mace - he'll bleed you dry if he thinks he's in.

Mace - Crouchless chaps again?

Hi x x x

Excel loses I think. Back in your box

 

[JoedotwasT5phy]

yes thats pretty much it, i want a formular to search sheet 1, 2, 3, 4, 5 starting at A and compare them all to see if there are any duplicates, so if mrs smith is on sheet 1 and also on sheet 4, i want it to highlight her on sheet 1 and 4...

Ok - i would use a pivot table - PM me if you want instructions on what i would do

 

[JoedotwasT5phy]

Hi x x x

Excel loses I think. Back in your box

Sorry :slap:

Wheres your car BTW?

 

[Rhino172]

lol you could probably do a Vlookup formula too....check it out in the help screens on excel, although that would meen giving each worksheet a range name etc and tbh it's a lot of pissing about...

 

[mace¬]

Sorry :slap:

Wheres your car BTW?

In the garage, all tucked up for winter. Only 5 months 20 days to go:approve:

 

[JoedotwasT5phy]

In the garage, all tucked up for winter. Only 5 months 20 days to go:approve:

Walking FTL!!!

 

[mace¬]

Sat on the bus practicing Excel formulas.

£1 a day, gay bus faresaver.

 

[JoedotwasT5phy]

Sat on the bus practicing Excel formulas.

£1 a day, gay bus faresaver.

Loser

 

[mace¬]

Of the highest degree.

172 on order though...