Scanning for Duplicate Data in a Column, Merging Data and Finally Removing All Duplicates...?

[: VB :]

Hello,

Firstly, my thanks to Dick Kusleika and Ken Wright for replying to my
original posts regarding how to merge 3 columns in my Excel 2000
spreadsheet. I have another query related to this that I'm now stuck on...

I have realised that within the spreadsheet of 20,000 records, there are
repeat rows - basically, the spreadsheet lists customer information and
also, notes. Rows of customers can be identical except for the notes field
which is always different.

I'm using the following formula to merge and "separate" the 3 columns:

=A1&"-"&B1&"-"&C1

This part works perfectly - i now need to know if its going to be possible
to write a macro/formula to scan through all the 2nd column looking for
duplicates (the second column being Customer Name) and then somehow merging
their unique Notes together into 1 column/row - it would also, be a good
idea to automatically remove all "duplicate rows" after they have been
processed so I'm left with a spreadsheet full of individual rows of data -
no duplicates.

Could you let me know how i would go about trying to achieve this
successfully?

Many, many thanks for pointing me in the right direction - i hope that this
additional request is "doable" and that you can let me know either way...?

Best Regards,

Alex

 

[keepitcool]

I'd say.. split your database in 2 tables:

1 custID, custName etc
2 custID, noteDate, NoteDesc etc

retrieve the cust then lookup their notes.

i realize that any redesign is a chore..
but with 20.000 records I assume /presume
it's outgrown the original idea..



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[: VB :]

Hello,

Thanks for your reply...

I am wondering if what your suggesting is the best route to take since i am
stuck at the moment - i think its worth a try but it does pose another
problem...

How would i ensure data-integrity when exporting columns of data from Excel
into Access in 2 different tables...?

Have you got any tips in this scenario...?

Many thanks,

Alex