Transferring data in recurring patern

chumhum

New Member
Joined
Nov 22, 2015
Messages
3
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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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?
 
Upvote 0
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?
 
Upvote 0
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)?
 
Upvote 0
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.
 
Upvote 0
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.

Rich (BB code):
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top