Data validation- Remove duplicate records by concatenating multiple columns to get unique

Hi,


I am new for this forum, i want to know the Data validation in excel using macros.

(1st method I know selecting Column A & B --DATA -->Remove Duplicates option-- will give me unique records)

but i want to know how this can be achieve using macros...

I attached the file which contains (originally 14900 records. But in this file only few records are given because i should know the functionality flow)
Details of the file:
flavour sheet -- flavour_id is the unique entity (primary key)
season sheet -- season_id is the unique entity (primary key)
price sheet -- flavour_id,season_id (both column A & B makes unique)


after running macro
1st it has to check whether flavour _id is there in flavour sheet
2nd it has to check whether season _id is there in season sheet
then in Price sheet it has to check for unique with 2 columns(i.e by making concatenate of Column A &B to obtain unique records)

i should obtain an Output like
unique_records -- new sheet
duplicates -records in a new sheet..

Please guide me how to resolve this data validation in Excel itself before importing data into an Database table( Sql server 2008 R2).
So that Data has to Validated before saving/closing the Excel file.

Please guide me how to resolve this using macro..


Thank you.

hi thara.p24, welcome to Excelforum, option, please check attachment, run code "test"(ALT+F11, select "test", click Run) or press Run button

Hi,

Thank you so much....
I expected the same result in which the flow has to taken place...
this attachment is very helpful for me and learners also (if you would have been written comments(for main options) in the scripts then it will helped me a lot)
please provide us a link or document/file so that we can learn (basic/fundamentals of) macros functionalists.
How we should proceed with macros, advantages of macros etc. with examples and all..
so that we can solve simple to complex algorithms in excel itself....(without entering in Database & (including triggering table) stage)

Hi,

Thank you for the solution... it is working fine...
Data can be validated in excel itself using macro functions!!!!!
validating excel file before importing into Database (tables)with triggering action...
(Database table containing composite primary key relationship/ foreign key constraints - by making unique records...)

commented code:

Please Login or Register  to view this content.

Helpful information and links: http://www.excelforum.com/excel-prog...materials.html

Hi,

Thank you for providing the comments and links..

This is the syntax for declaration of data types....
Dim price_sh_lrow As Long, flavour_sh_lrow As Long, season_sh_lrow As Long, flavour_str As String, season_str As String, uncheck As String

I am facing one issue that when i execute the same procedure with original data..

All text column will be treating as number format in output sheets...(few data having an Flavour_id (text/nvarchar) starts from "00" it is eliminating 00 character...)
I should retain the original data flow only...
(Formatting cell --Text Column A & B should be text only in output files- unique_records/duplicates)

I am enclosed the file. please refer...

plz suggest me how to ensure this.. and get proper original data in output sheets...(unique_records/duplicate)

Please Login or Register  to view this content.

please post a sample file and result expected

Hi,
I enclosed the Result sheet also for reference...
please refer....

please check attachment

Thank you...

I want to know the security options...
whether this query i suppose to post in new thread?????

Suppose we given this template to 1 end user to entry only raw_data sheet..(he should have all permission for that sheet)
He should have an read only access for customer, season,flavour sheet.. such that, the person need not insert/delete/update any records for those sheets..
& also he should not see the macro function.. (so that macro will be secured)
he just have to execute run button...
then he should get output as unique_records & duplicates sheets...

and then unique_records should be readable.. he is not suppose to entry/alter any data in that sheet..

But the developer who is developing that template of that excel with macro should have all access/permission.

I enclosed this file. please refer
Please suggest me about the security issues.....

I would suggest this issue to be posted as a separate thread as it does not relate to the original thread topic

Hi,
ok ...
Thank you...