Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Arrange the data by year and format.

  1. #1
    Junior Member
    Join Date
    Aug 2012
    Posts
    19
    Rep Power
    0
    Last edited by DocAElstein; 07-09-2023 at 10:33 PM.

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by pesteness View Post
    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?

  3. #3
    Junior Member
    Join Date
    Aug 2012
    Posts
    19
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    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

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by pesteness View Post
    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

  5. #5
    Junior Member
    Join Date
    Aug 2012
    Posts
    19
    Rep Power
    0
    yes that's okay, we'll just replace those mercesdes model.

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by pesteness View Post
    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.
    Last edited by Rick Rothstein; 08-19-2012 at 12:40 PM.

  7. #7
    Junior Member
    Join Date
    Aug 2012
    Posts
    19
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    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.

  8. #8
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by pesteness View Post
    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.

  9. #9
    Junior Member
    Join Date
    Aug 2012
    Posts
    19
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    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.

  10. #10
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by pesteness View Post
    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?

Similar Threads

  1. Test Copy Do you know when Easter is this year?
    By Rick Rothstein in forum Test Area
    Replies: 6
    Last Post: 08-01-2021, 08:23 PM
  2. Replies: 3
    Last Post: 04-05-2013, 08:24 AM
  3. Replies: 17
    Last Post: 12-18-2012, 04:15 PM
  4. Convert Text In YYYYMMDD Format To Date Format
    By S M C in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 02-28-2012, 12:04 AM
  5. Merry Christmas and Happy New Year
    By Admin in forum Greetings and Inception
    Replies: 1
    Last Post: 01-02-2012, 12:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •