[Icke_F]

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.

[bomb #21]

Is the whole thing sorted by "Company"?

[bomb #21]

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

Microsoft ... directed me here

[Icke_F]

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

[bomb #21]

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.

[Icke_F]

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]

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]

[bomb #21]

Originally Posted by Icke_F

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.

[Icke_F]

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]

Thanks. I'll run it on the original data and see what happens.

[Icke_F]

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?

[bomb #21]

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

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

[Icke_F]

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.

[Icke_F]

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.