Macro to calculate, merge, and delete

  • Thread starter Thread starter Lost in Excel
  • Start date Start date
L

Lost in Excel

Hello, I need help creating a macro, and can't figure out how it should be
written.

I want the macro to look for duplicate names in column B, add figures in
columns N to X, and merge information to one line.

The names only appear twice in my report, but I need to merge the YTD info
(columns N to X).

Doing this manually is taking forever.

Any assistance would be appreciated. Thanks.
 
Not looking to create pivot table, as I need data on this merged worksheet
consolidated.

Thanks anyway, but looking for a macro. As I am totalling, pasting special,
and deleting the duplicate lines, currently. Would prefer a macro for this
tedious task.
 
The best way to create a macro is to record one. The next time you do it
manually, turn on the macro recorder. Turn it off when you're done. Now you
have a good start on your macro. It will take some editing, but it's a lot
better than creating it from scratch.

No one's going to write a full procedure for you, but you'll certainly get
help with particular areas. Post to the .proramming group when you have
specific questions.

Regards,
Fred
 
I attempted recording, but need assistance with changing cell (D4), or
"ActiveCell" to search for a duplicate record, and insert line below.

I will research your site and see what I can uncover for the specifice
command I am looking for.

Thanks just the same.
 
I'll try to help you with this. You say you have names in Column B and
figures/numbers in Columns N:X. What's in the other columns and what do you
want to happen to that data when Columns B & N:X are merged to one row? In
what row do the names start? When the 2 rows are merged into one row, where
do you want that one row placed? Do the names ALWAYS appear twice and NEVER
just once? HTH Otto
 
Hello Otto, Thanks for the assistance. The spreadsheet contains a list of
names. A person's name will appear either once or twice, as not all names
have two entries. Top entry has all columns filled, second entry only has
info in N:X. I want to add columns N:X for the double entry, replace N:X in
first entry to be combined total, and then delete the second entry leaving
the name only listed once.
Here were the steps I took: 1. scrolled through names, if listed twice,
inserted a row, then put in summation formular for addtion of the two lines.
copied the totals of columns N:X, and "pasted special" into first column. Now
I have the one line with all information I need, then I deleted second line
and total line. Continued scrolling through document for another duplicate
name, and repeated the steps. It was cumberson, and I am sure there is a
macro I can write to say: if value of D2=D3, then, (do steps above), else
goto D3, (repeat) then if value D3=D4, etc.

I am sure if written correctly, the excel file and do this in an instance,
but my programming skills just aren't there. I get it conceptually, but can't
write it technically. That is were I need some instruction.

Any suggestions you can supply would be appreciated.
 
Try this macro. I assumed your headers are in Row 1 and your data starts in
Row 2. I also assumed the names are in Column A. HTH Otto
Sub MergeRows()
Dim rFirst As Range, rSecond As Range
Dim rColA As Range, c As Long
Dim d As Long
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For c = rColA.Count To 1 Step -1
Set rSecond = rColA(c)
If Application.CountIf(rColA, rSecond.Value) > 1 Then
Set rFirst = rColA.Find(What:=rSecond.Value)
For d = 13 To 23
rFirst.Offset(, d) = rFirst.Offset(, d) + rSecond.Offset(,
d)
Next d
rSecond.EntireRow.Delete
End If
Next c
End Sub
 
Hello Otto, my headers in Row 9, with my names starting in column D, row 10.
Therefore I changed your text in message below to "D10" instead of "A2", but
I am getting a "Run-time error '424'". object required, and if I "Debug" it
hilghlights line:
Set rFirst = ColA.Find(What:=rSecond.Value), I have checked twice, but can't
uncover a problem. Any ideas?
 
FOUND THE ERROR, I was missing the "r" before rColA

Set rFirst = ColA.Find(What:=rSecond.Value)

Set rFirst = rColA.Find(What:=rSecond.Value)

I ran the Macro, and compared this file to the one I completed. Looks Good.

THANKS FOR YOUR TIME AND EFFORT!! Going to try to take a VB Class.
 
Glad I was able to help. Otto
Lost in Excel said:
FOUND THE ERROR, I was missing the "r" before rColA

Set rFirst = ColA.Find(What:=rSecond.Value)

Set rFirst = rColA.Find(What:=rSecond.Value)

I ran the Macro, and compared this file to the one I completed. Looks
Good.

THANKS FOR YOUR TIME AND EFFORT!! Going to try to take a VB Class.
Click to expand...
 
Back
Top