MCount2

M

MCheru

I have a worksheet and Columns A:C have contents in them. The contents in
every row belong together. So A1, B1, and C1 belong together, A2, B2, and C2
belong together and so on.

Every cell in Column A typically has between 10 and 15 characters.
Every cell in Column B has between 30 and 35 characters.
Every cell in Column C has 1 number in it which could be one or two digits.

I’d like to create a macro that will search column A:B. Every time a
character is repeated in Column A:B, I want to delete that row but take the
quantity in Column C that is associated to the row that was deleted and add
it to the quantity in Column C of the original character in Column A:B that
was identical but did not get deleted.

Might you help me?
 
J

Jacob Skaria

I have read this post before. You can achieve this using Pivot Table. Let us
know if you still looking for a macro to do this.

1. Assign a header for each column say Head1,Head2, Head3
2. Select the data including header. From menu Data|Pivottable and
PivotChartWizard .
3. Next...Next...Next (you will reach Step3 or 3)
4. Select existing sheet. and point the cursor to say cell E1...Hit Finish
5. Drag and drop Head1 and Head2 into Row fields
6. Drag and drop Head3 into Data item area....

This will give you what you require. From the pivot table; right click see
Table options for any modifications.


If this post helps click Yes
 
M

MCheru

Thanks for the tip. I tried you’re instructions. But I am still looking for
a macro. I pasted below what I currently have, but I still think there is a
better way.

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "a"
Range("B1").Select
ActiveCell.FormulaR1C1 = "b"
Range("C1").Select
ActiveCell.FormulaR1C1 = "c"
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R35C3").CreatePivotTable TableDestination:= _
"'[Asset Repairs - Zasor-Cleanup Macro22.xls]Sheet1'!R1C5",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("PivotTable1").PivotFields("a")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("b")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("c"), "Sum of c", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MC3 0
Macrocount 0
ColumnMatch Include Column H:I 0
Automating Pivot 0
CopyPasteSortCountMacro 3
Cumulative formula for word table 0
Column Match 8
Remove Identical words 0

Top