check duplicated data

I am looking a program to check the date be duplicated to avoid any mistakes.

There are many program for duplicate check but they all only compare the cells but not to check the data within any cells one by one.

Is there any program to check the duplicated data in the selected column, columns or cells?

Attached to show what I want, C235 be input duplicated.

XLForum_kevincwk2000_check duplicated data
------------------
Hi, Try this. It is set up for Data in Column "B" from range "B1" on.
Alter the range ref at top of code to suit you.
If the data is duplicated more than once, the message box will show the duplicate more than once.

Please Login or Register  to view this content.

Regards Mick

Originally Posted by MickG

XLForum_kevincwk2000_check duplicated data
------------------
Hi, Try this. It is set up for Data in Column "B" from range "B1" on.
Alter the range ref at top of code to suit you.
If the data is duplicated more than once, the message box will show the duplicate more than once.

Please Login or Register  to view this content.

Regards Mick

Hi Mick,

How to run the code, is it macro for VB?

regards,
Kevin

Hi, Here is some general information for getting your code working.

Inserting a Command Button in worksheet and Allocating Code

Open your workbook at the Sheet Number with your Data that you want to Manipulate.
Click View, Tools, Control Box,---- Control Box Menu Appears on sheet.
Slide you cursor over the Tool Box until you find a Command Button.
Click It then click Somewhere on the sheet.-- Command Button appears on sheet.
The Command Button will have handles round its edge, This is so you can Position it on the sheet.
Click the command Button "Hold the Mouse Click Down" and Drag the Command Button to where you want it.
Double Click the Command Button, The VB Editor window will appear.
You can also open the Editor By clicking Alt + F11, but if you double click the Command Button the editor will open in the procedure relating specifically to your Command Button.
If the VB Editor window has two panes the right pane is where you must paste your code.
The left pane can be "Project Window" or "Properties Window, Click "Ctrl+R" if not showing.
Paste your code just under the words "Private Sub CommandButton1_Click() " in the Right hand pane.
If you have done this correctly. Scroll to the bottom of the code and you should see the words "End Sub"
On the VB Toolbar you will see a Green Triangular shapes icon,.
This is to change the VB Editor mode from "Run Mode" to "Design Mode "
Click this Icon, The Small blue square to its left will change from light blue to dark blue, or Vice Versa. Before you close the Editor make sure this Square is "Dark Blue" i.e. (Reset)
Sometimes the it will appear Reset when it is not, that why I usually put a message at the bottom of the code, to know if is run or not.
Close the Editor. Select the Command Button and Click it.
When the code Runs the Msgbox should appear With The Message "Transfer Complete" ,if this doesn't happen Open The VB Editor "Alt + F11" and click the "Reset ( Blue Square) and or The "Green Triangle" on the Tool bar . The Blue Square should be "Dark Blue ", in order to run the code..
NB:- If you want to get back into this specific code through the Command Button.
Get the Control Box menu back on the screen, Click the green triangle, When you slide the cursor over your Command button , The cursor shape will change to a "Arrow Headed Cross" , you will then be able to double click it to view your code.
If you prefer, you can forgo all this hassle by putting the code in an MT macro, with a key combination like (Ctrl+"A") to run it.
Don't be daunted by all this, when you get the hang of it, it will seem quite simple
Regard Mick

Dear Sir,

Here are what I expected to the Macro.

i. trim all spaces in field of "location"
ii. check the QTYs matching to the number of location, the total qtys would be counted by no. of comma - 1
iii. check duplicated datas in in field of "location"

http://mail.hightech.com.hk/~kevincwk/E-System/EX3.xls

SHEET3 is the original sheet while SHEET3A is to illustrate my expectation as :-
- "4" are checked mismatched QTYs be highlighted.
- C82, C83, C3001 are the duplicated data be highlighted.

thanks,
Kevin

Hi, Just to Confirm you requirement,.
(1) All values in Column Location to be highlighted "Red" if duplicate.
(2) Count each "Set" of values in Column "Location" and insert the correct Number in corresponding Row, Column "Qty"
NB:- I see in sheet "3a" that Number "C3001" in Row (7) of Column "Location" is not Highlighted, and the last Row of Column "Qty" shows "6" when there is only (1) in the corresponding Column "Location", is this an oversight or something I've missed.
Regards Mick

Hi Mick,

Yes, you are correct.

the sheet3A was not checked by any Macro programs but it was checked and modified manually to illustrate what I need.

thanks,
Kevin Chan

Hi Kevin, Trythis:- It also checks for duplicates in single cells.
NB:- There was odd bits of Junk in that data !!

Please Login or Register  to view this content.

Regards Mick

Hi Mick,

The program seems work well.
All the duplicated data are highlighted in RED. Right?

Is the Macro able to check and highlight all the duplicated datas in multiple-worksheet?

http://mail.hightech.com.hk/~kevincwk/E-System/EX4.xls

from the EX4.xls, there was duplicated datas, C856, C83, C843,C1280, C864 in SHEET4.

thank you,
Kevin

Hi Sorry I can view your file for some reason.
Do you want to check all sheets in a workbook or just some.??
If "some" Please specify the onces you don't want to check.
Regards Mick

Hi Mick,

Yes, I want to check all sheets in a workbook.

Regards
Kevin

Hi Kevin, This seems to work.

Please Login or Register  to view this content.

Regards Mick

Hi Mick,

I tried the latest Macro from you on the file ex4.xls but got very strange result

http://mail.hightech.com.hk/~kevincwk/E-System/EX4A.xls

Ex4a.xls is to illustrate the results

thanks,
Kevin

Hi Kevin sorry can't download your webpage, can you try just attaching it to the thread as an .xls file
I don't know what your results were, but I assumed the Data would be in the same columns for all sheets.
Mick

Hi Mick,

attached is the file, ex4a.xls which I put macro named "check_duplcated_mick_multi()" already.

Run that Macro will show quite strange result

thanks,
Kevin