's Forum Archives - Non-Cycling Discussions

Archive Home >> Non-Cycling Discussions(1 2 3 4 )

AAARRRRGH!!... help! Any Excel wizards here?(6 posts)

AAARRRRGH!!... help! Any Excel wizards here?lonefrontranger
Apr 10, 2002 4:03 PM
I know, I know, it's work related and therefore not fun. But I really have to know this, because this is a gigantic list we receive and have to clean and validate daily. Demographically there are many, many computer & engineering whizzes in cycling, so someone on this board should know this. ;-)

Question: How do I compare information in an array, then throw out ALL the duplicate rows? Please note, I do NOT want the Advanced Filter function where you check the "unique records only" radio button. If there are two matching rows, I want to delete BOTH of them, and return ONLY unique rows in the result. This list is about 580 rows (a list of purchase order #s), and of that there are ~ 300 unique numbers to compare, so the Filter features (greather than, less than) won't work - too many variables.

I thought I was onto something in the "MATCH" function, but can't get it to work. I'm not a great Visual Basic guru, so my macro writing skills are kinda shaky. I know this has to be easier than I'm making it.

Any help or advice is greatly appreciated and will get you huge karma points and my everlasting gratitude (for what that's worth). And if you're ever in the Boulder area, I'll buy you a beer.

Apr 11, 2002 8:57 AM
according to an excel guru--set up a control column that compares the value of the current record with the value of the previous and following records and then sort that information out and delete the resulting rows. email if you need details--he is an excel guru and sits next to me at work.
forumulas as an exampleColnagoFE
Apr 11, 2002 9:07 AM


Jeff H =IF(OR(A4&B4=A5&B5,A4&B4=A3&B3),1,0)
Loren K =IF(OR(A5&B5=A6&B6,A5&B5=A4&B4),1,0)
Carlos R =IF(OR(A6&B6=A7&B7,A6&B6=A5&B5),1,0)
Carlos R =IF(OR(A7&B7=A8&B8,A7&B7=A6&B6),1,0)
It WORKED!!lonefrontranger
Apr 11, 2002 11:08 AM
My co-workers are at my feet, and I owe you AND your co-worker a beer.

Thanks huge. This just helped us avoid at least 2 hours of manual scrubbing and validation daily.
no problemColnagoFE
Apr 12, 2002 8:52 AM
I know little to nothing about Excel, but it helps to sit next to someone who does. To tell you the truth anything related to spreadsheets and databases just bores the heck out of me! ;)
you rule, will let you know the result! (nm)lonefrontranger
Apr 11, 2002 10:15 AM