Compare two columns and calculate sum
Closed
Shri
-
Mar 24, 2010 at 04:38 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Mar 24, 2010 at 10:13 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Mar 24, 2010 at 10:13 PM
Related:
- Compare two columns and calculate sum
- Beyond compare - Download - File management
- Display two columns in data validation list but return only one - Guide
- Tweetdeck larger columns - Guide
- How to calculate 90 days from date in excel - Guide
- Excel compare two sheets - Guide
1 response
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Mar 24, 2010 at 10:13 PM
Mar 24, 2010 at 10:13 PM
two macros are given . The operative macro is "test".
macro "undo" undoes the results of the macro
on the sample workbook run the macro "test"
if you want to recheck run "undo" and then run "test"
if it is ok you can use these macro in your original file
macro "undo" undoes the results of the macro
on the sample workbook run the macro "test"
if you want to recheck run "undo" and then run "test"
if it is ok you can use these macro in your original file
Sub test()
Dim r As Range, c As Range, x As String
Dim j As Integer, k As Integer, m As Integer
Dim r1 As Range, dest As Range
Dim reg As String
m = 0
Worksheets("sheet1").Activate
j = Range("A1").End(xlToRight).Column
For k = 1 To j
With Columns(k)
reg = Cells(1, k).Value
Set r = Range(Cells(2, k), Cells(2, k).End(xlDown))
'MsgBox r.Address
For Each c In r
x = Trim(c)
With Worksheets("sheet2")
Set r1 = Range(.Range("A2"), .Range("A2").End(xlDown))
m = m + WorksheetFunction.CountIf(r1, x)
End With
Next c
End With
With Worksheets("sheet3")
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
dest = reg
dest.Offset(0, 1) = m
End With
m = 0
Next k
Worksheets("sheet3").Activate
End Sub
Sub undo()
Worksheets("sheet3").Cells.Clear
End Sub
