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!