VBA Duplicate Name Check and Calculating the Values

Hai

I am New to VB Macros . I want to Check the Duplicate Value in the Excel Value Using Macro VB editor

Eg:

If the 1 St Row Contains Name as " VB" and the Value For Vb is 30
2 nd Row Contains the Same Name "VB" and the Value for this is 20.

I want to Remove the Duplicate Name and I want the output like this

VB and the Value is 50 (30+20)

Can Anyone Tell How can I Get this? Can AnyOne Send me the Code for Adding the Value After Checks the Duplicate Name

I Written One Method that Generates the Values in the AboveSheet. I want to Check the Duplicate Value in the Sheet

Thanks in Advance

Kumar

Hai,

I am New to VB Macros .

I want to Check the Duplicate Value in the Excel Value Using Macro VB editor
Please Open My Attachments for Further Information.

Thanks a lot

Kumar

I did this for you in the Mac group.

Hai But want to Satisfy all the Above conditions in that Column that i send ,As i am New to Vb Macros i cant Understand the Code Fully

[VBA]

Public Sub ProcessData()
Dim LastRow As Long
Dim iRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

iRow = Application.Match(.Cells(i, "A").Value, .Columns(1), 0)
If iRow <> i Then

.Cells(iRow, "C").Value = .Cells(iRow, "C").Value + .Cells(i, "C").Value
.Cells(iRow, "E").Value = .Cells(iRow, "E").Value + .Cells(i, "E").Value
.Cells(iRow, "G").Value = .Cells(iRow, "G").Value + .Cells(i, "G").Value
.Cells(iRow, "I").Value = .Cells(iRow, "I").Value + .Cells(i, "I").Value
.Cells(iRow, "K").Value = .Cells(iRow, "K").Value + .Cells(i, "K").Value
.Cells(iRow, "M").Value = .Cells(iRow, "M").Value + .Cells(i, "M").Value
.Rows(i).Delete
End If
Next i
End With

End Sub
[/vba]

and no, you can't have my email, this is a community forum.

this Code Shows Error in the
iRow = Application.Match(.Cells(i, "A").value, .Columns(1), 0) . In this Line. As i am New to VB Macros , I cant Know the Reason. Please Help me

Howdy. Is this specific to the Mac?

Normally when a line errors out, it will give you the option of getting "help", what did it suggest to you?

Secondly, while you took the trouble to provide some data within a word doc, why not post your data as an Excel workbook? We should not have to re construct your workbook for you.

Look at the WorkSheet. In the First Sheet(Occupancy Consultant Report) , row 10,11 is the Same Name with Different Values . I want to Remove the Duplicate Name after adding the values . I want to Add the % Change Values . Can Anyone Modify that One and Send you .

Thanks

Quote:

---

Originally Posted by Aussiebear

Normally when a line errors out, it will give you the option of getting "help", what did it suggest to you?

Secondly, while you took the trouble to provide some data within a word doc, why not post your data as an Excel workbook? We should not have to re construct your workbook for you.

---

Its Related To VBA Macros

Quote:

---

Originally Posted by shades

Howdy. Is this specific to the Mac?

---

You want us to add the values from row 11 to row 10????

In the Excel Sheet I want to Remove the Duplicate Name after Adding the Values .

Eg: In Row 10 and 11 the Names is Same . I want the Output As follows
Column3 Column5 Column7 Column 9 COlumn 11 Column13
101 Cal: 77+-=77 103+-=103 103+-=103 -+-=- 103+-=103 N/a+N/a/2 =N/A

Please Help me

Quote:

---

Originally Posted by Aussiebear

You want us to add the values from row 11 to row 10????

---

Well the data sheet I opened has no data in row 11.

Please Check the Row 10 and 11 in Occupancy Consultacy Sheet(Sheet1) .

Quote:

---

Originally Posted by Aussiebear

Well the data sheet I opened has no data in row 11.

---

Then I will move it to the general Excel forum. Since this forum is for Mac specific VBA.

I have for a second time. And I reiterate - there is no data in row 11 to add.

HAi in tenth row , value is 77 and in the 11 th row , the value is - . I want the output as 77+-=77 . If Suppose in the 11 th row , the value is 3 means , i want the Output as 77+3=80 . I want the Same output for the the Remaining 5,7,9,11 and 13 columns . In 13 th column , there is the % Value . So that i want to add the two Values and its Divided by 2 . ie . if 10 th row contains 20 % and 11 th row Contains 10 % Means , i want the Output as (20+10)/2= 15%

Thanks

Quote:

---

Originally Posted by Aussiebear

I have for a second time. And I reiterate - there is no data in row 11 to add.

---

Greetings Kumar,

It was unclear to me at least if you are talking about strings (like: "Vb30") in one cell, or if these are side-by-side; as well as I'm not sure if you are talking about the "second row" in the same column?

Could you maybe attach an example workbook where what you are aiming at would be easier to see?

Thanks so much,

Mark

See the Sample Attachement . It Deletes the Duplicate Name After Adding the Values . The Same Thing want to Appply in the Original Excel Sheet .

Quote:

---

Originally Posted by GTO

Greetings Kumar,

It was unclear to me at least if you are talking about strings (like: "Vb30") in one cell, or if these are side-by-side; as well as I'm not sure if you are talking about the "second row" in the same column?

Could you maybe attach an example workbook where what you are aiming at would be easier to see?

Thanks so much,

Mark

---

Look at the 10 th and 11 th row in Occupancy Consultant Sheet . the Name are Same (101 California ..) . I want to Remove the Duplicate Value in the Excel Sheet . Also i want to Add the Two Values in Column 3,5,7,9,11,13 .

For Eg : In Column 3 , I want the Value as 77+- = 77
In Column 5 , I want the Value as 103+- = 103
In Column 7 , I want the Value as 103+- = 103
In Column 9 , I want the Value as - + - = -
In Column 11 , I want the Value as 103+- = 103
In Column 13 , I want the Value as (N/A+N/A)/2 = N/a

The above posts will appears to be out of sync with normal conversation. This is a result of having to merge two threads on the same topic.

Kumar, please do not start another thread on the same topic.

Greetings again,

In just glancing at the code, it is very easy to see that you have put in a lot of work on this. We would like to help, but I'm afraid we may be having a slight communication gap. Also, and I mean this in the kindest way, your naming conventions (ie - the names you assign to variables) are a bit hard to follow, due at minimum, to their length. In one line of code, I believe it was at column (that is character in the line of code) 412 or so!

Anyways, I think this is part of the problem, at least I know my poor ol' eyes nearly went crossed!

Now let's at least break up your question a bit, and see if we can make better progress.

Looking at row 10 and 11, I see that the vendor name is indeed the same, to wit: "101 California Venture". You state you want to remove the "duplicate value" in the "Excel sheet".

You then state you want the values (resultant) like:
"For Eg : In Column 3 , I want the Value as 77+- = 77 " etc...

Now please help us help you. I know its frustrating, but carefully see if this would better describe what you want. (If not, please carefully re-explain, and/or correct where I go awry.)

I think that what you want so far is this:

1. 'Build' the sheet (Occupancy Consultant Automated), by running the code you have so far. That is, by running the code you have so far to put all the data in.
2. Then, you want to search for duplicate names in the 'Vendor' column.
3. If a duplicate name is found:
   1. add the values in each category, from ea of the 'duplicate records' (so-to-speak), such as "Actual YTD".
   2. Place the total for each category in just one of the duplicate records; let's say the first record.
4. Then delete the entire row(s) to eliminate the unnecessary duplicate records.

Now if I got that all correct, I would say that I'm not so sure about 'adding' N/A's.... But let's not even worry about that yet. Please advise if I have understood what the goal is thus far.

Thanks,

Mark

I have helped with this before havent i, this is the code i gave you...

[vba]Sub Cons()
Dim rCell As Range, LastRow As Integer, Tot As Integer
Dim r As Long, N As Long, MyRange As Range
Dim V As Variant

N = 0
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set MyRange = Range("A10:A" & LastRow)


On Error Resume Next
For Each rCell In MyRange.Cells

Tot = WorksheetFunction.CountIf(Range("A:A"), rCell.Value)

If Tot > 1 Then
rCell.Offset(, 2).Value = WorksheetFunction.SumIf(Range("A:A"), rCell.Value, Range("C:C")) '+ Tot
rCell.Offset(, 4).Value = WorksheetFunction.SumIf(Range("A:A"), rCell.Value, Range("E:E")) '+ Tot
rCell.Offset(, 6).Value = WorksheetFunction.SumIf(Range("A:A"), rCell.Value, Range("G:G")) '+ Tot
rCell.Offset(, 8).Value = WorksheetFunction.SumIf(Range("A:A"), rCell.Value, Range("I:I")) '+ Tot
rCell.Offset(, 10).Value = WorksheetFunction.SumIf(Range("A:A"), rCell.Value, Range("K:K")) '+ Tot
rCell.Offset(, 12).Value = WorksheetFunction.SumIf(Range("A:A"), rCell.Value, Range("M:M")) / 2
End If
Next


For r = MyRange.Rows.Count To 1 Step -1
V = MyRange.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(MyRange.Columns(1), V) > 1 Then
MyRange.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

End Sub[/vba]