MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help with macro

  Asked By: Rainhard    Date: Mar 14    Category: MS Office    Views: 870

The source code from the tutorial is:

Sub RemoveDuplicates()

Cells.Sort Key1:=Range("A1")
totalrows = ActiveSheet.UsedRange.Rows.Count

For Row = totalrows To 2 Step -1
If Cells(Row, 1).Value = Cells(Row - 1, 1).Value Then
End If
Next Row
End Sub

Question: If the two cells are duplicates, rather than deleting the
row that is a duplicate, how can I add their values that are in
adjacent cells and put the value in the next cell and repeat the

For example:

Duplicate 1 0.987 1.863
Duplicate 1 0.876

Any help would be appreciated.



7 Answers Found

Answer #1    Answered By: Varun Mehta     Answered On: Mar 14

Try this:

For row  = totalrows To 2 Step -1
If Cells(Row, 1).Value = Cells(Row - 1, 1).Value Then
Cells(Row-1,3).Value = Cells(Row,2).Value +
End If
Next Row

Answer #2    Answered By: Vidhya Iyer     Answered On: Mar 14

The macro  worked exactly as you posted............

Answer #3    Answered By: Alvin Nguyen     Answered On: Mar 14

I have a workbook with two worksheets.

In the first worksheet, I have one column with about 4000 unique

In the second worksheet, I have another column with about 40,000
entries. For each cell  in the 1st column, the corresponding cell has
row  of data.

I am looking for a macro  to match the entries from the column from
the first worksheet with the first column from the second worksheet.

If there is a match, then copy the entire row from the second
worksheet to a new worksheet.

Answer #4    Answered By: Jackson Bouchard     Answered On: Mar 14

I'd need more detail to be sure, but this sounds like a job for some
VLOOKUP formulae rather than VBA. Can you give us more detail of how the
dat is structured? And of the 40,000 entries on the second sheet, are
the other 36,000 duplicates  of the 4,000 on the first, or are they just
other non-matches?

Answer #5    Answered By: Isaac Williams     Answered On: Mar 14

For example, in worksheet 1 contains 4000 entries in one column,
there is only one column with data like this, for example:


In worksheet 2, there are about 40000 entries like the following:

A123 56.7 444 555
NMNC 76.9 345 123
D567 23.0 233 123

If a cell  in the column from the worksheet 1 matches a cell in the
first column of worksheet 2, then print that row  from the match from
worksheet2 to a new worksheet.

For example, A123(from worksheet 1) matches the cell in the first
column of worksheet 2, then print that row to worksheet 3.

Answer #6    Answered By: Luki Fischer     Answered On: Mar 14

Yes, it looks like each of your rows  in Sheet 2 is unique, with 4
columns in each. My advice would be not even to think about VBA, but to
use VLOOKUPs to read the information straight onto Sheet 1 in the 3
additional columns, in the format below for Sheet 1 B1:

=VLOOKUP (A1,Sheet2!A1:D40000,1,TRUE)

For columns C and D replace the ,1,TRUE with - respectively - ,2, TRUE
and ,3,TRUE. The select B1:D1 and drag down the length of the range to
row 4000.

Answer #7    Answered By: Lela Lynch     Answered On: Mar 14

Incidentally, I meant to say the TRUE argument will only work if the
range in Sheet 2 is alpha sorted on column A. If it isn't, replace all
the TRUEs with FALSE.

Didn't find what you were looking for? Find more on Help with macro Or get search suggestion and latest updates.