PDA

View Full Version : VBA Duplicate Name Check and Calculating the Values



yapmadhu
10-24-2008, 12:08 AM
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

yapmadhu
10-29-2008, 05:51 AM
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

Bob Phillips
10-29-2008, 05:58 AM
I did this for you in the Mac group.

yapmadhu
10-29-2008, 06:02 AM
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

Bob Phillips
10-29-2008, 06:30 AM
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


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

yapmadhu
10-29-2008, 10:20 PM
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

shades
10-30-2008, 12:52 PM
Howdy. Is this specific to the Mac?

Aussiebear
10-30-2008, 08:33 PM
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.

yapmadhu
10-30-2008, 11:29 PM
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


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.

yapmadhu
10-30-2008, 11:29 PM
Its Related To VBA Macros

Howdy. Is this specific to the Mac?

Aussiebear
10-31-2008, 12:39 AM
You want us to add the values from row 11 to row 10????

yapmadhu
10-31-2008, 01:16 AM
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


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

Aussiebear
10-31-2008, 04:45 AM
Well the data sheet I opened has no data in row 11.

yapmadhu
10-31-2008, 04:49 AM
Please Check the Row 10 and 11 in Occupancy Consultacy Sheet(Sheet1) .


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

shades
10-31-2008, 07:35 AM
Then I will move it to the general Excel forum. Since this forum is for Mac specific VBA.

Aussiebear
10-31-2008, 02:49 PM
I have for a second time. And I reiterate - there is no data in row 11 to add.

yapmadhu
10-31-2008, 07:41 PM
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


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

GTO
10-31-2008, 09:34 PM
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

yapmadhu
10-31-2008, 09:36 PM
See the Sample Attachement . It Deletes the Duplicate Name After Adding the Values . The Same Thing want to Appply in the Original Excel Sheet .



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

yapmadhu
10-31-2008, 09:40 PM
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

Aussiebear
10-31-2008, 11:36 PM
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.

GTO
11-01-2008, 01:11 AM
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:


'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.
Then, you want to search for duplicate names in the 'Vendor' column.
If a duplicate name is found:
add the values in each category, from ea of the 'duplicate records' (so-to-speak), such as "Actual YTD".
Place the total for each category in just one of the duplicate records; let's say the first record.
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

georgiboy
11-01-2008, 01:20 AM
I have helped with this before havent i, this is the code i gave you...

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