How to do this very specific concatenation and transform an array to list using Excel?

Question

First off, sorry for any grammatical errors, I'm not a native English speaker.

My problem is as follows: I have a very big dataset which contains populational data of a given city (row) in a given year (collum). I want to transform this table as to have the city and year concatenated (Ex: Los Angeles 2001, and under it, Los Angeles 2002, etc.) and the populational data to the right side, also in a single collum (two in total).

I made a mockup of what I need, given my limited English skills, I can only explain it so far.

• I have tried using the concatenate function, but I did not get very far. My version of Excel doesn't have the TOCOL function.

• Doing it by hand is out of question, it is just too big.

• If you don't know a perfect solution, some pointers would come a long way. I would be thankful just to know if excel is the appropriate tool here.

Answer 1

Try:

=HSTACK(TOCOL(A2:A6&B1:E1),TOCOL(B2:E6))

Answer 2

For older versions of Excel you can use formulas as on the screenshot.

Or without this G2:J6 area you can enter in L2:

=INDEX($A$2:$A$6 & $B$1:$E$1,(ROW(A1)-1)/4+1,MOD(ROW(A1)-1,COLUMNS($B$2:$E$6))+1)

Answer 3

This is not the complete answer to your question. I have a solution to convert the array of values into a list.
Use the following formula =TRANSPOSE(HSTACK(C3:F3,C4:F4,C5:F5,C6:F6,C7:F7))

Please let me know if you have any questions.

Answer 4

You can use Power Query to get the result:

1. Select the Range- go to Data- From Table/Range- My Table have Headers:

2.Open Power Query editor- select Column 1 to 4- Transform tab- Unpivot Columns:

3. Select Column Column1 and Attribute- Right click- select Merge Columns- Click OK:

4. Go to Home tab- Close & Load:

Answer 5

Since you've explicitly stated your version of excel does not have functions like HSTACK or TOCOL, and you've also tagged VBA, open the Visual Basic editor (Alt+F11) and paste the following code into a new module:

Option Explicit

Sub UnpivotData()

'Load the current data range into an array
    Dim currentData As Variant
    currentData = ActiveSheet.Range("A1").CurrentRegion.Value

'Set the dimensions of the output data
    Dim lRows As Long, data() As Variant
    lRows = (UBound(currentData, 1) - 1) * (UBound(currentData, 2) - 1)
    ReDim data(1 To lRows, 1 To 2)

'Transform the data, row by row, column by column
    Dim i As Long, j As Long, rowId As Long
    For i = 2 To UBound(currentData, 1)
        For j = 2 To UBound(currentData, 2)
            rowId = rowId + 1
            data(rowId, 1) = currentData(i, 1) & currentData(1, j)
            data(rowId, 2) = currentData(i, j)
        Next j
    Next i

'Write the transformed data to the output range
    With ActiveSheet.Range("H1")
        .CurrentRegion.ClearContents
        .Resize(UBound(data, 1), UBound(data, 2)).Value = data
    End With

End Sub

The above code will output the desired results, regardless of the number of rows or columns in your data range. Simply adjust the two range references to meet your needs.

Figure 1: Results after running the code...