Rearranging large amounts of data

If the data forms a nice rectangle, it's fairly easy to do this with some formulas and a copy/paste special values. However, since I don't know if that's the case, it's easiest to do this with a macro. I made a couple of assumptions, though.
Assumption 1:
the company names will be held in cells A1, E1, I1, etc., either by merged cells, or center across selection, or some other method.
Assumption 2: you don't mind copy/pasting the headers h1,h2,h3,h4 into the final positions (this is a one time thing, right?)

This macro will take the data from the currently active sheet, and place it into a newly added sheet in the same workbook.

Please Login or Register  to view this content.

cjo,

Firstly thank you very much for the reply & effort you put into that post.

I should have mentioned that the information is not in a nice rectangle. The first column is a date and this is not uniform across the companies.

I had hoped there was an easy way to do this on Excel, my experience with macros is very limited. I should have said in the original post that there is actually 13 columns per company (12 + company name/H0). Is it easy to modify the macro to include this?

Re the assumptions:

1- The company names will be held in the first row. A will be a2-a?, B will be a?-a?? etc.

2- Actually a fourteen time thing as it didn't all fit into one spreadsheet. But you're correct I certainly don't mind doing that.


Thanks again for the help!

Changing the number of columns on the final spreadsheet isn't hard.

First, though, what I meant by "Nice Rectangle" is each company has the same number of rows. That is fairly simple thing to do with formulas. If each company has a different number of rows underneath them, then I can't think of a formula off hand that wouldn't require a clean up using an autofilter.

As to the assumptions, I think we've got some tenses confused. In your current sheet, can the company names can be seen in cells A1, M1, Y1, etc?
Also, I'm just leaving the top row blank for the headers. So long as your headers h1, h2, etc don't change value, you should only have to copy/paste it over once.

As to some new information you just provided, is your data is currently spread across multiple worksheets? If so, it could be possible to save you some effort. Do the worksheets have some sort of similarity in the names, or some other way to give a good positive identification that they should be included in this cleanup/merge?

Anyway, here's the revised (for now) code.

Please Login or Register  to view this content.

To use the code: press Alt-F11. this will bring up the VBA code editor. Go to Insert-module and copy/paste this code into it. You can then either run the code from the excel sheet from the Developer tab-Macro, or put your cursor somewhere in the middle of the code and press f5.

Originally Posted by cjo

First, though, what I meant by "Nice Rectangle" is each company has the same number of rows. That is fairly simple thing to do with formulas. If each company has a different number of rows underneath them, then I can't think of a formula off hand that wouldn't require a clean up using an autofilter.

Sorry I should have been clearer. They have different numbers of rows.

As to the assumptions, I think we've got some tenses confused. In your current sheet, can the company names can be seen in cells A1, M1, Y1, etc?
Also, I'm just leaving the top row blank for the headers. So long as your headers h1, h2, etc don't change value, you should only have to copy/paste it over once.

Close they are currently in B1, O1, AB1 etc. - but they are also in the first column of each table(A3-,N3-, ec.) so perhaps for ease I should remove this first row?

As to some new information you just provided, is your data is currently spread across multiple worksheets? If so, it could be possible to save you some effort. Do the worksheets have some sort of similarity in the names, or some other way to give a good positive identification that they should be included in this cleanup/merge?

They are named the same except numbered 1-14 at the end of the filename, but as I mentioned this is a small number so I don't mind doing them singly.


Tested the macro and it seems very close to doing what I want, although there seems to be an error. Company A's data looks perfect, but for B's the data all shifts one square to the right (and column M goes to column B). After this company there is a string of numbers in the B column, before the next company which is further right-shifted, then string in B & C, then next company further right-shifted etc.
Wish I could troubleshoot this myself but the code looks completely alien to me. Any ideas?


Again massive thanks for the help so far!

Ok,now that one piece of confusion is cleared up (where the company names are held), and I think I've got one other piece of confusion cleared up (the number of columns per item in the original dataset is 13, not 12 as I had on the last revision), we can now get down to business.

I'm going to ask you to change something in the following code. You said that all the sheetnames are the same, except that they are followed by 1, 2, ... ,14. There's a line

Please Login or Register  to view this content.

where you should replace the "TheCommonName*" with "Data*" or "Companies*" (notice the star) with whatever is your naming convention -- provided that all worksheets are in the same workbook. If they aren't, you'll need to remove that line, this line

Please Login or Register  to view this content.

and these two lines at the end.

Please Login or Register  to view this content.

(make sure that the 'end if' directly above the Next wsInput is removed.
and then, right above

Please Login or Register  to view this content.

put back in

Please Login or Register  to view this content.

any way, hopefully this won't be a problem, so try this:

Please Login or Register  to view this content.

cjo,

You're an absolute lifesaver thank you so much. Works perfectly!

I'm so happy about this- thanks again you've made my day!