Ordering data from rows into column

It's not as quick as possible - try:

Sub Alex(): Dim r As Long, c As Long, n As Long, CY As String, CD As String
For r = 3 To Range("A" & Rows.count).End(xlUp).Row * 16: CY = Cells(r, 1): CD = Cells(r, 2)
If CY = "" Then GoTo ExitSub
Cells(r, 1) = CY & " " & Cells(2, 3)
Cells(r, 2) = CD & " " & Cells(2, 3)
Range("A" & r + 1).Resize(15, 1).EntireRow.Insert Shift:=xlDown
n = 0: For c = 9 To 98 Step 6: n = n + 1
Cells(r + n, 1) = CY & " " & Cells(2, c)
Cells(r + n, 2) = CD & " " & Cells(2, c)
Cells(r + n, 3).Resize(1, 6).Value = Cells(r, c).Resize(1, 6).Value
Next c
r = r + n
Next r
ExitSub:
ActiveSheet.UsedRange.Offset(0, 8).Clear

End Sub

Another formula somewhat similar to DBY's but relying upon matching codes.

This assumes all 'Codes' suffixes in sheet WHOLE DATA to be the same. Enter this formula in C3 of Summaries sheet filled down and across.

Formula:

=INDEX('WHOLE DATA'!$C$3:$CU$138,MATCH($A3,'WHOLE DATA'!$A$3:$A$138,0),MATCH(RIGHT($B3,2)&C$2,'WHOLE DATA'!$C$1:$CU$1,0))

My code takes 5 seconds without the screen updating:

Sub Alex(): Dim r As Long, c As Long, N As Long, CY As String, CD As String
Application.ScreenUpdating = False
For r = 3 To Range("A" & Rows.count).End(xlUp).Row * 16: CY = Cells(r, 1): CD = Cells(r, 2)
If CY = "" Then GoTo ExitSub
Cells(r, 1) = CY & " " & Cells(2, 3)
Cells(r, 2) = CD & " " & Cells(2, 3)
Range("A" & r + 1).Resize(15, 1).EntireRow.Insert Shift:=xlDown
N = 0: For c = 9 To 98 Step 6: N = N + 1
Cells(r + N, 1) = CY & " " & Cells(2, c)
Cells(r + N, 2) = CD & " " & Cells(2, c)
Cells(r + N, 3).Resize(1, 6).Value = Cells(r, c).Resize(1, 6).Value
Next c
r = r + N
Next r
ExitSub:
ActiveSheet.UsedRange.Offset(0, 8).Clear
Application.ScreenUpdating = True
End Sub

@ DBY - Yeah, on my machine, with screen updating, it took 48s, and yours took 36s - I only took the values as any formulas would have been invalid (I also kept the Country Code as suggested by Alex' headers.)

@ Alex, thanks for the rep!

Here is your data re-organized as I think you indicated you wanted. There are 3 years without data 1997, 1999 and 2001. These years are included with blanks but you can filter them out by using the filter on the codes and exclude blanks.

Hi guys,

Sorry I was in seminars all day. Thank you so much for your help. It really means a lot - you guys are actual magicians The files you have uploaded are exactly what I need. As I mentioned I am completely VBA illiterate, however I am interested to know how that formula works FlameRestired - how do I use it?

As I final request, you will find attached the two other datasets (which have the exact same structure as the previous one). So I was wondering if these could be re-organised as well if possible?

I'll probably have a million other questions but this is my main issue at the moment.
Thank you for your help!!

It is VBA



If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)

Then - Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select "Module" from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name
*Be sure to save the book with the code as Macro-Enabled

Hi Alex,

It looks like the same routine works on this batch as well - is there something different??

I did trim the code a little:

Sub Alex(): Dim r As Long, c As Long, N As Long, CY As String, CD As String
Application.ScreenUpdating = False
ActiveSheet.UsedRange.Replace "#N/A", ""
For r = 3 To Range("A" & Rows.count).End(xlUp).Row * 16: CY = Cells(r, 1): CD = Cells(r, 2)
If CY = "" Then GoTo ExitSub
Cells(r, 1) = CY & " " & Cells(2, 3)
Cells(r, 2) = CD & " " & Cells(2, 3)
Range("A" & r + 1).Resize(15, 1).EntireRow.Insert Shift:=xlDown
N = 0: For c = 9 To 98 Step 6: N = N + 1
Cells(r + N, 1) = CY & " " & Cells(2, c)
Cells(r + N, 2) = CD & " " & Cells(2, c)
Cells(r + N, 3).Resize(1, 6).Value = Cells(r, c).Resize(1, 6).Value
Next c
r = r + N
Next r
ExitSub:
ActiveSheet.UsedRange.Offset(0, 8).Clear
Rows(2).EntireRow.Delete Shift:=xlUp
Cells(1, 2) = "Code/Year"
Application.ScreenUpdating = True
End Sub

Here is your workbook with the two data sets re-arranged. The data sets re-arranged include the missing years. These years can be omitted by using the filter on the years column and unchecking 1997, 1999, and 2001 which are the missing years in your data. All data in the original data with errors have blank cells. If you fill in those cells with data, that data will be included in the re-arranged data.

Originally Posted by Alex789king

Hi guys,

.......The files you have uploaded are exactly what I need. As I mentioned I am completely VBA illiterate, however I am interested to know how that formula works FlameRestired - how do I use it?.............

Thank you for your help!!

The key is in understanding how the INDEX function works. The first argument is the entire data range 'WHOLE DATA'!$C$3:$CU$138.

The next two arguments return the row and column numbers of that range. INDEX returns data from the intersection of that row and column.

The row number is determined by MATCHing the country name to the column of country names in 'WHOLE DATA'!$A$3:$A$138. In the case of Albania it is the first row of your data range or row 1.

The column number is a little trickier in this case.

The string to MATCH the ‘Codes’ must be built. This formula takes the RIGHT most 2 characters of the year in column B and appends

the ‘Codes’ suffix in row 2 to it by concatenation .... RIGHT($B3,2)&C$2. Note the relative and absolute addressing of the cells $B3 and C$2.

The “$” anchors column B and row 2 so that all copies of the formula reference the same column and row, but as you move down and across the row in anchored column B changes as does the column reference in the anchored row 2.

Then MATCH matches those resulting strings to the ‘Codes’ row in 'WHOLE DATA'!$C$1:$CU$1 which returns the column number of the data range. Those two arguments passed to INDEX instruct INDEX to return whatever is at the intersection of that row and column.

Has this helped?

Originally Posted by Alex789king

Hi Newdoverman - thank you so much for re-ordering the two other datasets! Flameretired & xladept, I've been trying to figure both VBA and the formula over the past 3 days without asking for your help, but with no success - and I've got to the point where my work is getting really delayed (but at least i'm upping my excel skills!!).
So - one of my last (initial) calls for help is to order this last dataset in the same manner (so country/years on the left and indicators on the top).

Thank you again! Hope your Friday is going well!

I cannot do it without an array formula ... > 54,000 copies of it. They are resource hungry and took my machine about 2½ minutes to calculate. The file size @ 1.72 Meg.

It is not much of a "solution". Sorry.

Hi ALex,

I had to use a different browser - try this:

Sub AlexII(): Dim R As Long, c As Long, N As Long, CY As String
Application.ScreenUpdating = False
ActiveSheet.UsedRange.Replace "n/a", ""
For R = 2 To Range("A" & Rows.count).End(xlUp).Row * 16: CY = Cells(R, 1)
If CY = "" Then GoTo ExitSub
Cells(R, 1) = CY & " " & Cells(1, 3)
Range("A" & R + 1).Resize(18, 1).EntireRow.Insert Shift:=xlDown
N = 0: For c = 4 To 21: N = N + 1
Cells(R + N, 1) = CY & " " & Cells(1, c)
Cells(R + N, 2) = Cells(R, 2)
Cells(R + N, 3).Value = Cells(R, c).Value
Next c
R = R + N
Next R
ExitSub:
ActiveSheet.UsedRange.Offset(0, 3).Clear
Cells(1, 3) = "": Range("A:A").Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Tragically, I have no idea

Hey xladept,
ok so I found the Visual Basic Editor - do I just paste the code on 'Economic indicators' tab or do I need to do something beforehand (I posted a screenshot of how my Visual Basic Editor looks like)?

(I also posted a screenshot of what I'm trying to achieve, just for a refresher )Screen Shot 2016-06-18 at 18.43.12.png
Screen Shot 2016-06-18 at 18.35.14.png

See post#25

Here's another version:

Sub AlexIII(): Dim R As Long, c As Long, N As Long, CY As String
Application.ScreenUpdating = False
ActiveSheet.UsedRange.Replace "n/a", ""
For R = 2 To Range("A" & Rows.count).End(xlUp).Row * 16: CY = Cells(R, 1)
If CY = "" Then GoTo ExitSub
Cells(R, 1) = CY & " " & Cells(1, 3)
Range("A" & R + 1).Resize(19, 1).EntireRow.Insert Shift:=xlDown
N = 0: For c = 4 To 21: N = N + 1
Cells(R + N, 1) = CY & " " & Cells(1, c)
Cells(R + N, 2) = Cells(R, 2)
Cells(R + N, 3).Value = Cells(R, c).Value
Next c
N = N + 1: Cells(R + N, 2) = Cells(1, c) & " " & Cells(R, c)
R = R + N
Next R
ExitSub:
ActiveSheet.UsedRange.Offset(0, 3).Clear
Cells(1, 3) = "": Range("A:A").Columns.AutoFit
Application.ScreenUpdating = True
End Sub

There is at least one inconsistency in the last data upload. Taiwan is listed once followed by Taiwan Province of China 7 times. How do you want this resolved?

I have not done a painstaking job of searching out others. My formulas won't resolve until these are cleaned up.

Edit There are also trailing spaces in some .... "Gambia, The" and "Gambia, The " ... for example.

Another edit There are more showing up along with duplicated / missing 'Subject Descriptors'. Iran entries are another 'mixed' country name.

Another edit If you enter this formula in A1 of the data sheet, click in the formula bar and press the F9 function key you will see an array of row numbers.

{"B226";"B227";"B228";"B229";"B230";"B231";"B232";"B233";"B314";"B315";"B316";"B317";"B318";"B319";"B320";"B321";"B698";"B699";"B700";"B701";"B702";"B703";"B704";"B705"}

That is where all of the country problems are. The 'Descriptors' are likely the same as well.

="B"&SMALL(IF(COUNTIF(B2:B825,B2:B825)<>8,ROW(B2:B825)),ROW(INDEX(A:A,1):INDEX(A:A,COUNT(IF(COUNTIF(B2:B825,B2:B825)<>8,ROW(B2:B825))))))

It looks like your RibbonX_Code is a standard module so just paste the code there

For a formula solution I took the liberty of "Standardizing" the country names and descriptors.

There are two helper columns. In C4:C1960 of 'Summary' this formula:

Formula:

=INDEX(EconDataNew!$B$2:$B$825,(CEILING((ROW()-ROW($4:$4)+1)/19,1)-1)*8+1)

In D4:D1960 this formula:

Formula:

=MOD(ROW()-ROW($4:$4),COUNT(EconDataNew!$D$1:$V$1))+EconDataNew!$D$1

Then array enter this formula in E4 fill down and across to L1960

Formula:

=INDEX(EconDataNew!$D$2:$V$825,MATCH(1,1/(($C4=EconDataNew!$B$2:$B$825)*(Summary!E$3=EconDataNew!$C$2:$C$825)),1),MATCH($D4,EconDataNew!$D$1:$V$1,1))

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

You are welcome. Thank you for the feedback, and hope your thesis goes well.

I took the liberty of standardizing some of the column headers. 1999 and 2009 were missing the M columns. It looked like typos. I changed them.

I inserted a row in DataAlex for a helper row. The helper row in of DataAlex has this formula in B1. Filled across it defines the common years.

This helper cell formula is in B1 of the Summary sheet. It defines the last row of the helper columns. It must be array entered (Ctrl + Shift + Enter).

Formula:

=COUNTA(DataAlex!$A$3:$A$23)*SUM(IF(FREQUENCY(DataAlex!$B$1:$IN$1,DataAlex!$B$1:$IN$1),1))

This is the helper column for the countries. Enter in B3 and fill down until you get blanks. it is non array.

Formula:

=IF((ROW()-ROW($3:$3)+1)>$B$1,"",INDEX(DataAlex!$A$3:$A$23,CEILING((ROW()-ROW($3:$3)+1)/19,1)))

This is the helper formula for years. It is non array. Enter in C3. Fill down until you get blanks.

Formula:

=IF(B3="","",INDEX(DataAlex!$B$1:$IN$1,,MOD(ROW()-ROW($3:$3),19)*13+1))

Array enter this in D3. I returns the data from DataAlex. Fill across and down until you get blanks.

Formula:

=IF(B3="","",IF((COLUMN()-COLUMN($D:$D)+1)>13,"",INDEX(INDEX(DataAlex!$B$3:$IN$23,MATCH(1,1/($B3=DataAlex!$A$3:$A$23),0),),SMALL(IF($C3=DataAlex!$B$1:$IN$1,MATCH(DataAlex!$B$2:$IN$2,DataAlex!$B$2:$IN$2,0)),COLUMN()-COLUMN($D:$D)+1))))