Split a year range into one row per year

rameez788

New Member
Joined
Sep 8, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi all,
I am new on this forum. I have attached sample data, i need someone to help me convert the left side to how the data looks on the right.

Please let me know if you require any additional information.

Thanks,
RJ
 

Attachments

  • Data.JPG
    Data.JPG
    149.1 KB · Views: 34

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel.

Does the data start in cell A3?

In the column "Manufacturer" do you only have one data or are there others?
If there are other data, you could put how the following data would be with their respective values in the columns Model, Year and Version. You can paste a data range here using the XL2BB tool, see my signature.

The result in the columns on the right was already clear to me, I just want to know how the following data would be in the "Manufacturer" column.
 

rameez788

New Member
Joined
Sep 8, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi Thanks for your reply. Please see the attached screen shot. The data is around 13000 rows.
 

Attachments

  • Data 2.JPG
    Data 2.JPG
    99.3 KB · Views: 13

rameez788

New Member
Joined
Sep 8, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Creating the final list from the RAW Data.xlsx
ABCD
1ManufacturerModelYearVersion
2
3AC2122000-20043.5 SC
43022000-2004
5Ace1998-20023.5 Convertible
64.6 Convertible
74.9 convertible (Automatic)
84.9 convertible (Manual)
9Aceca1998-20023.5 Sports
104.6 Coupe Automatic
114.6 Coupe Manual
12Cobra1997-20024.9 MK IV CRS
134.9 MK IV
14Dazx Cobra1997-2002
15Mamba2001-20043.5 coupe
164.0 Coupe (192 bhp)
174.0 Coupe (231 bhp)
18Superblower1998-20024.9 Convertible (320 bhp)
194.9 Convertible (355 bhp)
20AKCobra
21ARO240
22243
23Abarth124 Spider2016-20181.4 Multiair GT Roadster
241.4 Multiair Scarpione Spider
251.4 Multiair Spider
265002009-20161.4 T- jet Esseesse Manual
271.4 T- jet Hatchback Automatic
281.4 T- jet Hatchback MTA
291.4 T- jet Hatchback Manual
30500C2010-20161.4 T-jet Competizione Convertible
311.4 T-jet Competizione Esseesse
321.4 T-jet Convertible MTA
331.4 T- jet Convertible Manual
341.4 T- jet Esseesse Convertible
355952013-20161.4 T- jet 50th Anniversario Hatchback Automatic
361.4 T- jet 50th Anniversario Hatchback MTA
371.4 T- jet Hatchback Manual
381.4 T- jet Competizione Hatchback Automatic
391.4 T- jet Competizione Hatchback MTA
401.4 T-jet Competizione Hatchback Manual
411.4 T- jet Hatchback MTA
421.4 T- jet Trofeo Hatchback
431.4 T-jet Turismo Hatchback Automatic
441.4 T- jet Turismo Hatchback MTA
451.4 T- jet Turismo Hatchback Manual
461.4 T- jet Yamaha factory
4720161.4 T- jet 70th Hatchback Manual
481.4 T- Jet Competizione 70th Auto
491.4 T- Jet Competizione 70th Manual
501.4 T- jet Competizione Hatchback Auto
511.4 T- Jet Competizione Hatchback Manual
521.4 T- Jet EsseEsse Auto
531.4 T- Jet EsseEsse Manual
541.4 T- jet Hatchback Auto
551.4 T- Jet Hatchback Manual
561.4 T-jet Monster Yamaha
571.4 T- Jet Pista 70th Hatchback Auto
581.4 T- Jet Pista 70th Hatchback Manual
591.4 T-jet Trofeo Hatchback Manual
601.4 -jet Turismo 70th (Automatica) Auto
611.4 T- jet Turismo 70th Auto
621.4 T- jet Turismo Auto
631.4 T- jet Turismo Manual
64595C2013-20161.4 T- Jet Cabrio MTA
651.4 T- Jet Cabrio Manual
661.4 T- Jet Competizione Cabrio Automatic
671.4 T- jet Competizione Cabrio MTA
681.4 T-jet Competizione Cabrio Manual
691.4 T- jet Turismo Cabrio Automatic
701.4 T- Jet Turismo Cabrio MTA
711.4 T- jet Turismo Cabrio Manual
7220161.4 T- Jet 70th Cabrio
731.4 T- jet Cabrio
741.4 T- jet 70th Cabrio
751.4 T- jet Competizione Cabrio
761.4 T- jet EsseEsse 70th Cabrio
771.4 T- jet Pista 70th cabrio
781.4 T- jet Pista 70th cabrio Auto
791.4 T- jet Turismo 70th (Automatica) Cabrio
801.4 T- jet Turismo 70th Cabrio
811.4 T- jet Turismo 70th Cabrio Auto
821.4 T- jet Turismo Cabrio
831.4 T- jet Turismo Cabrio Automatic
8469520141.4 T- jet 70th hatchback 3dr Manual
851.4 T- jet Biposto hatchback manual
861.4 T- jet Biposto hatchback manual EU5
871.4 T- jet Biposto Record hatchback manual
881.4 T- jet Rivale 175 hatchback Auto
891.4 T- jet Rivale 175 hatchback Manual
901.4 T- jet Rivale Hatchback Auto
911.4 T- jet Rivale Hatchback Manual
921.4 T- jet XSR Yamaha
93695C20121.4 T- jet Edizione Maserati
941.4 T- jet Rivale Convertible
951.4 T- jet Rivale Convertible Auto
961.4 T- Jet Rivale 175 Convertible
971.4 T- Jet Rivale 175 Convertible Auto
981.4 T- jet XSR Yamaha Convertible
99Grande Punto2010-20111.4 T- Jet 155
100Punto Evo2010-20141.4 16v
1011.4 T- Jet Abarth Esseesse
1021.4 T- jet Hatchback
1031.4 T- Jet Supersport
104Axiam4002000-20030.4 DE
1050.4 D Echo
1060.4 D L
1070.4 D Luxe
1080.4 D SL
1090.4 Standard
1100.4 D Super Luxe
1115002000-20030.5 D Mac
1121999-20080.5 D
1130.5 D L
1140.5 D SL
1150.5 D UT
1160.5 L
1170.5 SL
1187002007-20110.5 A751 Scouty GTR
1192005-20110.5 A751 Crossline
1200.5 A751 SL
1210.5 A751 Superluxe
1220.5 D A751 L
1230.5 D A751 Pack
1240.5 D A751 SL
1250.5 D A751 Superluxe
1260.5 D Roadline GT
1270.5 Roadline GTR
128Coupe2012-20190.4 D S
129Crossline2011-20190.4 D GT Superluxe
1300.5 GTR Superluxe
1310.6 D GT Superluxe
1320.6 D Premium GT
133Crossover2012-20170.5 GTR
1340.5 GTR Vision Range
1350.5 D GT
1360.5 D GT Vision Range
137eCity
138Alfa Romeo1451994-19991.6 3dr
1391.7 16v
1402.0 T.Spark 16v
1411997-20011.6 T.Spark 16v
1421.8 T.Spark 16v
1432.0 T.Spark 16v Cloverleaf
1441461995-19991.6
1451.7 16v 5dr
1462.0 TI
1471997-20011.6 T.Spark
1481.8 T.Spark
1492.0 T.Spark TI
1501472001-20053.2 GTA
1511.6 T.Spark
1521.9 JTD
1532.0 T.Spark
1541.6 T.Spark
1551.9 JTD
1561551992-19982.0 Cloverleaf
1572.0 T.spark
1581.8 Silverstone
1592.0 T.Spark 16v
1601561998-20031.6 T.Spark
1611.8 T.Spark
1622.0 T.spark
1632.4 JTD
1642.5 V6 24v
1651.6 T.Spark
1661.8 T.Spark
1672.0 T. Spark
1682.4 JTD
1692.5 V6
1701.6 T.spark
1711.8 T.Spark
1722.0 T.Spark
1732.4 JTD Veloce
1742.5 V6 24v
1752002-20061.9 JTD 115
1763.2 GTA
1771.9 JTD
1782.0 JTS
1791.6 T.Spark TI
1801.8 T.Spark
1811.9 JTD TI
1822.0 JTS TI
1832.4 JTD TI
1841.6 T.Spark
1851.8 T.Spark
1861.9 JTD
1871.6 T.Spark
1881.8 T.Spark
1891.9 JTD
1902.0 JTS
1912.4 JTD
192156 Sportwagon2000-20031.6 T.Spark
1931.8 T.Spark
1942.0 T.Spark
1952.4 JTD
1962.5 V6 24v
1971.6 T.Spark
1981.8 T.Spark
1992.0 T.Spark
2002.4 JTD Turismo Estate
2012.5 V6 24v
2021.6 T.Spark
2031.8 T.Spark
2042.0 T.Spark
2052.4 JTD Veloce Estate
2062.5 V6 24v
2072002-20063.2 GTA
2082.0 JTS
2092.4 JTD
2101.8 T.Spark
2111.9 JTD
2122.0 JTS
2131.8 T.Spark
2141.9 JTD
2152.0 JTS
2162.5 JTD
2171.8 T.Spark
2181.9 JTD
2192.0 JTS
2202.4 JTD
2211592006-20121.8 Tbi
2221.9 JTDM
2232.0 JTDM
2242.2 JTS
2252.4 JTDM
2263.2 JTS V6 Elegante
2271.9 JTDM Limited edtion
2281.8MPI
2291.8 Tbi
2301.9 JTDM
2312.0 JTDM
2322.2 JTS
2332.4 JTDM
2343.2 JTS
2351.8 Tbi
2361.9 JTDM
2372.0 JTDM
2382.2 JTS
2392.4 JTDM
2403.2 JTS
2411.8 MPI
2421.9 JTDM
2432.0 JTDM
2442.2 JTS
2452.4 JTDM
2463.2 JTS V6
2471.8 Tbi
2481.9 JTDM
2492.0 JTDM 16v
250159 Sportwagon2006-20121.8 Tbi
2511.9 JTDM 16v
2522.0 JTMD
2532.2 JTS
2542.4 JTMD
2553.2 JTS V6
2561.8 MPI
2571.9 JTDM 16v
2582.0 JTDM 16v
2592.2 JTS Lusso
2602.4 JTDM Lusso
2613.2 JTS V6
2621.8 TBi
2631.9 JTDM 16v
2642.0 JTDM 16v
2652.2 JTS
2662.4 JTDM
2673.2 JTS V6
2681.8 Tbi
2691.9 JTDM 16v
2702.0 JTMD 16v
2712.2 JTS
2721.8 TBi
2731.9 JTDM
2742.0 JTDM
2751641988-19983.0 Cloverleaf
2762.0 T.Spark
2773.0 Lusso
2782.0 T. Spark Super
2793.0 Super
2802.0 T.Spark Super Lusso
2813.0 Super Lusso
2821661999-20042.0 T.Spark 16v
2832.5 V6 24v
2843.0 V6 24v
2852004-20062.0 T.Spark
2863.0 V6 24v
2873.2 V6 24v
2881750
2892000
2902600
291331985-19901.5 Sportwagon
2921.7 Cloverleaf
2931.7 veloce
2941991-19941.7 16v
2951983-19901.3 Hatchback
2961.3 s Boxer 4C
2971.5 Amica
2981.5 Boxer 4C
2991.5 Cloverleaf
3001.5 Hatchback
3011.5 TI
3021.5 Veloce
3031.7 Cloverleaf
3041.7 Veloce
3051990-19941.5 iE
3061.7 16v
3071.7 Elegante
3081.7 Turismo
3091.7 iE
3104C20151750 TBi
3112014-20181750 TBi
3126
313751986-19931.8 Saloon
3141.8 Veloce
3152.0 T.Spark
3162.5 Cloverleaf
3172.5 Saloon
3182.5 Veloce
3193.0 Cloverleaf
3203.0 Lusso
3213.0 Saloon
3223.0 Veloce
3238C
324901985-19872.5 Cloverleaf
325Alfasud1981-19841.3 SC
3261.3 TI
3271.5 Cloverleaf
3281.5 Hatchback
3291.5 SC
3301.5 TI
3311978-19831.3 Saloon
3321.5 Super Saloon
3331.5 TI Saloon
334Alfetta1978-19862.0 Cloverleaf
3352.0 L
3362.0 SL
3372.0 Saloon
338Arna1984-19861.2 SL
3391.3 SL
3401.5 TI
341Brera2006-20111.8 TBi Coupe
3421.8 TBi Italia
3432.0 JTDM Coupe
3442.0 JTDM Italia
3452.2 JTS
3462.2 JTS S
3472.2 JTS SV
3482.4 JTD
3492.4 JTDM
3503.2 JTS V6
351Dauphine
352GT2004-20111.8 T.Spark
3531.9 JTDM 16v
3542.0 JTS
3553.2 V6 24v
356GTV1976-19872.0 Coupe
3571996-20032.0 T.Spark 16v
3583.0 V6 24v
3592003-20062.0 JTS
3602.0 T.Spark
3613.2 V6 24v
362GTV 61981-19872.5 Coupe
363Giulia20162.0 T
3642.2 TD
3652.9 V6
366Gold Cloverleaf1984-19862.5 Saloon
367Junior
368MiTo2008-20140.9 TB
3690.9 TwinAir
3701.3 JTMD
3711.4 16v
3721.4 8v
3731.4 MultiAir
3741.4 TB
3751.6 JTDM
376Montreal
377RZ
378SZ1990-19923.0 Zagato
379Spider1990-19932.0 Convertible
3801996-20032.0 T.spark 16v
3813.0 V6 24v
3822003-20062.0 JTS
3832.0 T.Spark
3843.2 V6 24v
3852007-20111.8 TBi
3862.0 JTDM
3872.2 JTS
3883.2 JTS V6
389Sprint1979-19891.3 Coupe
3901.5 Cloverleaf
3911.5 Veloce
3921.7 Cloverleaf
3931.7 veloce
394Stelvio20172.0 T
3952.2 TD
3962.9 V6
397AllardL Type Tourer
398AlpineA11020181.8 Turbo
399AlvisABH386C
400TA14
401TD21
402TE21
403TF21
404AnzaniAstra
405ArielAtom
406Nomad
407Arm Siddeley30
408Hurricane
409Lancaster
410Sapphire
411Typhoon
412Whitley
413Arnolt-BristolDeluxe
414AscariA10
415Ecosse
416GT3
417KZ1
418AsiaRocsta1994-19971.8 DX Convertible
4192.2 D Convertible
4201994-19971.8 DX SUV
4212.2 D SUV
422Aston MartinCygnet2011-20131.33 Edition
4231.33 Launch Edition
424DB1
425DB1120174.0 V8 Volante
42620164.0 V8 Coupe
4275.2 V12
428DB2
429DB3
430DB4
431DB5
432DB6
433DB71996-20043.2 Volante
4345.9 Volante
4351994-20043.2 Coupe
4365.9 GT
4375.9 GTA
4385.9 Vantage
439DB92004-20085.9 Volante
4402008-20176.0 Carban
4416.0 GT
4426.0 Luxuary Edition
4436.0 Sport Edition
4446.0 Volante
4452003-20085.9 Coupe
4462008-20176.0 Carban
4476.0 Coupe
4486.0 GT
4496.0 Luxuary Edition
4506.0 Sport Edition
451DBS2009-20136.0 V12
4526.0 Volante
45320195.2 V12
4542008-20136.0 V12
45520185.2 V12
456DBX20194.0 V8 SUV
457International
458Lagonda1979-19905.3 Saloon
459Le Mans
460Mark II
461One-77
462Q-One-77
463Rapide2010-20136.0 Luxuary Edition
464V12 Saloon
465Rapide S20136.0 V12
466Ulster
467V12 Zagato
468V81978-19895.3 Vantage
4695.4 Volante
4701997-19995.3 Volante
4711993-19995.3 Coupe
4725.3 Limited Edition
4735.3 Vantage
4741975-19895.3 Saloon
4755.3 Vantage
476Vanquish2015-20165.9 V12
47720166.0 V12
4782001-20045.9 Coupe
4792004-20095.9 S
4802012-20165.9 V12
48120166.0 V12
482Vantage1986-19875.3 Zagato
4832007-20184.3 V8
4844.7 V8
4856.0 V12
4861986-19875.3 Zagato
4872005-20184.3 V8
4884.7 V8
4896.0 V12
49020174.0 V8
491Virage1991-19985.3 Volante
4922011-20126.0 V12
4931990-19935.3 Coupe
4942011-20126.0 V12
4951992-19955.3 Shooting
496Volante1987-19915.3 Zagato
497Vulcan
498AuburnSpeedster
499Audi1001983-19911.8 CC
5002.0 CC
5012.0 E
5022.0 SE
5032.0 TD
5042.1 CD
5052.2 Avant
5062.2 CD
5072.2 E
5082.2 Turbo
5092.3 Avant
5102.3 E
5112.3 SE
5121991-19952.0 E
5132.0 SE
5142.2 S4
5152.3 E
5162.5 TDI
5172.6 E
5182.6 SE
5192.6 Sport
5202.8 E
5211977-19822.1 CD5E
5222.1 CL
5232.1 GL
5242.1 GL5E
5252.1 L5S
5261982-19911.8 Saloon
5271.9 CC
5282.0 CC
5292.0 E
5302.0 SE
5312.0 Saloon
5322.0 TD
5332.1 CD
5342.1 CS
5352.2 CD
5362.2 E
5372.2 Saloon
5382.2 T
5392.3 E
5402.3 SE
5412.3 Saloon
5421991-19952.0 E
5432.0 SE
5442.2 S4
5452.3 E
5462.5 TDI
5472.6 E
5482.6 SE
5492.6 Sport
5502.8 E
551100 Avant1977-19821.6 L
5522.0 D
5532.1 CD5E
5542.1 GL5S
5552001985-19912.1 T
5562.2 T
5571983-19892.1 T
5582.2 E
5592.2 T
56060
56170
56275
563801992-19951.6 Estate
5641.6 SE
5651.9 TDI
5662.0 16v
5672.0 E
5682.0 SE
5692.2 S2
5702.6 E
5712.6 Estate
5722.6 SE
5731979-19861.6 CL
5741.6 GL
5751.6 GLE
5761.6 GLS
5771.6 LS
5781.6 TD
5791.8 CL
5801.8 GL
5811.8 Saloon
5821.8 Sport
5831.9 CD
5842.0 CD
5852.1 Saloon
5861986-19911.6 Saloon
5871.6 TD
5881.8 E
5891.8 S
5901.8 Saloon
5912.0 16v
5922.0 E
5932.0 Saloon
5942.0 Sport
595901984-19862.0 CD
5962.0 Saloon
5972.2 Saloon
5981987-19912.0 Saloon
5992.2 E
6002.2 Saloon
6012.3 20v
6022.3 E
6032.3 SE
6042.3 Saloon
605920
Tab1 - A sample of 1300 Lines
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Thank you, you set a good example.
Try the following. Results in G3

VBA Code:
Sub split_year()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, lr As Long
  Dim nMin As Double, nMax As Double, ini As Long, fin As Long
  Dim sh1 As Worksheet, sh2 As Worksheet
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Set sh1 = Sheets("Tab1")
  sh1.Copy after:=Sheets(Sheets.Count)
  Set sh2 = ActiveSheet

  lr = sh2.Range("A:D").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  nMin = Evaluate("=MIN(IF(C3:C" & lr & "<>"""",LEFT(C3:C" & lr & ",4)+0))")
  nMax = Evaluate("=MAX(IF(C3:C" & lr & "<>"""",RIGHT(C3:C" & lr & ",4)+0))")

  With sh2.Range("C3:C" & lr).SpecialCells(xlCellTypeBlanks)
    .Formula = "=IF($B" & .Cells(1).Row & "="""",C" & .Cells(1).Row - 1 & ","""")"
    sh2.Range("C3:C" & lr).Copy
    sh2.Range("C3").PasteSpecial xlPasteValues
  End With
  
  With sh2.Range("B3:B" & lr).SpecialCells(xlCellTypeBlanks)
    .Formula = "=IF($A" & .Cells(1).Row & "="""",B" & .Cells(1).Row - 1 & ","""")"
    sh2.Range("B3:B" & lr).Copy
    sh2.Range("B3").PasteSpecial xlPasteValues
  End With

  With sh2.Range("A3:A" & lr).SpecialCells(xlCellTypeBlanks)
    .Formula = "=A" & .Cells(1).Row - 1
    sh2.Range("A3:A" & lr).Copy
    sh2.Range("A3").PasteSpecial xlPasteValues
  End With
  
  Application.CutCopyMode = False
  a = sh2.Range("A3", Range("F" & lr)).Value2
  ReDim b(1 To UBound(a, 1) * nMax - nMin + 1, 1 To 4)
  
  For i = 1 To UBound(a, 1)
    If a(i, 3) <> "" Then
      If InStr(a(i, 3), "-") Then
        ini = Val(Split(a(i, 3), "-")(0))
        fin = Val(Split(a(i, 3), "-")(1))
      Else
        ini = a(i, 3)
        fin = a(i, 3)
      End If
    Else
      ini = 1
      fin = 1
    End If
    
    For j = ini To fin
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(i, 2)
      b(k, 3) = IIf(j = 1, "", j)
      b(k, 4) = a(i, 4)
    Next j
  Next i
  
  sh1.Range("G3").Resize(k, 4).Value = b
  sh2.Delete
  Application.ScreenUpdating = True

End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,268
Office Version
  1. 365
Platform
  1. Windows
Try the following.
Hi Dante
I was working on this last night but ran out of time. My suggestion was looking a bit like yours anyway so I won't go on with it for now. However, just a suggestion. I think you could raplace this whole section of code
VBA Code:
If InStr(a(i, 3), "-") Then
  ini = Val(Split(a(i, 3), "-")(0))
  fin = Val(Split(a(i, 3), "-")(1))
Else
  ini = a(i, 3)
  fin = a(i, 3)
End If
with this
VBA Code:
ini = Split(a(i, 3), "-")(0)
fin = Split(a(i, 3) & "-" & a(i, 3), "-")(1)
 

rameez788

New Member
Joined
Sep 8, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for your valuable input. After running the code i am having an issue on one of the line. Please see the attached screen shot.
 

Attachments

  • File.PNG
    File.PNG
    232.2 KB · Views: 10

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
What does the error message say?

What is the smallest year that could exist: 1975, 1950,1920?

And which is the biggest, the current year (2020)?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try this

VBA Code:
Sub split_year()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, lr As Long
  Dim nMin As Double, nMax As Double, ini As Long, fin As Long
  Dim sh1 As Worksheet, sh2 As Worksheet
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Set sh1 = Sheets("Tab1")
  sh1.Copy after:=Sheets(Sheets.Count)
  Set sh2 = ActiveSheet

  lr = sh2.Range("A:D").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  'nMin = Evaluate("=MIN(IF(C3:C" & lr & "<>"""",LEFT(C3:C" & lr & ",4)+0))")
  'nMax = Evaluate("=MAX(IF(C3:C" & lr & "<>"""",RIGHT(C3:C" & lr & ",4)+0))")

  With sh2.Range("C3:C" & lr).SpecialCells(xlCellTypeBlanks)
    .Formula = "=IF($B" & .Cells(1).Row & "="""",C" & .Cells(1).Row - 1 & ","""")"
    sh2.Range("C3:C" & lr).Copy
    sh2.Range("C3").PasteSpecial xlPasteValues
  End With
  
  With sh2.Range("B3:B" & lr).SpecialCells(xlCellTypeBlanks)
    .Formula = "=IF($A" & .Cells(1).Row & "="""",B" & .Cells(1).Row - 1 & ","""")"
    sh2.Range("B3:B" & lr).Copy
    sh2.Range("B3").PasteSpecial xlPasteValues
  End With

  With sh2.Range("A3:A" & lr).SpecialCells(xlCellTypeBlanks)
    .Formula = "=A" & .Cells(1).Row - 1
    sh2.Range("A3:A" & lr).Copy
    sh2.Range("A3").PasteSpecial xlPasteValues
  End With
  
  Application.CutCopyMode = False
  a = sh2.Range("A3", Range("F" & lr)).Value2
  ReDim b(1 To UBound(a, 1) * 80, 1 To 4)
  
  For i = 1 To UBound(a, 1)
    If a(i, 3) <> "" Then
      If InStr(a(i, 3), "-") Then
        ini = Val(Split(a(i, 3), "-")(0))
        fin = Val(Split(a(i, 3), "-")(1))
      Else
        ini = a(i, 3)
        fin = a(i, 3)
      End If
    Else
      ini = 1
      fin = 1
    End If
    
    For j = ini To fin
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(i, 2)
      b(k, 3) = IIf(j = 1, "", j)
      b(k, 4) = a(i, 4)
    Next j
  Next i
  
  sh1.Range("G3").Resize(k, 4).Value = b
  sh2.Delete
  Application.ScreenUpdating = True

End Sub
 

rameez788

New Member
Joined
Sep 8, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I would say smallest year can be 1970. and biggest one is 2020.
 

Attachments

  • msg.PNG
    msg.PNG
    3.5 KB · Views: 7

Watch MrExcel Video

Forum statistics

Threads
1,109,186
Messages
5,527,296
Members
409,758
Latest member
Smith79

This Week's Hot Topics

Top