Arrange the data by year and format.

[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.


Thanks,
Pesteness


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hWn2pGBe SS (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hWn2pGBe SS)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg.9hJRnEjxQrd9hJoCjomN I2 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg.9hJRnEjxQrd9hJoCjomN I2)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg.9hJOZEEZa6p9hJqLC7El-w (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg.9hJOZEEZa6p9hJqLC7El-w)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg.9hIlxxGY7t49hJsB2PWx C4 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg.9hIlxxGY7t49hJsB2PWx C4)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg.9hIKlNPeqDn9hJskm92n p6 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg.9hIKlNPeqDn9hJskm92n p6)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg.9hI2IGUdmTW9hJuyaQaw qx (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg.9hI2IGUdmTW9hJuyaQaw qx)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hJwTB9Jl ob (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hJwTB9Jl ob)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Rick Rothstein]

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]

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]

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]

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

[Rick Rothstein]

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]

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]

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]

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 :laugh: 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]

no no. i'm not fighting with you :laugh: 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?

[pesteness]

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?

I see, got it. :biggrin:

example this, Mercedes model - 190E 2.3 201.024 102.985 from 1984-1986

this should be look like:

Make=Mercedes Model|Model=190E|Year=1984|Notes=2.3 201.024 102.98
Make=Mercedes Model|Model=190E|Year=1985|Notes=2.3 201.024 102.98
Make=Mercedes Model|Model=190E|Year=1986|Notes=2.3 201.024 102.98

the 2.3 comes with the notes :)

Thank you.

[Rick Rothstein]

I see, got it. :biggrin:

example this, Mercedes model - 190E 2.3 201.024 102.985 from 1984-1986

this should be look like:

Make=Mercedes Model|Model=190E|Year=1984|Notes=2.3 201.024 102.98
Make=Mercedes Model|Model=190E|Year=1985|Notes=2.3 201.024 102.98
Make=Mercedes Model|Model=190E|Year=1986|Notes=2.3 201.024 102.98

the 2.3 comes with the notes :)

Thank you.

Okay, now what would the other three look like in the ACTUAL FILE (I do have a reason for asking about them also)?

[pesteness]

Okay, now what would the other three look like in the ACTUAL FILE (I do have a reason for asking about them also)?

yes, sure.

Make=Mercedes Model|Model=300CE Coupe E Class|Year=1993|Notes=124.052 104.992

Make=Mercedes Model|Model=300E E Class|Year=1993|Notes=124.032 104.992

Make=Mercedes Model|Model=300CD Turbo|Year=1982|Notes= 123.153 617.952
Make=Mercedes Model|Model=300CD Turbo|Year=1983|Notes= 123.153 617.952

just please disregard (3.2) the liters, i already know now what's your point. :biggrin:

Thanks,

[pesteness]

then this should be,

Make=Mercedes Model|Model=190E|Year=1984|Notes=201.024 102.98
Make=Mercedes Model|Model=190E|Year=1985|Notes=201.024 102.98
Make=Mercedes Model|Model=190E|Year=1986|Notes=201.024 102.98


Thanks,

[Rick Rothstein]

just please disregard (3.2) the liters, i already know now what's your point. :biggrin:

Actually, my point is that I cannot disregard the 3.2 because I won't be specifically looking for it. There is no way I can write a program that looks for specific things because there are probably too many of them for existing models and who knows what there will be for future models. The best I can do, in the absence of proper delimiters is to find the most common "shape" to your data (that is, the juxtaposition of numbers and letters) and try to break the text apart based on that. This is why I asked the question I did... because I do not know your product line at all (things about it that you take for granted are completely unknown to me). Okay, I think you have given me enough to work with, now let me digest it and then see if I can come up with a strategy to handle... that may take awhile, so please be patient with me.

[pesteness]

sure no problem, thanks for the help. god bless.