Move rows on condition and count

[dado6612]

Hello everyone,
WIll try to explain this as best as I can
Starting at A1
Row 1 is conditional row for the code
[TABLE="width: 100"]
<tbody>[TR]
[TD]John[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Mark[/TD]
[/TR]
[TR]
[TD]Cup[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Cup[/TD]
[TD]Mark[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Steve[/TD]
[/TR]
[TR]
[TD]Pen[/TD]
[TD]Mark[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
</tbody>[/TABLE]
What I wish for is in other sheet rows from column A to be moved (not copied) by matching criteria from cell A1, added in column B value from B1, in Column C total counts moved of items in column A matching value in C

So lets say top table has been added twice to the sheet with same criteria
Starting at a1
[TABLE="width: 100"]
<tbody>[TR]
[TD]Apple[/TD]
[TD]22[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cup[/TD]
[TD]22[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If after that criteria changes to ex.
Mark and 22
and
Steve and 40
[TABLE="width: 100"]
<tbody>[TR]
[TD]Apple[/TD]
[TD]22[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Cup[/TD]
[TD]22[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Pen[/TD]
[TD]22[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]40[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And in next rows all new moved values would be filled

I hope I didn't miss anything and it's all clear

Thank you

 

[dado6612]

So I came up (with the help of google) with this code which does almost everything I need. It adds new entries, count total add.. but it makes infinitive range as new entries are added to it and looking for total count is not easy to find (its always at the bottom, while some less used entries are lost in the middle). Was trying to fix that but can't. What basicaly I need is all unique values to be listed with total count next to them. And to be updated as new entries are added. Hope it makes sense. Thank you

Sub week()application.screenupdating = false
dim copysh as worksheet
dim pastesh as worksheet
dim fr as long
dim fr as long
set copysh = worksheets("sheet1")
set pastesh = worksheets("sheet2")
copysh.range("a1:b7").copy
fr = pastesh.cells(cells.rows.<wbr>count,1).end(xlup).row+1
[FONT=sans-serif]pastesh.cells(cells.rows.<wbr>count,1).end(xlup).offset(1,0)<wbr>.pastespecial xlpastevalues[/FONT]
[FONT=sans-serif]lr = pastesh.cells(cells.rows.<wbr>count,1).end(xlup).row[/FONT]
[FONT=sans-serif]pastesh.select[/FONT]
pastesh.range("[FONT=sans-serif]c2:,"c" & lr).value=1[/FONT]
pastesh.range("d" & fr,"d" & lr).formula= "=sumproduct(($a$2:$a$"& lr &"=a"& fr &")*($b$2:$b$"& lr &"=b"& fr &")*($c$2:$c$"& lr &"))"

pastesh.range("a2","d"& lr).copy
pastesh.range("a2").<wbr>pastespecial xlpastevalues
pastesh.range("d2","d"& lr).copy

pastesh.range("c2").<wbr>pastespecial xlpastevalues

application.cutcopymode = false
application.screenupdating = true
copysh.select
end sub