Macro to calculate, merge, and delete

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

 

[Jacob Skaria]

Try Pivot Table

http://contextures.com/xlPivot10.html

If this post helps click Yes

 

[Lost in Excel]

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.

 

[Fred Smith]

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

 

[Lost in Excel]

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.

 

[Otto Moehrbach]

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

 

[Lost in Excel]

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.

 

[Otto Moehrbach]

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

 

[Lost in Excel]

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?

 

[Lost in Excel]

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.

 

[Otto Moehrbach]

Glad I was able to help. Otto

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.