3

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 Jul 19 '14 at 1:32
  • 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 Jul 19 '14 at 2:05
  • 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. Jul 19 '14 at 14:35
  • 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 Jul 19 '14 at 19:50
  • Hi 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 Aug 8 '14 at 15:56
1

With VBA, assuming data is in Sheet1 starting at A1 and that Sheet2 exists:

Sub normalize()
    Dim wks1 As Worksheet, wks2 As Worksheet
    Dim iColCount As Integer, iRowCount As Integer
    Dim i As Integer, j As Integer, k As Integer

    Set wks1 = ActiveWorkbook.Sheets("Sheet1")
    Set wks2 = ActiveWorkbook.Sheets("Sheet2")
    iColCount = Application.WorksheetFunction.CountA(wks1.Range("1:1"))
    iRowCount = Application.WorksheetFunction.CountA(wks1.Range("A:A"))

    k = 1
    For i = 2 To iRowCount
        For j = 2 To iColCount
            If wks1.Cells(i, j) <> vbNullString Then
                wks1.Cells(i, 1).Copy Destination:=wks2.Cells(k, 1)
                wks1.Cells(1, j).Copy Destination:=wks2.Cells(k, 2)
                wks1.Cells(i, j).Copy Destination:=wks2.Cells(k, 3)
                k = k + 1
            End If
        Next j
    Next i
End Sub

Results in Sheet2:

2345    5/2005  P
2345    11/2005 V
3789    11/2005 A
3789    3/2006  V
4321    5/2005  V
4321    11/2005 A
4321    3/2006  V
7890    11/2005 I
0

For completeness' sake, I now show how I do this without resorting to VBA. I must warn that the following code is convoluted and it is hard to scale effectively.

Let's assume the following initial condition:

Sheet1

   |  A |    B    |    C    |    D    | …
---+----+---------+---------+---------+---
 1 | ID | 05/2005 | 11/2005 | 03/2006 |
 2 |2345|    P    |    V    |         |
 3 |3789|         |    A    |    V    | …
 4 |4321|    V    |    A    |    V    |
 5 |7890|         |    I    |         |
 … |                 …

Sheet2

   |  A |   B  |       C       |
---+----+------+---------------+
 1 | ID | Date | Voting method |
 2 | #1 |  #2  |       #3      |

The cell named #1 has the following formula:

=INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/27 + 2,0),1,,,"Sheet1"))

What this formula does is to map the current cell to the proper cells in Sheet1. This is done with the help of the FLOOR function. The function will increase by 1 whenever 27 rows have been passed, mapping correctly the rows in Sheet2 with the content on Sheet1.

The ADDRESS function constructs an usable reference to a cell from numeric input and sheet names, while the INDIRECT function retrieves the content pointed by the reference.

The rest of the other functions follow an identical reasoning: you use an auxiliary function to map the current cell's coordinates to the correct cell on Sheet1.

For the cell named #2:

=INDIRECT(ADDRESS(1,MOD(ROW(A2)-2,27)+2,,,"Sheet1"))

In this case, the MOD function alternates sequentially between 0 and 26, which is then converted to a sequence between 2 and 28 (in other words, where the cells with the dates are).

Finally, for the cell named #3:

=INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/27 + 2,0),MOD(ROW(A2)-2,27)+2,,,"Sheet1"))

It is a mix of the two sequences used before. The reason why is because the contents vary with regards to ID (as such, the part from cell #1 is called) and Date (which is where the part from cell #2 enters).

After inputting those functions into the correct cells, just drag down and the results should be apparent, with one small issue: the null votes are also popping up.

You can filter those results though. Select the header (in this case, the A row on Sheet2) and go to Data > Filter > Automatic Filter (or the equivalent in the Excel version you are using). Click on the drop-down on the voting method column and personalize the sorting in order to exclude results that consist of zero.

0

I would use the Power Query Add-In for this. It does not require any code or complex functions. Starting from scratch it will probably take less than 5 minutes to complete this task.

You can start a Query from an existing Excel Table. I would then use the Unpivot command to transform the data as you require.

http://office.microsoft.com/en-au/excel-help/unpivot-columns-HA104053356.aspx

The beauty of their implementation of Unpivot is that it will accept any further columns added (new dates) and process them without any change in your Query definition.

I would rename columns as required and deliver the result to an Excel Table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.