Adding contents of cells for duplicates

excel_noob86

New Member
Joined
Nov 10, 2009
Messages
30
Hi,

I have an employee sheet which has multiple hours rows for one employee, i want to select an employee ID and add corresponding hours and place both in a separate sheet. Here's my code so far.
Code:
Sub dup()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set NewSheet = Worksheets.Add
NewSheet.Name = "NEW"
Set NewSheet = Worksheets.Add
supcount = 2
     countresources = 0
     Do While Sheets("RS Nov 2009").Range("A" & supcount).Value <> ""
        countresources = countresources + 1
        supcount = supcount + 1
Loop
'Dim temp As Range
Dim i, j, hours As Integer
hours = 0
Sheets("RS Nov 2009").Select
     Columns("A:A").Select
     Selection.Copy
     Columns("Q:Q").Select
     Selection.Copy
For i = 0 To supcount
For j = i + 1 To supcount
If Sheets("RS Nov 2009").Range("A" & i).Value = Sheets("RS Nov 2009").Range("A" & j).Value Then
hours = hours + Sheets("RS Nov 2009").Range("Q" & j).Value
Sheets("NEW").Range("A" & i).Value = Sheets("RS Nov 2009").Range("A" & i).Value
Sheets("NEW").Range("B" & i).Value = hours
End If
Next
Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Iam getting an error 1004 at
Code:
If Sheets("RS Nov 2009").Range("A" & i).Value = Sheets("RS Nov 2009").Range("A" & j).Value Then

Can anybody help me please?? Thanks in advance :)
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

ProSolutions

New Member
Joined
Dec 15, 2009
Messages
20
Try using this instead

Range(Range("A" & ActiveCell.Row), Range("IV" & ActiveCell.Row).End(xlToLeft)).Select</PRE>
 

ProSolutions

New Member
Joined
Dec 15, 2009
Messages
20
If Sheets("RS Nov 2009").Range(Range("A" & ActiveCell.Row), Range("IV" & ActiveCell.Row).End(xlToLeft)).Select

So sorry, try this code instead of the line you had where it was erroring out.
 

excel_noob86

New Member
Joined
Nov 10, 2009
Messages
30

ADVERTISEMENT

I tried dat. D code runs but it doesn't do what i want at all. Cn anybdy help??
 

ProSolutions

New Member
Joined
Dec 15, 2009
Messages
20

ADVERTISEMENT

Can you post the sheet itself so I can see what you have there?
 

excel_noob86

New Member
Joined
Nov 10, 2009
Messages
30
Can you post the sheet itself so I can see what you have there?

I dint know how else to do dis. M pasting part of my sheet. Basically i just want to copy this into another sheet but that sheet cant hve duplicates so i need to add the hours...
<TABLE style="WIDTH: 157pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=210 border=0 x:str><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl25 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black 1pt solid; WIDTH: 59pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #3366ff" width=79 height=18>ID</TD><TD class=xl25 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black; WIDTH: 98pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #3366ff" width=131>Hours</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver 1pt solid; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>1173901</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver 1pt solid; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="20">20</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>1173901</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="140">140</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0069968</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="-1.7763568394002505E-15">-1.77636E-15</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0884552</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="164">164</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0885001</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="160">160</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C08856</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="120">120</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0910023</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="160">160</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0910396</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="23.9998">23.9998</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0910642</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="168">168</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0910698</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="197.0001">197.0001</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0911145</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="95">95</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0911379</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="200">200</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0911474</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="128">128</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0911710</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="220">220</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0911749</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="160">160</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0911993</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="40">40</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0911993</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="120">120</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0912190</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="183">183</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0912239</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="128">128</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0912516</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="160">160</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0912598</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="136">136</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0912603</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="171">171</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0912726</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="160">160</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0913061</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="160">160</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0913214</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="112">112</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0913230</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="160">160</TD></TR><TR style="HEIGHT: 4.5pt; mso-height-source: userset" height=6><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 4.5pt; BACKGROUND-COLOR: white" height=6>C0915720</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="75">75</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0915874</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="161.5">161.5</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0915877</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="120">120</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0915905</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="180">180</TD></TR></TBODY></TABLE>
 

ProSolutions

New Member
Joined
Dec 15, 2009
Messages
20
supcount = 2 (what is this and where did you dimension it?)

hours = hours + Sheets("RS Nov 2009").Range("Q" & j).Value (what is hours = hours + sheets.etc.....?)
 

ProSolutions

New Member
Joined
Dec 15, 2009
Messages
20
One more thing would it be easier to add a UserForm in which you could have a ListBox control for the ID and if you select ID #111111 it would check for duplicates and if there are dups then add the two dups hours together and paste the ID and the new value for the hours in a new sheet? Is this what you are looking to do?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,620
Members
414,081
Latest member
Subaru_Steve

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top