Okay, Excel gurus, how do you rearray data with a horizontal orientation into a vertical orientation? Here’s what I mean: we have a data set with 100 procedure codes as rows, and 500 zip codes as columns, and the value element is procedure count for each zip. We’d like to end up with three columns: zip, procedure, and procedure count by zip. To put it another way, we’ll go from 501 columns to three, and from 101 rows to 50,001.
I know there has to be an easy solution, but I’ve wrestled with this over the years and gotten nowhere. But I repose my confidence in the bosom of the Dope!
I recently solved a problem like this using Excel, Access and ASP.
If you send me an email at the address listed in my profile, I can hook you up with the scripts I used, or run it myself.
For completeness -
Exported the Excel into a CSV file.
Imported into Access.
Ran the data from Access through an ASP script that showed it in a “properly formatted” HTML table (with one row per data set, instead of one column).
Copy and pasted the resulting HTML table back into Access (could also paste into Excel) as an actual table.
The script I wrote is pretty simple, but I’d be happy to run it for you on my local machine (requires IIS 'n stuff). Shoot me an email.
I am having some problems visualizing what you are getting at but you can flip the orientation of an array with Copy - Paste Special - Transpose option checked. Only click on the one starting cell when you Paste Special or you will get an error. From there, simple formulas may do what you want.
I haven’t figured out all the details or how to formate exactly as you want it, but look into pivot tables.
Select your data and then click on Data -> PivotTable and PivotChartReport
Select PivotTable
Click on Next
Click on Finish
You should be in a new worksheet that has a Table on the left with things like “Drop Data Items Here”, “Drop Row Fields Here” and “Drop Column Fields Here”. On the right will be a field list showing your column headings from the first worksheet (Procedure code and then your 500 zip columns).
Drop the Procedure Code item into the “Drop Row Fields Here” section.
Drop the zip code items into the “Drop Data Items Here” section.
A “Data” column should appear that defaults to “Sum of <zip>” with a total column showing the procedure/zip count from your original data table.
There are likely more elegant ways to do this, but that should get you started.
I’m just not that familiar with pivot tables to be more help at the moment. Sorry.
ZipperJJ, thanks for the offer. I’m sure your solution would do the trick, though ideally I’d find a solution within Excel, since it’s something I need to convey to several different people. I don’t think they know ASP (and I’m certain I don’t!).
Shagnasty, your transposition idea just would get you the zip codes as rows, and the procedures as columns – the same as the initial problem. The pivot table idea has the same issue – each of the 500 zip codes would show up as a different potential row or column or page element.
I am pretty sure you could do this in Access as well with just a little effort. You could concatenate every cell so that it contains all of the needed information (zip, procedure, and count), import that into Access and then break it down the way to you to re-export it back to Excel. I am pretty sure that I could do it in 10 minutes.
Shagnasty, the concatenation route is the route I have been exploring. It’s that last step – taking it into Access and then back again, that I find a little confounding.
Gigi, my coding skills are not what they could be, so you’ll have to bear with me. The current array looks like this:
…Zip1…Zip2…Zip3…Zip4…etc.
Proc1…1…2…0…9…etc.
Proc2…0…4…3…1…etc.
Proc3…5…1…6…7…etc.
etc.
And what I want is this:
Proc…Zip…No.
Proc1…Zip1…1
Proc1…Zip2…2
Proc1…Zip3…0
Proc1…Zip4…9
Proc2…Zip1…0
etc.
Dim X, Y As Integer
Dim LineCount As Integer
LineCount = 2
For X = 2 To 5
For Y = 2 To 4
Worksheets("Sheet2").Cells(LineCount, 1).Value = Worksheets("Sheet1").Cells(1, X).Value
Worksheets("Sheet2").Cells(LineCount, 2).Value = Worksheets("Sheet1").Cells(Y, 1).Value
Worksheets("Sheet2").Cells(LineCount, 3).Value = Worksheets("Sheet1").Cells(Y, X).Value
LineCount = LineCount + 1
Next Y
Next X
Dim X, Y As Integer
Dim LineCount As Integer
LineCount = 2
For X = 2 To 5
For Y = 2 To 4
Worksheets("Sheet2").Cells(LineCount, 1).Value = Worksheets("Sheet1").Cells(1, X).Value
Worksheets("Sheet2").Cells(LineCount, 2).Value = Worksheets("Sheet1").Cells(Y, 1).Value
Worksheets("Sheet2").Cells(LineCount, 3).Value = Worksheets("Sheet1").Cells(Y, X).Value
LineCount = LineCount + 1
Next Y
Next X
Arg! One more time. This has the correct size of your input data and the correct order of output columns.
Dim X, Y As Integer
Dim LineCount As Integer
LineCount = 2
For Y = 2 To 101 [COLOR=Green]'Range of procedure code data
For X = 2 To 501 'Range of Zip code data
Worksheets("Sheet2").Cells(LineCount, 1).Value = Worksheets("Sheet1").Cells(Y, 1).Value
Worksheets("Sheet2").Cells(LineCount, 2).Value = Worksheets("Sheet1").Cells(1, X).Value
Worksheets("Sheet2").Cells(LineCount, 3).Value = Worksheets("Sheet1").Cells(Y, X).Value
LineCount = LineCount + 1
Next X
Next Y[/COLOR]
Are you going to do this one time or are you going to have to do it over and over?
The reason I ask is that normalizing data is generally a pain in the a$$, and if you just need it done once it’ll probably be just as quick to do it manually.
If you need to do it over and over I’d suggest VBA.
I don’t have the time right now but if you can’t (or won’t) do it manually and you don’t come up with a satisfactory solution and you can wait till Monday (MAN that’s a lot of "if"s) - I’d be happy to help you write the code.
A way to get the procedure count row without using Macros is:
Copy the worksheet to a new file (backup the original).
Delete the Procedure Code column and the Zip Code row.
Save the worksheet as CSV. Close it in Excel.
Open the CSV file in a text editor, and replace the commas with carriage returns. Save.
Open the CSV file in Excel. Walla.
Now you just have to get the zip code and procedure code columns.
That should be pretty straight forward using a function to map the original procedure code row (and transposed zip code column):
e.g.
use mod(A1,100) for Zip Codes,
and floor(B1/500,1) or something for procedure code. Then copy those functions for 10,000 rows weeee!
Thanks, all. ThePCapeman, your solution seems like the thing I was looking for. I’m unschooled in the ways of VB, but I think I can successfully paste in your code. Unfortunately, I’m not at the office, so I can’t try it till Monday!
You are very welcome, Sal. If you’ve not done this before, allow me to offer some quickstart steps:
The code assumes your data is on a worksheet named Sheet1 within your workbook (aka file). It also assumes the workbook contains an empty, unused worksheet named Sheet2. The new table will be created on Sheet2. Edit the code and/or add a sheet as necessary. Also, adjust to For X = and For Y = values to match the actual size of your data. (500 columns really?)
In your workbook, click on Tools/Macro/Macros… Make up a Macro Name and click on Create. This will open MSVB with the start of a new module. Cut and Paste the code between the Sub and End Sub lines. You don’t need to Save or Close MSVB. Excel will take care of all that for you.
To run the macro from MSVB, click on the green Play button. Or from Excel, use Tools/Macro/Macros…/Run. Depending on the speed of your computer and the amount of data, the macro may run for a while (maybe up to a minute). This environment is not setup for speed.
It might be a good idea to try all this in a copy of your original file - just in case something goes wrong. If you want to tinker with the code, just clear all data from Sheet2 and go again. Have fun.