Search:

# Help with macro

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

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
Rows(Row).Delete
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
process?

For example:

Duplicate 1 0.987 1.863
Duplicate 1 0.876

Any help would be appreciated.

Share:

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 +
Cells(Row-1,2).Value
End If
Next Row

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

I have a workbook with two worksheets.

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

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.

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?

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

A123
B234
C323
D567
etc...

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
etc...

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.

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.

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.