How to split information from cell into multiple rows

davidplowman · Oct 31, 2013

Hello:

I have an issue and I'm hoping someone can help me with.

I have a spreadsheet that's has 28 columns and more than 3,000 rows.

One of the columns is a year range (i.e. 2010-2013, 1988-1999, 1996-1998 or whatever).

The file has to be submitted to another department for an upload to a website, and each year has to be listed on a separate row, with all of the information remaining the same.

So in other words, a portion of my spreadsheet that looks like this:
[TABLE="width: 537"]
<TBODY>[TR]
[TD]Source
[/TD]
[TD]New PN (Main # to Search By)
[/TD]
[TD]Make
[/TD]
[TD]Vehicle Type
[/TD]
[TD]Year (From - To)
[/TD]
[TD]Model
[/TD]
[TD]Engine
[/TD]
[/TR]
[TR]
[TD]Lester
[/TD]
[TD]A7T03277A
[/TD]
[TD]Hyster
[/TD]
[TD]Lift Truck
[/TD]
[TD]2010-2013
[/TD]
[TD]DB
[/TD]
[TD]FE
[/TD]
[/TR]
[TR]
[TD]Lester
[/TD]
[TD]A7T03277A
[/TD]
[TD]Hyster
[/TD]
[TD]Lift Truck
[/TD]
[TD]2012-2013
[/TD]
[TD]DB
[/TD]
[TD]HA
[/TD]
[/TR]
[TR]
[TD]Lester
[/TD]
[TD]A7T03277A
[/TD]
[TD]Hyster
[/TD]
[TD]Lift Truck
[/TD]
[TD]1999-2003
[/TD]
[TD]Various Models
[/TD]
[TD]FE
[/TD]
[/TR]
</TBODY>[/TABLE]

Would ultimately look like this:

[TABLE="width: 537"]
<TBODY>[TR]
[TD]Source
[/TD]
[TD]New PN (Main # to Search By)
[/TD]
[TD]Make
[/TD]
[TD]Vehicle Type
[/TD]
[TD]Year (From - To)
[/TD]
[TD]Model
[/TD]
[TD]Engine
[/TD]
[/TR]
[TR]
[TD]Lester
[/TD]
[TD]A7T03277A
[/TD]
[TD]Hyster
[/TD]
[TD]Lift Truck
[/TD]
[TD]2010
[/TD]
[TD]DB
[/TD]
[TD]FE
[/TD]
[/TR]
[TR]
[TD]Lester
[/TD]
[TD]A7T03277A
[/TD]
[TD]Hyster
[/TD]
[TD]Lift Truck
[/TD]
[TD]2011
[/TD]
[TD]DB
[/TD]
[TD]FE
[/TD]
[/TR]
[TR]
[TD]Lester
[/TD]
[TD]A7T03277A
[/TD]
[TD]Hyster
[/TD]
[TD]Lift Truck
[/TD]
[TD]2013
[/TD]
[TD]DB
[/TD]
[TD]FE
[/TD]
[/TR]
[TR]
[TD]Lester
[/TD]
[TD]A7T03277A
[/TD]
[TD]Hyster
[/TD]
[TD]Lift Truck
[/TD]
[TD]2012
[/TD]
[TD]DB
[/TD]
[TD]HA
[/TD]
[/TR]
[TR]
[TD]Lester
[/TD]
[TD]A7T03277A
[/TD]
[TD]Hyster
[/TD]
[TD]Lift Truck
[/TD]
[TD]2013
[/TD]
[TD]DB
[/TD]
[TD]HA
[/TD]
[/TR]
[TR]
[TD]Lester
[/TD]
[TD]A7T03277A
[/TD]
[TD]Hyster
[/TD]
[TD]Lift Truck
[/TD]
[TD]1999
[/TD]
[TD]Various Models
[/TD]
[TD]FE
[/TD]
[/TR]
[TR]
[TD]Lester
[/TD]
[TD]A7T03277A
[/TD]
[TD]Hyster
[/TD]
[TD]Lift Truck
[/TD]
[TD]2000
[/TD]
[TD]Various Models
[/TD]
[TD]FE
[/TD]
[/TR]
[TR]
[TD]Lester
[/TD]
[TD]A7T03277A
[/TD]
[TD]Hyster
[/TD]
[TD]Lift Truck
[/TD]
[TD]2001
[/TD]
[TD]Various Models
[/TD]
[TD]FE
[/TD]
[/TR]
[TR]
[TD]Lester
[/TD]
[TD]A7T03277A
[/TD]
[TD]Hyster
[/TD]
[TD]Lift Truck
[/TD]
[TD]2002
[/TD]
[TD]Various Models
[/TD]
[TD]FE
[/TD]
[/TR]
[TR]
[TD]Lester
[/TD]
[TD]A7T03277A
[/TD]
[TD]Hyster
[/TD]
[TD]Lift Truck
[/TD]
[TD]2003
[/TD]
[TD]Various Models
[/TD]
[TD]FE
[/TD]
[/TR]
</TBODY>[/TABLE]

The problem is I’m not sure of the best way to get from Point A to Point B. I would image in would involve splitting the beginning and end year into end rows, then calculating the year range (3 years, 1 year and 4 years.)

But I’m unsure of what to do from there? Is it just a process of manually inserting the rows and copying in the information from the other columns, or is there a quicker, more efficient way of doing this?

As a side note, I’m familiar with many of the “advanced” functions of Excel, but haven’t done any VBA program yet. But if there is a program that would help, I’m willing to take the plunge into learning about it.
Thanks,
David

Rick Rothstein · Oct 31, 2013

Give this macro a try (see note at end for manual changes you have to make)...

Rich (BB code):

Sub SplitDataByYears()
  Dim R As Long, C As Long, CC As Long, Index As Long, TotalYears As Long, LastRow As Long
  Dim YearCol As Long, ArrIn As Variant, ArrOut As Variant
  Const YearLetter As String = "E"
  YearCol = Cells(1, YearLetter).Column
  LastRow = Cells(Rows.Count, YearCol).End(xlUp).Row
  ArrIn = Range("A1:AB" & LastRow)
  TotalYears = 1 + Evaluate("SUMPRODUCT(ISNUMBER(FIND(""-""," & YearLetter & "2:" & YearLetter & _
                            LastRow & "))*(1+RIGHT(" & YearLetter & "2:" & YearLetter & LastRow & _
                            ",4)-LEFT(" & YearLetter & "2:" & YearLetter & LastRow & ",4)))")
  ReDim ArrOut(1 To TotalYears, 1 To 28)
  For R = 2 To UBound(ArrIn)
    For CC = CLng(Left(ArrIn(R, YearCol), 4)) To CLng(Right(ArrIn(R, YearCol), 4))
      Index = Index + 1
      For C = 1 To 28
        If C = YearCol Then
          ArrOut(Index, C) = CC
        Else
          ArrOut(Index, C) = ArrIn(R, C)
        End If
      Next
    Next
  Next
  With Sheets("Sheet2")
    .Range("A1:AB1") = ActiveSheet.Range("A1:AB1").Value
    .Range("A2:AB" & UBound(ArrOut) + 1) = ArrOut
  End With
End Sub

NOTE: Change my assumed Column E for the year range, highlighted in red, to the actual column letter containing the years. Also change the output sheet name as needed (highlighted in green)... if you want to overwrite the original data with the newly formatted data, then change Sheets("Sheet2") to ActiveSheet instead.

davidplowman · Oct 31, 2013

Rick:

Thank you so much for your help with this and your extremely quick response!

In reviewing my data, I realize I'll have some "prep work" in getting the data in the date range to where it needs to be, but I'm looking forward to trying out my first code out, and seeing how it works... I just know this will save me so much time as opposed to doing it manually.

I will definitely report back once I've gotten my data in order and have had a chance to try this out!

Thanks so much for your help!

Thanks again,
David

Rick Rothstein · Oct 31, 2013

davidplowman said:
In reviewing my data, I realize I'll have some "prep work" in getting the data in the date range to where it needs to be, but I'm looking forward to trying out my first code out, and seeing how it works... I just know this will save me so much time as opposed to doing it manually.

What do you mean by "getting the data in teh date range where it needs to be"? It can stay in the column it is in now, just change the letter "E" that I highlighted in red to the letter for the column that those dates are really in... the rest of the code will automatically adjust to that change once you do that.

davidplowman · Oct 31, 2013

Rick: 

I was just referring to the incompleteness my data in my long spreadshee--especially in the date range section (the example I posted was “idealized” to make my question simpler).

I've been downloading data from various sources, and my source material in the date range is incomplete and inconsistent in a lot of cases.... Some rows have just one year listed, others have no year at all, others just say "n/a" etc. Both for the sake completeness of my spreadsheet and for the macro to work, I’m sure I will have to have product specialists review all of the lines and fill in a complete year range.

I'm sure the program you provided will work great; I just meant I will have I'll need to do some inernat "data consistency work" so I can be at a point where I can put it to proper use.. (Otherwise, it would be the old “garbage in, garbage out” scenario, no matter how helpful this macro is…) Oh well, such has been the nature of this project, and is I suppose "job security" for me!
At any rate, I’m sure this will be a huge help!

Thanks again,
David

Rick Rothstein · Oct 31, 2013

davidplowman said:
Some rows have just one year listed, others have no year at all, others just say "n/a" etc.

Just so you know, the code I posted will work for both single years and for year ranges (but as you guessed, not for blank, N/A, etc.).

davidplowman · Oct 31, 2013

Rick:

That's great to know! I was wondering about how single-year info would work with the program... Again I appreciate your help!

Thanks,
David

davidplowman · Jan 30, 2014

Hey All:

The scope of the project changed, I'm just being able to get to this.

I haven't worked for VBA before, so I'm hoping my question isn't too rudimentary.

But in trying to run the above VBA script, I get an "Expected line or statement or end of Statement" error. What am I doing wrong? How do I fix this?

Also, so I don't have to keep asking basic questions, where should I go to get a "crash course" on VBA programming?

Thank you,
David

davidplowman · Feb 4, 2014

Hello:

Just an update, I figured it out, and everything works wonderfully!!!!

Thanks so much for your help!

Thanks,
David

How to split information from cell into multiple rows

davidplowman

New Member

Excel Facts

Rick Rothstein

MrExcel MVP

davidplowman

New Member

Rick Rothstein

MrExcel MVP

davidplowman

New Member

Rick Rothstein

MrExcel MVP

davidplowman

New Member

davidplowman

New Member

davidplowman

New Member

Similar threads

Forum statistics

Share this page

How to split information from cell into multiple rows

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock