Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Help needed in trying to create a Macro? to cut, and concatenate rows in Excel


(!)

Icke_F's Avatar
Icke_F Icke_F is offline
Member with 11 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Intermediate
17-Apr-2012, 02:40 PM #1
Help needed in trying to create a Macro? to cut, and concatenate rows in Excel
Hello, all. I'm looking to get assistance in creating a Macro to help format and move data in an Excel file: I have an Excel file with over 12,800+ rows and over 20+ columns, of data from about 192 companies (spanning ~16 years - 1994 - 2010 for each company).

Trouble is that there are several entries per year for each company.

What I want to do is to have every year, per company, be on one row: so I want to move the data for the same years, which come on different rows (in the data file), to the end of the row with the very first data for that year, which would make one long row (eg. if 5 rows of year 1999, make into 1 row of year 1999 by concatenation, and so on etc). And do this for each year for all the 192 companies.

Doing this manually is just inefficient, time consuming, and possibly prone to mistakes.

I've been told that a macro can do this, but as I'm a novice Excel user, don't know how to go about doing that. Even customer service & technical support at Microsoft weren't able to help me very much: they directed me here.

I have screen shots (of a simple spreadsheet I created) if anyone needs further clarification.

In the pictures, I only did the first company. But this is to happen for each year for all of the companies.

Please help. Thanks.
Attached Thumbnails
Help needed in trying to create a Macro? to cut, and concatenate rows in Excel-original-data-picture-1.jpg   Help needed in trying to create a Macro? to cut, and concatenate rows in Excel-sorting-company-date-pic-1.jpg   Help needed in trying to create a Macro? to cut, and concatenate rows in Excel-sorting-company-date-pic-2.jpg   Help needed in trying to create a Macro? to cut, and concatenate rows in Excel-sorting-company-date-pic-3.jpg   Help needed in trying to create a Macro? to cut, and concatenate rows in Excel-sorting-company-date-pic-4.jpg  

bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
17-Apr-2012, 03:05 PM #2
Is the whole thing sorted by "Company"?
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
17-Apr-2012, 03:57 PM #3
Maybe this will get you close (if sorted by [i] Company [ii] year).

Sub test()
Application.ScreenUpdating = False
Range("G1").CurrentRegion.ClearContents
Range("A1:E1").Copy Range("G1")
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For Each Cell In Range("B2:B" & LastRow)
If Cell <> Cell.Offset(-1) Then
Cell.Offset(, -1).Resize(, 5).Copy Range("G" & Rows.Count).End(xlUp).Offset(1)
Else
If Cell.Offset(, -1) <> Cell.Offset(-1, -1) Then
Cell.Offset(, -1).Resize(, 5).Copy Range("G" & Rows.Count).End(xlUp).Offset(1)
Else
x = Range("G" & Rows.Count).End(xlUp).Row
y = Cells(x, 7).End(xlToRight).Column + 1
Cell.Offset(, -1).Resize(, 5).Copy Cells(x, y)
End If
End If
Next Cell
Application.ScreenUpdating = True
End Sub


Quote:
Microsoft ... directed me here
Attached Files
File Type: xls summariser.xls (22.0 KB, 57 views)
Icke_F's Avatar
Icke_F Icke_F is offline
Member with 11 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Intermediate
17-Apr-2012, 04:54 PM #4
Thank you for your quick reply! Yes. From the original data, I do a custom sort: first by Company, and then by date (in column B in the attached picture).

Attached is a screenshot of the first several rows and columns.

I did a custom sort (first on "CUSIP" number - column G - which pretty much sorts by company (CUSIP is basically a company identification number), then I sort by "Data Date" - column B).

You can see that there are several year entries for each company (eg. ABBOTT Labs have several entries for 1994, 1995, 1996, etc), I wanted to to make it so that there is just one row entry for 1994, 1995, 1996).

With that being said, will the code you gave me still do what I'm trying to do? And where would I enter it in Excel (I've never used Macros before).
Attached Thumbnails
Help needed in trying to create a Macro? to cut, and concatenate rows in Excel-original-data-screenshot.jpg  
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
17-Apr-2012, 05:00 PM #5
Your real data is way different from your initial example.

How many columns does your real data have?

Edit: is year in another (not shown) column? If not it'll need "extracting from column B.

Last edited by bomb #21; 17-Apr-2012 at 05:20 PM..
Icke_F's Avatar
Icke_F Icke_F is offline
Member with 11 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Intermediate
17-Apr-2012, 07:22 PM #6
True, it is. I did a simplified version, because I thought it was a bit overwhelming. The original data has 12,886 rows and 30 columns, and is a ~25MB file.

No: the only column I'm taking the year data from is column B.
Icke_F's Avatar
Icke_F Icke_F is offline
Member with 11 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Intermediate
17-Apr-2012, 07:33 PM #7
I've just tried out the macro. Thanks so much - that's what I wanted to have happen. However, Is there any way to work with the data instead of making a copy?

As in manipulating the original data itself and rearranging it (without a second copy of the data)? Or if not possible, moving the rearranged data to a new spreadsheet (so it doesn't further clutter up the original spreadsheet - with all those columns and rows, the spreadsheet would get even uglier than it is now).
Garf13LD's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
17-Apr-2012, 10:36 PM #8
Attached Files
File Type: xls summariser2.xls (38.0 KB, 46 views)
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
18-Apr-2012, 02:46 AM #9
Quote:
Originally Posted by Icke_F View Post
I've just tried out the macro. Thanks so much - that's what I wanted to have happen. However, Is there any way to work with the data instead of making a copy?

As in manipulating the original data itself and rearranging it (without a second copy of the data)? Or if not possible, moving the rearranged data to a new spreadsheet (so it doesn't further clutter up the original spreadsheet - with all those columns and rows, the spreadsheet would get even uglier than it is now).
If you (i) make a copy of the worksheet (ii) process the copy data along the lines I started (iii) delete the original data (columns) from the copy worksheet, I believe you'll get what you need.

But some basics need setting up first.

Here's code to set up a Year column:

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("B1").EntireColumn.Insert
Range("B1") = "Y_Date"
Range("B2:B" & LastRow).FormulaR1C1 = "=LEFT(RC[1],4)*1"
Range("B2:B" & LastRow).Value = Range("B2:B" & LastRow).Value


It's in the attached, you run it by pressing CTRL+Shift+M. Do that & note the effect on Sheet1.

Then copy your worksheet into this test file, and run it again with your worksheet active.

Then confirm you get an inserted year column B with the values derived from what used to be column B.
Attached Files
File Type: xls summariser.xls (22.5 KB, 42 views)
Icke_F's Avatar
Icke_F Icke_F is offline
Member with 11 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Intermediate
18-Apr-2012, 05:56 AM #10
Thank you very much. It works just like I'd like.

Will this macro work with up to 30 columns (as there are 30 columns in the original spreadsheet)?
Icke_F's Avatar
Icke_F Icke_F is offline
Member with 11 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Intermediate
18-Apr-2012, 05:57 AM #11
Thanks. I'll run it on the original data and see what happens.
Icke_F's Avatar
Icke_F Icke_F is offline
Member with 11 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Intermediate
19-Apr-2012, 03:49 AM #12
Issue with using the code in original data file
Okay, I tried to create a new macro (in the original excel spreadsheet) and then I copy and paste the macro into the VB window, but when I click on "run" it shows an error.

I've got screenshots. Then, I check back at the data, and everything is gone.

When I try the summarizer, it works, but how to do take the code for that and use it in the original data spreadsheet?
Attached Thumbnails
Help needed in trying to create a Macro? to cut, and concatenate rows in Excel-pic-1.jpg   Help needed in trying to create a Macro? to cut, and concatenate rows in Excel-pic-2.jpg  
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
19-Apr-2012, 06:27 AM #13
I couldn't say what the error's about. I've no idea what "I check back at the data, and everything is gone" means. Essentially you shouldn't be using that on the original data spreadsheet because it's not designed for the original data spreadsheet layout (that's why I commented out most of the code).

Quote:
how to do take the code for that and use it in the original data spreadsheet?
You don't need to. If you open the summariser wb, then the original data spreadsheet, then press ALT+F8 for the Run Macro dialog, you can then run "summariser.xls!test" and it will "act on" the active wb, i.e. the original data spreadsheet. But again, the code's not designed for that layout. A good idea would've been to stick to the original plan ("I'll run it on the original data and see what happens") when "it" was simply:

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("B1").EntireColumn.Insert
Range("B1") = "Y_Date"
Range("B2:B" & LastRow).FormulaR1C1 = "=LEFT(RC[1],4)*1"
Range("B2:B" & LastRow).Value = Range("B2:B" & LastRow).Value

Last edited by bomb #21; 19-Apr-2012 at 06:36 AM..
Icke_F's Avatar
Icke_F Icke_F is offline
Member with 11 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Intermediate
19-Apr-2012, 10:18 AM #14
Alright. I've followed the original plan to run the Macro while the original data as the active worksheet is opened (using the summarizer that creates a year column). It added a new column,Y_Date, to the spreadsheet. However, instead of showing a 4 number year (as in the test spreadsheet I sent you previously), it the every row in that column shows a formula, instead.

See attached picture.

I'm not sure why that is. I'd send the original data, but it's too big at 25MB to attach.
Attached Thumbnails
Help needed in trying to create a Macro? to cut, and concatenate rows in Excel-screenshot.jpg  
Icke_F's Avatar
Icke_F Icke_F is offline
Member with 11 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Intermediate
19-Apr-2012, 10:40 AM #15
Okay. Just to give you part of the original data, I copied the first 20 rows of data into a new spreadsheet so that I could attach it here.

Perhaps, through that, you'll better understand where the problem is coming from. Just want to remind you, though, that there are 12,866 rows.

I hope the attachment helps.
Attached Files
File Type: xlsx Test Data.xlsx (12.0 KB, 62 views)
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
excel, macro, merge

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑

Content Relevant URLs by vBSEO 3.3.2