Help Me Sort Out a Compiled Database

[rilindoilario]

Dear Reader,

I need to simplify a worksheet before I can use it. Maybe I can use it as it is but I do not know how.

How can I change this:
Bui:Regal(1988-1996);Chev:Lumina(1990-2001),Monte Carlo(1995-1999);Olds:Cutlass Supreme(1988-1997);Pont:Grand Prix(1988-1996)
Into this:[TABLE="width: 335"]
<tbody>[TR]
[TD]Buick[/TD]
[TD]Regal[/TD]
[TD="align: right"]1988[/TD]
[TD="align: right"]1996[/TD]
[/TR]
[TR]
[TD]Chevrolet[/TD]
[TD]Lumina[/TD]
[TD="align: right"]1990[/TD]
[TD="align: right"]2001[/TD]
[/TR]
[TR]
[TD]Chevrolet[/TD]
[TD]Monte Carlo[/TD]
[TD="align: right"]1995[/TD]
[TD="align: right"]1999[/TD]
[/TR]
[TR]
[TD]Oldsmobile[/TD]
[TD]Cutlass Supreme[/TD]
[TD="align: right"]1988[/TD]
[TD="align: right"]1997[/TD]
[/TR]
[TR]
[TD]Pontiac[/TD]
[TD]Grand Prix[/TD]
[TD="align: right"]1988[/TD]
[TD="align: right"]1996[/TD]
[/TR]
</tbody><colgroup><col><col><col span="2"></colgroup>[/TABLE]
I have what is called a buyers guide and all twentyeight thousand rows look like that and I want to change all that into a more user friendly database I can use to build a "tree list" application catalog.

I am not THAT experienced in Excel but I can help myself. Do you have any sugestions? Let me know!

Thank you in advance for your cooperation.
-rilindo

 

[S.H.A.D.O.]

Just a quick suggestion as I am on my way out.
Have you tried Text To Columns, then you could possibly use Find & Replace for the rest, just an idea.
I hope this helps!

 

[GRTorres]

Does each row follow the exact same format as the line above? Starts with Make (actuall letters) and ends with the years in parenthesis?

 

[rilindoilario]

Just a quick suggestion as I am on my way out.
Have you tried Text To Columns, then you could possibly use Find & Replace for the rest, just an idea.
I hope this helps!

Thanks for replying.
No I haven't tried that yet. Is it a "time saving" solution? As I posted I have 28000+ of these examples for each part number.

I'll add three examples for you to take a look at:

Chev:Camaro(1988-1997),Corvette(1988-1996);Pont:Firebird(1988-1997)

BMW:525i(1989-1995),525iT(1992-1995),530i(1994-1995),530iT(1994-1995),535i(1989-1993),540i(1994-1995),735i(1988-1992),735iL(1988-1992),740i(1993-1994),740iL(1993-1994),750iL(1988-1994),M(1999),M3(1994-1999,2001-2004),M5(1991-1993),Z3(1998-2001)

ChevTrk:B6 <B6P042>(1983-1991),B7 <B7T042>(1993-2002),B7500(1999-2000),C50 <C5D042>(1983-1989),C50 <C5H042>(1990-1991),C60 <C6D042/62>(1983-1989),C6000 <C6D042/62>(1983-1989),C70 <C7D042/064>(1983-1989),Kodiak C5500 <Chevy Trk>(1997-2002),Kodiak C60 <C6H042>(1990-2002),Kodiak C70 <C7H042/064>(1990-2002),P4 <P4T042 Chevy Truck>(1979-1990),P6 <P6S042 Chevy Trk>(1992-1996),P6 <P6T042 Chevy Trk>(1983-1992),S7T042(1988-1991),T6 <F6B042 Chevy Trk>(1997-2002),T7 <F7B042 Chevy Truck>(1997-1998),T7 <F7B064 Chevy Truck>(1997-1998),T7500 < Chevy Trk>(1997-2002),W6 <Chevy Trk>(1996-1999);FordTrk:B600(1984-1989),B700(1984-1997),B7000(1984-1988),C600(1984-1986),C700(1984-1987),C7000(1984-1986),C800(1984-1987),CF600(1986-1989),CF700(1986-1989),CF800(1986-1992),F600(1984-1995),F650 Super Duty(2000-2003),F700(1984-1998),F7000(1984-1988),F750 Super Duty(2000-2003),F800(1984-1999),FT800(1983-1990),FT900(1984-1998),L800(1984),L8000(1984-1997),LN600(1984-1985),LN700(1984-1987),LN7000(1984-1997),LN800(1984,1986),LN8000(1984-1997),LNT800(1984),LS800(1984-1989),LT800(1984);GMC Trk.:B6000 <B6P042 GMC Trk>(1983-1991),B7000 <B7T042>(1993-2002),B7500(1999-2000),C5000 <C5D042>(1983-1989),C5000 <C5H042>(1990-1991),C6000 <C6D042/62>(1983-1989),C7000 <C7D042/64>(1983-1989),P4 <P4T042 GMC Trk>(1979-1993),P6 <P6S042 GMC Trk>(1991-1996),P6 <P6T042 GMC Trk>(1983-1991),S7T042(1988-1991),T6 <F6B042 GMC Trk>(1997-2002),T7 <F7B042 GMC Trk>(1997-2002),T7 <F7B064 GMC Trk>(1997-2002),T7500 <GMC Trk>(1997-1998),Topkick C5500 <GMC Trk>(1997-2002),Topkick C6000 <C6H042>(1990-2002),Topkick C7000 <C7H042/64>(1990-2002),W6 <GMC Trk>(1996-1999);IsuTrk:FSR(1996-2002),FTR(1996-2002),FVR(1996-2002)

Hope this helps to answer your question.

Thanks again.
-rilindo

 

[rilindoilario]

Does each row follow the exact same format as the line above? Starts with Make (actuall letters) and ends with the years in parenthesis?

Thanks for replying.

I'll add three examples for you to take a look at:

Chev:Camaro(1988-1997),Corvette(1988-1996);Pont:Firebird(1988-1997)

BMW:525i(1989-1995),525iT(1992-1995),530i(1994-1995),530iT(1994-1995),535i(1989-1993),540i(1994-1995),735i(1988-1992),735iL(1988-1992),740i(1993-1994),740iL(1993-1994),750iL(1988-1994),M(1999),M3(1994-1999,2001-2004),M5(1991-1993),Z3(1998-2001)

ChevTrk:B6 <b6p042>(1983-1991),B7 <b7t042>(1993-2002),B7500(1999-2000),C50 <c5d042>(1983-1989),C50 <c5h042>(1990-1991),C60 <c6d042 62="">(1983-1989),C6000 <c6d042 62="">(1983-1989),C70 <c7d042 064="">(1983-1989),Kodiak C5500 <chevy Trk="">(1997-2002),Kodiak C60 <c6h042>(1990-2002),Kodiak C70 <c7h042 064="">(1990-2002),P4
(1979-1990),P6
(1992-1996),P6
(1983-1992),S7T042(1988-1991),T6 <f6b042 Trk="" Chevy="">(1997-2002),T7 <f7b042 Truck="" Chevy="">(1997-1998),T7 <f7b064 Truck="" Chevy="">(1997-1998),T7500 < Chevy Trk>(1997-2002),W6 <chevy Trk="">(1996-1999);FordTrk:B600(1984-1989),B700(1984-1997),B7000(1984-1988),C600(1984-1986),C700(1984-1987),C7000(1984-1986),C800(1984-1987),CF600(1986-1989),CF700(1986-1989),CF800(1986-1992),F600(1984-1995),F650 Super Duty(2000-2003),F700(1984-1998),F7000(1984-1988),F750 Super Duty(2000-2003),F800(1984-1999),FT800(1983-1990),FT900(1984-1998),L800(1984),L8000(1984-1997),LN600(1984-1985),LN700(1984-1987),LN7000(1984-1997),LN800(1984,1986),LN8000(1984-1997),LNT800(1984),LS800(1984-1989),LT800(1984);GMC Trk.:B6000 <b6p042 Trk="" GMC="">(1983-1991),B7000 <b7t042>(1993-2002),B7500(1999-2000),C5000 <c5d042>(1983-1989),C5000 <c5h042>(1990-1991),C6000 <c6d042 62="">(1983-1989),C7000 <c7d042 64="">(1983-1989),P4
(1979-1993),P6
(1991-1996),P6
(1983-1991),S7T042(1988-1991),T6 <f6b042 Trk="" GMC="">(1997-2002),T7 <f7b042 Trk="" GMC="">(1997-2002),T7 <f7b064 Trk="" GMC="">(1997-2002),T7500 <gmc Trk="">(1997-1998),Topkick C5500 <gmc Trk="">(1997-2002),Topkick C6000 <c6h042>(1990-2002),Topkick C7000 <c7h042 64="">(1990-2002),W6 <gmc Trk="">(1996-1999);IsuTrk:FSR(1996-2002),FTR(1996-2002),FVR(1996-2002)

Hope this helps to answer your question.

Thanks again.
-rilindo</gmc></c7h042></c6h042></gmc></gmc></f7b064></f7b042></f6b042></p6t042></p6s042></p4t042></c7d042></c6d042></c5h042></c5d042></b7t042></b6p042></chevy></f7b064></f7b042></f6b042></p6t042></p6s042></p4t042></c7h042></c6h042></chevy></c7d042></c6d042></c6d042></c5h042></c5d042></b7t042></b6p042>

 

[GRTorres]

Looks like these are full lines without separation. Try using this to help alleviate some of the cluster, this will put everything in the format of:

Chev:Camaro(1988-1997)
Bui:Regal(1988-1990)
etc.

Make sure to change the name of the sheet names from Sheet1 and Sheet2 to whatever the data tabs are called. This wont do everything, but if you like this path it could help you out.

Sub SearchAndMove()

Application.ScreenUpdating = False

Set a = Sheets("Sheet1")
Set b = Sheets("Sheet2")
Dim spot As Integer
Dim name As String
Dim over As String
Dim leng As Long
Dim i As Long
Dim j As Long
i = 1

'Add semicolon at end of each break line for carry over
For i = 1 To a.Range("A" & Rows.Count).End(xlUp).Row
    a.Range("A" & i) = a.Range("A" & i) & ";"
Next i

'Loop thru each line until empty
i = 1
j = 1
For i = 1 To a.Range("A" & Rows.Count).End(xlUp).Row

Do Until IsEmpty(a.Range("A" & i))
     leng = Len(a.Range("A" & i))
     spot = InStr(1, a.Range("A" & i), ")", vbTextCompare)
     name = Left(a.Range("A" & i), spot)
     over = Right(a.Range("A" & i), leng - spot - 1)
     b.Range("A" & j) = name
     a.Range("A" & i) = over
j = j + 1
Loop

Next i

End Sub