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
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:
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!