Transferring data in recurring patern

[chumhum]

Hello, forum.

I have an excel sheet with time-series data for several items, where each row represents a different year. Something like this:
Item Var 1 Var 2 ...
A 5 10
A 6 10
A 8 20
B 1 33.7
B 2 29
B 0 24
.
.
.
And so on.

I'm trying to create a separate sheet for each variable that will list each item as a single row and have the sequential values of that variable as columns. So Sheet1 would look like this:
T1 T2 T3
A 5 6 8
B 1 2 0

Sheet2 would be similar but for variable2, and so on.

Can anyone help me figure out how to do that? I've looked around this forum and basic googling but no luck. Maybe there's a way to use modular counting to skip cells in an if statement, but other than that I don't have many good ideas.

The variables are all numbers and there are an equal number of samples (10) for each member of the population.

Thanks in advance for your help and I'll answer any questions you need.

 

[Peter_SSs]

Welcome to the MrExcel board!

... and there are an equal number of samples (10) for each member of the population.

Can you explain what that means? What part of your sample is a "member of the population"?

Just to be sure, for the sample data given previously, what would the Var 2 sheet look like?

 

[chumhum]

Sorry for not being more clear!

A and B are members of the population. There is a population of objects (they're actually corporations) and the data are auditing figures for the corporations. So there's a list of objects (~500), and each object occupies ten row, one per year.

The Sheet2 should look like this:
Item T1 T2 T3
A 10 10 20
B 33.7 29 24

Does that answer your questions?

 

[Peter_SSs]

Does that answer your questions?

Sorry, I'm still not quite sure.
Does it mean that there would be 10 A's (or whatever the real data looks like) then 10 B's, 10 C's etc down the left column?
Or does it mean there will be 10 values across for each row. That is, 11 columns altogether per row (the Item & 10 variables)?

 

[chumhum]

There are 10 rows for each item. So 10 As, 10 Bs, etc. I just kept my example to 3 of each so it wouldn't get too long.

There are about 20 columns, each representing different variables.

Each row represents a single year of data for A, and what I want to do is combine all of the A data for a given variable into a single row. All of the B data for that same variable occupying the row below it and so on.

 

[Peter_SSs]

Test in a copy of your workbook.

I haven't tried to insert headers at this point. We can worry about that if needed if the rest of the code does what is should.

Adjust the 'Const' line to match your repeated group size in column A - 10 as we discussed I believe, but at the moment I have 3 per the sample data.

Sub SplitOut()
  Dim a, b, var
  Dim i As Long, j As Long, k As Long, x As Long
  
  Const RwsPerGroup As Long = 3   '<- Ajust this to your standard size
  
  a = Sheets("Data").Range("A1").CurrentRegion.Value
  ReDim b(1 To (UBound(a) - 1) / RwsPerGroup, 1 To RwsPerGroup + 1)
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  For i = 2 To UBound(a, 2)
    x = 0
    For j = 2 To UBound(a) Step RwsPerGroup
      x = x + 1
      b(x, 1) = a(j, 1)
      For k = 1 To RwsPerGroup
        b(x, 1 + k) = a(j - 1 + k, i)
      Next k
    Next j
    On Error Resume Next
    Sheets(a(1, i)).Delete
    On Error GoTo 0
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = a(1, i)
    ActiveSheet.Range("A2").Resize(UBound(b), RwsPerGroup + 1).Value = b
  Next i
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
End Sub