Combine duplicates

[Damo10]

Hi,

I have a worksheet "Data" that has lots of data and some duplicates, I would like a macro that will go through all the rows looking for duplicates that match in 4 columns (B,F,I,J) if there are duplicates found then I would like a message box asking if they are to be combined. If the answer is yes then the value of column K of the first matching row is to have all the values added of the other rows and then the other rows deleted just leaving the first matching row.

Regards

 

[nightcrawler23]

Hi,

I have a worksheet "Data" that has lots of data and some duplicates, I would like a macro that will go through all the rows looking for duplicates that match in 4 columns (B,F,I,J) if there are duplicates found then I would like a message box asking if they are to be combined. If the answer is yes then the value of column K of the first matching row is to have all the values added of the other rows and then the other rows deleted just leaving the first matching row.

Regards

Do you mean to say that for row 1 if you have same values in each of columns B,F,I,J then the sum of the values are to be given in column K of row 1? If not same then leave col K blank.

 

[Damo10]

Hi,

I would only like to add the values of columns G & K (Forgot to add column G in original post) the rest of the matching rows apart from the first which has the values changed are to be deleted.

See example below

row 9 shows what the result would be and this would replace the values in row 3 and row 4 deleted. There may be more than 2 rows with matching criteria and there could be several different matches.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
1	Machine	Index	Start	Finish	Base	KG	Flavour	Code	Fruit %	No Bags
2	1	1	00:00	00:50	THY100	800	Strawberry	BAS100	15	10
3	1	2	00:50	02:10	RTH243	1200	Raspberry	BAS101	20	18
4	1	5	02:10	06:00	RTH243	1800	Raspberry	BAS101	20	35
5	2	8	00:00	06:00	EDR500	1500	Honey	BDR453	10	5
6	2	15	06:00	07:00	WEW236	400	Vanilla	GHY777	17	10
7	2	18	07:00	08:00	DEG670	350	Cherry	CHY106	15	18
8
9	1	2	00:50	02:10	RTH243	3000	Strawberry	BAS101	20	53
Sheet1

Excel 2010

Hope that makes it clearer

Regards

 

[nightcrawler23]

I dont know on what basis u r filling the values in cols C,D,E.....
The summation part, i have shown the formula. hope it helps.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Machine	Index	Start	Finish	Base	KG	Flavour	Code	Fruit %	No Bags
2	1	1	0:00	0:50	THY100	800	Strawberry	BAS100	15	10
3	1	2	0:50	2:10	RTH243	1200	Raspberry	BAS101	20	18
4	1	5	2:10	6:00	RTH243	1800	Raspberry	BAS101	20	35
5	2	8	0:00	6:00	EDR500	1500	Honey	BDR453	10	5
6	2	15	6:00	7:00	WEW236	400	Vanilla	GHY777	17	10
7	2	18	7:00	8:00	DEG670	350	Cherry	CHY106	15	18
8
9	1					3000				53
10	2					750				28
Sheet2

Excel 2007

Cell Formulas
Range	Formula
F9	=SUMIF($A$2:$A$7,$A9,F$2:F$7)-VLOOKUP($A9,A$2:F$7,6,FALSE)
F10	=SUMIF($A$2:$A$7,$A10,F$2:F$7)-VLOOKUP($A10,A$2:F$7,6,FALSE)
J9	=SUMIF($A$2:$A$7,$A9,J$2:J$7)-VLOOKUP($A9,A$2:J$7,10,FALSE)
J10	=SUMIF($A$2:$A$7,$A10,J$2:J$7)-VLOOKUP($A10,A$2:J$7,10,FALSE)

 

[Damo10]

Thanks for your reply.

The data is imported via VBA from 4 other workbooks and listed on this sheet, I am looking for a VBA macro that will do the combining as the ammount of data imported will change daily.

Regards

 

[nightcrawler23]

In that case i would suggest to keep the formulas in a different sheet. The everytime u run your macro ur formula sheet will be update automatically.

You will need to change the ranges to the end of the excel sheets so that all of your data is covered.

 

[Damo10]

I would really prefer a VBA solution to this if possible as there is a lot of rows which differ in numbers per day.

Row 9 does not exist in my sheet as this is only an example of what to change the data to.

Can anyone help with this?

Regards