Windows 7 Do I need a macro or something else?

N

NoITinmyday

Joined
Aug 31, 2011
Messages
10
Reaction score
0
Hello All
I have spent the last two days trawling forums trying to work out what I need to do and am none the wiser! The most complicated thing I can do in Excel is a straight forward VLOOKUP - that is the extent of my knowledge!
I have a spreadsheet of 16k rows and at least 17 columns. There are duplicate rows throughout the document that I would like to total the columns with numerical values and concatenate the data in column B
Column A - this is where there are some duplicate entries
Column B - these need to be amalgamated where there is a duplicate
Columns C & D will be the same for duplicates
Columns E onwards need to be totalled
What I would like to see is one line per company (as highlighted in the attached) in a seperate spreadsheet
Can someone please help me?
Thank you
 

Attachments

NolTinmyday,

Here is a macro to identify duplicate rows. In order to use it, I add a blank column to the right of the suspect column. This will receive the indicator.
Next, sort your spreadsheet by the suspect column. Select the first row in the suspect column- apply focus, then run the macro. Each duplicate row will be indicated in the added column, you can sort that column, delete the duplicates, and leave the unique rows.
I use the macro a lot. Usually I add a column with numbers from 1 to x so I retain my original sort order.

Here is the macro:
Sub duplicateRows()

'
' NOTE: You must select the first cell in the column and
' make sure that the column is sorted before running this macro
'
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
offsetcount = 1
Do While ActiveCell <> ""
If FirstItem = SecondItem Then
ActiveCell.Offset(offsetcount, 1).Value = "Duplicate"
ActiveCell.Offset(offsetcount, 0).Interior.Color = RGB(255, 0, 0)


offsetcount = offsetcount + 1
SecondItem = ActiveCell.Offset(offsetcount, 0).Value
Else
ActiveCell.Offset(offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
offsetcount = 1
End If
Loop
ScreenUpdating = True


End Sub

Stoneboysteve
 
Hello Stoneboysteve
Thank you for the macro - it works a treat at identifying the duplicates. I am still stuck, however, in how to:
Column B - these need to be amalgamated where there is a duplicate
Columns C & D will be the same for duplicates
Columns E onwards need to be totalled
I have been playing with the spreadsheet after using your macro but there is still a lot of manual work to do on it. Also there are some companies that have up to 5 rows.
What I would like to end up with is one record per company in column A, showing all managers in column B and then the totals of the numbers in columns E onwards
Does that make sense?
Thank you for your help so far
NoITinmyday
 
NolTinmyday,

I used subtotal function to get totals from Column E onward. I ran it again to count the mangers. With a little more time a for next or case statement could look at the numbers and concatenate the manager names. I cant do it tonight, but maybe if it is worth looking at I can see about later.

Another idea I had, but is purely VB, is to create a variable for each column. Using a for next loop look through the list of companies. In the manager case a series of variables can grab the manager names and concatenate them when the company changes. Same for the values that sum.

The measure of value of using VB is the task itself. Is this a one time assignment or a task that is repeated often. If it is a single action, using subtotals and a little manual work may be best. If it is something repeated then automate the tasks.

I use Office Pro Plus 2010 by the way. I have backward compatible options. I do not know how to attach the subtotal example:(.

Stoneboysteve
 
As always thanks for your response Stoneboysteve.
This is a task I will have to do at least once every month for 16k rows of data. I am as clueless with VB as I am with macros. I have now decided to buy myself the idiots guide to VB and macros to see if I can self teach before I have to work with this spreadsheet again.
 
NolTinmyday,

No need to buy a book. Take small steps and start by recording individual tasks with the macro recorder. It will write the code for you. I did a similar analysis on a complex spreadsheet (the one I created the duplicate row macro for) where I needed to concatenate cell content.
Since this forum is about learning and sharing, would you be willing to continue the thread and work together on a solution? We might even drag in others that can help along the way.
I figured out how to post an attachment thanks to your other thread, I have attached the subtotal result from yesterday.

Stoneboysteve
 

Attachments

Thanks for this - this is the main one to nail now (you will see my response to the other thread)
I used yoru duplicate macro yesterday on apiece of work I had to do and it worked perfectly. What I need to work on now is that once the macro has identified the duplicates it concatenates the names, totals the columns and then delets the rcords of 2 or more just to leave the one line per company with the amalgamated data. Would I need to edit the macro you built already - and if so this is where I need to learn to save the macro, run it, and then record to your macro to do the other bits.
 
NolTinmyday,

I have finals this week. I do have a few ideas on how to approach the problem. While I am working on my schoolwork can you describe the number of possible duplicates for each iteration (the number of managers possible for a company)? The reason I ask is in order to concatenate and sum the data we need to create variables. If it cannot be determined we can use and release them.

The answer to your question about macros is in 2003: Tools>Macros>Visual Basic Editor (alt F11) or Developer>Macros in 2007 and 2010.

Stoneboysetve
 
Hi - Good Luck in your finals.
I think I may have complicated the process by just using excel the way I know! What I am ideally looking for is just one manager name. The manager name to remain would be the manager who has the latest data in the year. So basically if there are 4 records for Company 1 with 4 individual managers. Manager 1 has sales figures in Jan and Feb, Manager 2 in March, Manager 3 in April, May and June and Manager 4 in July and August, then Manager 4 would be the name I would want to see in column B but with the total figures of all 4 company records for columns E onwards. Does that make sense - and is that easier than concatenating?
 
Thanks for the luck wish. I hope it works.

Are there sales amounts in every month? It may complicate things if there are too many logic statements to determine the last manager for each company. For example, if a company has the four managers you show in your last post, but the last two months are not reported, the script would have to 'roll back' in months to find the value it needs to determine the last active manager.

In the first Excel spreadsheet you posted the managers were listed in order. That may simplify the determination of the last manager if it is consistent.

Stoneboysteve
 
I could sort the data by company and then by values in the later months backwards so where there are 4 records for one company then the manager on the top one is the manager name to use and then total the columns - see attached
Would that work?
 
Back
Top