PDA

View Full Version : find duplicates with different data and extract it



mong shen
07-11-2012, 06:43 PM
Hi everyone ! I'm pretty new here and I tried to look if the answer was already in the forum but couldn't find it ...
I try to create a VBA program which would check duplicates and generate in an other worksheet the data related to each similar duplicates.
I'll do a quick presentation of my project :
worksheet 1:
______l______________l__________l_________l_________l
_data_l___Duplicates___l___data___l__data1___l__data__l
_data_l___Duplicates___l___data___l__data2___l__data__l
_data_l___Duplicates___l___data___l__data3___l__data__l

worsheet 2
______l____________________l__________l__data1____l_______l
______lEnter the duplicate textl__________l__data2____l_______l
______l____________________l__________l__data3____l_______l

(I hope you will understand what I mean with the example )
So I would like the VBA to detect the number of duplicates and extract all the different data from one column of one duplicate and make them appear in one cell on the other worksheet.
Would that be possible ? and which VBA coding should I use ...
Sorry I'm a true learner...
Thanks for your help and any suggestion ;)

Tinbendr
07-12-2012, 10:33 AM
What determines a duplicate? Other items in Column B?

If so, you want to search for data2 from Sheet1.Col D, in Sheet2.Col D, and put the duplicate item in Col B?

mong shen
07-12-2012, 06:04 PM
actualy everything in the worksheet 1 is manualy input. Certain data in column B need to be duplicate but refering to other data in other column. There are empty cells in column B and many different duplicates.
And you guess right. I'm looking to get the data 1, data 2, data 3, etc (until 10) from sheet1 and bring it to sheet 2 in row 2. I don't know if you see what I mean. but thanks for your interest. I would be pleased if you could help me ;)

Aussiebear
07-12-2012, 07:07 PM
Please upload a sample workbook with a before and after concept. To do so please click on Go Advanced then scroll down to Manage Attachments and follow the prompts from there.

mong shen
07-12-2012, 07:24 PM
I hope it uploaded ... here is an exemple of what Iwould like to acheve ... Thank you for your support

Tinbendr
07-12-2012, 08:48 PM
OK, see if this is close.

1. Combobox on Sheet2 D1.
2. Combobox is unique list of Sheet1.Col B.
3. When combobox is changed. macro pulls each matching item data to Sheet2.

mong shen
07-12-2012, 10:53 PM
thank you that's exactly what i want ! But I'm really sorry I thought I would just need to change the referrences but I cna't there is an error... If I want to run it on my Excel worksheet how should I rename :
Sheet2.ComboBox1.Clear
Sheet2.ComboBox1.Top = Sheet2.Range("D1").Top
Sheet2.ComboBox1.Left = Sheet2.Range("D1").Left
Sheet2.ComboBox1.Height = Sheet2.Range("D1").Height
Sheet2.ComboBox1.Width = Sheet2.Range("D1").Width

with the name of my sheet being "BMS Cheque issue "

BMS Cheque issue.ComboBox1.Clear
BMS Cheque issue.ComboBox1.Top = Sheet2.Range("G2").Top

really sorry about that ...

Tinbendr
07-12-2012, 10:57 PM
Worksheets(""BMS Cheque issue")

mong shen
07-12-2012, 11:30 PM
Great ! but now Worksheets("BMS Cheque issue").ComboBox1.Clear
is underlined ... why ? should I place the combobox ? the macro does not do it by himself ?...

mong shen
07-12-2012, 11:32 PM
it says run-time error '438' ...
object doesn't support this property or method

mong shen
07-13-2012, 12:46 AM
Sorry but I have a problem with this part. does it create the Combobox or do I need to attach it to a combobox ? and how ?
Worksheets("BMS Cheque issue").ComboBox1.Clear
Worksheets("BMS Cheque issue").ComboBox1.Top = Worksheets("BMS Cheque issue").Range("G2").Top
Worksheets("BMS Cheque issue").ComboBox1.Left = Worksheets("BMS Cheque issue").Range("G2").Left
Worksheets("BMS Cheque issue").ComboBox1.Height = Worksheets("BMS Cheque issue").Range("G2").Height
Worksheets("BMS Cheque issue").ComboBox1.Width = Worksheets("BMS Cheque issue").Range("G2").Width


I also have a problem with the ;atch and count if function you hav eput on the side of the data ... wgat are theyused for ? I don't see any reference in the VBA coding ....

Tinbendr
07-13-2012, 06:19 AM
What is the name of your other sheet?

I'll just fix it for you and add more comments throughout so you can understand it.

mong shen
07-15-2012, 05:50 PM
Hi thank you for helping me.
The other sheet is called "BMS Input". (So sheet 1 is BMS Input and sheet 2 is BMS cheque issue )
The duplicates are on "I" column and the combobox should be in "G4" and it should take out the data from "BMS Input" column "S" to "BMS cheque issue from cell "G8:G18"
that would be great if you could achive this and explain it to me. Thank you so much.

mong shen
07-15-2012, 06:01 PM
ah and for the combobox to appear on my sheet should i put it first or the vba will automaticaly install it ?