3
0
I have a set of data that is currently 39,000 rows and 27 columns. The first column is the ID number. The subsequent columns correspond to elections, with the date of the election in row 1 (cells B1
-AA1
). The rest of the cells are filled with a letter that corresponds to voting method (or null if did not vote). I need to rearrange this table so that there are a total of three columns: ID, Date, and Voting Method. For example:
Current table:
ID 05/2005 11/2005 03/2006 (etc., for 27 total columns)
2345 P V
3789 A V
4321 V A V
7890 I
And I need it to look like this:
ID Date Voting Method
2345 05/2005 P
2345 11/2005 V
3789 11/2005 A
3789 03/2006 V
4321 05/2005 V
4321 11/2005 A
4321 03/2006 V
7890 11/2005 I
I think this is going to require VBA script, and I've tried piecing together bits of script I've found online (because I've never learned VBA), but I can't seem to get it to work quite right. Perhaps this function already exists in Excel?
Here's the script I'm working with so far:
Sub NewLayout()
For i = 2 To Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
For j = 0 To 26
If Cells(i, 3 + j) <> vbNullString Then
intCount = intCount + 1
Cells(i, 1).Copy Destination:=Cells(intCount, 10)
Cells(i, 2).Copy Destination:=Cells(intCount, 11)
[I think this one is wrong. It needs to copy the column name,
not the cell value, if there is a cell value.]
Cells(i, 3 + j).Copy Destination:=Cells(intCount, 12)
End If
Next j
Next i
End Sub
If anyone has any suggestions, I'd appreciate it!
I've formatted your post a little bit to make it more readable. With this said, please show your work: we're not a script-writing service. – Doktoro Reichard – 2014-07-19T01:32:49.863
Thanks for cleaning up the formatting! I wasn't sure how to get it to display correctly. And I certainly don't expect you to write the script for me (if a script is the correct way to go? Maybe this function already exists in Excel?) I'll post the script I'm working with in my original post (I can't seem to get the formatting right in the comment section, either). – Haynes – 2014-07-19T02:05:27.880
Re-arranging cells in Excel is tricky business--Often, you cannot do it because Excel is in "Read-Only" mode while the script is running, to prevent issues such as a poorly written script trying to update a cell, which triggers another cell to update, which triggers your script to update, which triggers another cell to update, which triggers your script again, and so on, looping forever and ever. Could you possibly export your data in to a database product, like Access? – C. M. – 2014-07-19T14:35:11.587
This could be done without VBA, but the way I see it would be convoluted (because it would be done with a process similar to one I did on a previous answer, which I admit it is convoluted). I will try to make a concrete answer using just the Excel functions later on, but a VBA script would be "easier" to implement.
– Doktoro Reichard – 2014-07-19T19:50:32.067Hi all, Thanks so much for your answers! I got the data reformatted using Python script (hadn't even considered that) that a friend wrote. Glad to know there are multiple ways to get the same result! – Haynes – 2014-08-08T15:56:24.020