|
Title: Excel Guru, Help needed! Post by: millidonk on July 09, 2012, 02:25:03 PM I have done a normal dupe check but I need to narrow it down further, I thought it was possible to check for duplicates on the say the first 5 figures from the Left and the first 5 figures from the right etc. Anyone know if this is possible/how to do it.
I have tried many variations of stuff like: =countif('File 1 Sent'!$A$1:$A$3045,'Match 3'!LEFT(F2,5)) Where 'File 1 Sent' is 1 sheet, 'Match 3' is another sheet, F2 is the cell I need to check with the first 5 figures from the left from the range of A1-A3045 Any clues? ;carlocitrone; Title: Re: Excel Guru, Help needed! Post by: StuartHopkin on July 09, 2012, 03:10:34 PM Are you allowed an extra column.
Insta works if you make a column to show the 5 digits seperately then countif them against each other. No idea why but it doesnt seem to work if you use the LEFT function on a range of cells in the COUNTIF formula Title: Re: Excel Guru, Help needed! Post by: millidonk on July 09, 2012, 03:20:05 PM Yea, thats similar to what I ended up doing, merging all the data into the one sheet and just adding a new column which I will delete once I've finished all the matching.
Used this formula which is pretty handy as i can just delete and change the number of characters until I am happy. =IF(LEFT(L1,8)=LEFT(L2,8),"Match","No Match") I think the left function doesn't like the countif when it is across seperate sheets but not sure? I msgd Mere to see if he knows anything. Will give your suggestion a go on the next lot as I think it might be a bit faster. Cheers. Title: Re: Excel Guru, Help needed! Post by: smashedagain on July 09, 2012, 03:59:37 PM Is your baby nearly here. Pretty sure you must have other concerns right now :)
Title: Re: Excel Guru, Help needed! Post by: millidonk on July 09, 2012, 05:32:34 PM Is your baby nearly here. Pretty sure you must have other concerns right now :) Lolol, i still have a job to do, they don't just say put your feet up as your baby is nearly here.. Yea, now officially overdue, they let you go upto 2 weeks. It's ace as the missus wants a curry nearly every night! :D Title: Re: Excel Guru, Help needed! Post by: smashedagain on July 09, 2012, 06:06:55 PM Is your baby nearly here. Pretty sure you must have other concerns right now :) Lolol, i still have a job to do, they don't just say put your feet up as your baby is nearly here.. Yea, now officially overdue, they let you go upto 2 weeks. It's ace as the missus wants a curry nearly every night! :D Title: Re: Excel Guru, Help needed! Post by: rex008 on July 09, 2012, 07:26:48 PM Is your baby nearly here. Pretty sure you must have other concerns right now :) Lolol, i still have a job to do, they don't just say put your feet up as your baby is nearly here.. Yea, now officially overdue, they let you go upto 2 weeks. It's ace as the missus wants a curry nearly every night! :D Is that a euphemism? You do know the other way you can bring labour on, right? Or has she not told you about that one? :D Good luck with it! Just celebrated 10th and 9th birthday of my 2 boys. Won't tell labour stories, but the first one was NOT pretty. |