Arrange the data by year and format.

**pesteness** · 08-18-2012, 06:12 PM

Anyway, again thanks admin

**Rick Rothstein** · 08-18-2012, 10:56 PM

Originally Posted by pesteness

Anyway, again thanks admin

The main file will be the database and I want to arrange the data like in actual file and put every data on a worksheets. also, MPN # = Sheet name.

Basically i have so many files like that and its very hard for me to manually arrange the data.

I am not sure that there is a coded solution to your problem. Why? Among other things, because your data is not delimited well. The first problem I saw was a change in text description. Your Main File starts each cars description with "Mercedes model" whereas your Actual File shows you want that part to read Merceds Benz. I don't know what other car brands there may be, but to handle the switch in text, we would need to have a list of all possible starting text from the Main File and the equivalent text you want to use for it. That is probably doable since I'm sure the number of car brands is relatatively short. The big problem with your data, though, is the lack of any delimiters within the description itself. For example, consider these two lines from your Main File...

Mercedes model - 300CE Convertible 24 Valve E Class 124.051 104.980 from 1990-1992
Mercedes model - 300SL SL Class 129.061 104.981 from 1990-1993

The problem I am having is figuring out how to isolate the part I have highlighted in red. Obviously, it starts after the dash, but I see no "for sure" way to determine where it ends. I'm thinking maybe if there is a number, it is always first, so perhaps I can end at the next number I come to... that would work for the above. But then I wonder about this item which you did not give a "translation" for...

Mercedes model - 190E 2.3 201.024 102.985 from 1984-1986

Would the 2.3 belong with the 190E or with the 201.024etc. part? Perhaps if you answer that question and, if the 2.3 goes with the 201.024etc. part, then could provide some more examples of real data and their translation so we can get a feel for the variety of data you have to deal with?

**pesteness** · 08-18-2012, 11:55 PM

Originally Posted by Rick Rothstein

I am not sure that there is a coded solution to your problem. Why? Among other things, because your data is not delimited well. The first problem I saw was a change in text description. Your Main File starts each cars description with "Mercedes model" whereas your Actual File shows you want that part to read Merceds Benz. I don't know what other car brands there may be, but to handle the switch in text, we would need to have a list of all possible starting text from the Main File and the equivalent text you want to use for it. That is probably doable since I'm sure the number of car brands is relatatively short. The big problem with your data, though, is the lack of any delimiters within the description itself. For example, consider these two lines from your Main File...

Mercedes model - 300CE Convertible 24 Valve E Class 124.051 104.980 from 1990-1992
Mercedes model - 300SL SL Class 129.061 104.981 from 1990-1993

The problem I am having is figuring out how to isolate the part I have highlighted in red. Obviously, it starts after the dash, but I see no "for sure" way to determine where it ends. I'm thinking maybe if there is a number, it is always first, so perhaps I can end at the next number I come to... that would work for the above. But then I wonder about this item which you did not give a "translation" for...

Mercedes model - 190E 2.3 201.024 102.985 from 1984-1986

Would the 2.3 belong with the 190E or with the 201.024etc. part? Perhaps if you answer that question and, if the 2.3 goes with the 201.024etc. part, then could provide some more examples of real data and their translation so we can get a feel for the variety of data you have to deal with?

Hi Rick, thanks for the response.

Anyway the 2.3 is actually the liters, and it belongs from where that right now. we can just forget the mercedes model to mercedes-benz, if you could provide a code with "mercedes model" and that is fine, i think we can find and replace that to "mercedes-benz"

i can send to you the whole file so you could see the other brands, unfortunately i dont know why it can't attached here, (just 168kb)

Thank you,
Pesteness

**Rick Rothstein** · 08-19-2012, 12:20 AM

Originally Posted by pesteness

Anyway the 2.3 is actually the liters, and it belongs from where that right now.

Sorry, but the highlighted part seems to have gotten garbled during posting. Let's see if there is going to be a real problem parsing the model from the notes. Here are some entries from your MAIN FILE, show me what they would have looked if you had included them in the ACTUAL FILE sample you posted...

Mercedes model - 300CE Coupe E Class 124.052 104.992 from 1993-1993
Mercedes model - 300E 3.2 E Class 124.032 104.992 from 1993-1993
Mercedes model - 190E 2.3 201.024 102.985 from 1984-1986
Mercedes model - 300CD Turbo 123.153 617.952 from 1982-1983

**pesteness** · 08-19-2012, 11:54 AM

yes that's okay, we'll just replace those mercesdes model.

**Rick Rothstein** · 08-19-2012, 12:27 PM

Originally Posted by pesteness

yes that's okay, we'll just replace those mercesdes model.

I would like you to show me what they are supposed to look like after being processed... that information will guide me in which approach to take in the code I write. Just so you know, I am not asking you what to do with those exact models, rather, I am more interested in their "structure" so I wil be able to decide on how to handle similarly "shaped" models.

**pesteness** · 08-19-2012, 01:12 PM

Originally Posted by Rick Rothstein

I would like you to show me what they are supposed to look like after being processed... that information will guide me in which approach to take in the code I write. Just so you know, I am not asking you what to do with those exact models, rather, I am more interested in their "structure" so I wil be able to decide on how to handle similarly "shaped" models.

in the actual file, the second sheet. that's i wanted to be the output.

**Rick Rothstein** · 08-19-2012, 01:34 PM

Originally Posted by pesteness

in the actual file, the second sheet. that's i wanted to be the output.

Sigh! I am not sure why you are "fighting" me on this (after all, you asked for the help). The items I asked you about were not covered in your sample data in the Actual File... that is why I asked you to show me what they should look like (remember, I am looking at the "structure" of those models and how you would actually handle them). I think you may be thinking this should be easier to write code for then it actually will be... while I think the code won't be too long, how you handle the 4 models I asked you about is critical to me in deciding on how to write that code.

**pesteness** · 08-19-2012, 01:58 PM

Originally Posted by Rick Rothstein

Sigh! I am not sure why you are "fighting" me on this (after all, you asked for the help). The items I asked you about were not covered in your sample data in the Actual File... that is why I asked you to show me what they should look like (remember, I am looking at the "structure" of those models and how you would actually handle them). I think you may be thinking this should be easier to write code for then it actually will be... while I think the code won't be too long, how you handle the 4 models I asked you about is critical to me in deciding on how to write that code.

no no. i'm not fighting with you

why do i do that i'm the one asking for favor, i'm sorry i just dont really understand what you want.

**Rick Rothstein** · 08-19-2012, 02:22 PM

Originally Posted by pesteness

no no. i'm not fighting with you

why do i do that i'm the one asking for favor, i'm sorry i just dont really understand what you want.

Make believe your MAIN FILE had only this in it...

Mercedes model - 300CE Coupe E Class 124.052 104.992 from 1993-1993
Mercedes model - 300E 3.2 E Class 124.032 104.992 from 1993-1993
Mercedes model - 190E 2.3 201.024 102.985 from 1984-1986
Mercedes model - 300CD Turbo 123.153 617.952 from 1982-1983

What would the ACTUAL FILE for them look like?