1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Learning how to fully use Excel

Discussion in 'Business Applications' started by Kanai22, Mar 31, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Kanai22

    Kanai22 Thread Starter

    Joined:
    Mar 31, 2010
    Messages:
    12
    I am trying to figure out to automatically convert rows into columns. I know how to do it by transposing the information myself but is there anyway to set it up so that it converts automatically and still stays in the correct row? I have attached a copy of what I am working on. (all personal info blacked out) The section above the black line is what I need it to look like and below the black line is the info that I am starting with. I only have Excel 2003. I believe that I have Windows XP. It's a work computer and they have a lot of access blocked. Any help would be absolutly greatly appreciated!!:)
     

    Attached Files:

  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Your file is really a mess, with things as varied as words being split into 2 cells (see M20 and M21 for an example.)
    Also, to do a transposition properly, you need the same number of rows each time. Given that the most listings you have in this file is 17, someone will need to write some VBA to count down from one model name to the next and then insert enough lines between them to make a total of 25 (or whatever number makes sense to you.)
    Then the data can be transposed to 25 columns.
    Alas, I am not a VBA guy - I just kinda know what they need to do. But I will see if any are around.
     
  3. Kanai22

    Kanai22 Thread Starter

    Joined:
    Mar 31, 2010
    Messages:
    12
    I am pulling the information from an internal company program. So I have no idea how to fix the words that are broken up. The Program that I'm pulling the data from is called ERALink32 Windows. Any Ideas?
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Hi. :)

    I did a quick check using this formula:

    =LEN(M18)

    in N18 & copied downwards. It appears that the "cropping" occurs when the text length reaches 55 characters. But you couldn't use code like "if text = 55 characters, add the text from the cell below" because in some cases the text length might be exactly 55 characters.

    Do you have any ideas on that? :)
     
  5. Kanai22

    Kanai22 Thread Starter

    Joined:
    Mar 31, 2010
    Messages:
    12
    You are very right!! The text length is 55 characters. I have no clue how to change it in the program that I am pulling the info from. These are the recommended services for vehicles that have recently been serviced and our advisors write these notes for each vehicle. I am so stumped on how to get this wooksheet the way my boss wants it. So your help is very much appreciated!! Oh and were do I enter the formulas? I'm new at using these formulas.
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    See the attached.

    It would be possible to write code that bolts together the "cell groups" in column M except those where any of the cells (in a group) = 55 characters. You could probably even put in a switch -- have a prompt display like "cell xyz is 55 characters -- do you want to join it with the cell below, Yes/No?".

    I'll have another look in the morning (UK). :)
     

    Attached Files:

  7. Kanai22

    Kanai22 Thread Starter

    Joined:
    Mar 31, 2010
    Messages:
    12
    Thank you so much for all of your help!!!
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I made Sheet2 and Sheet3 into copies of Sheet1.

    Sheet2 is just to show you the TRANSPOSE (array) formula (N18:R18).

    Sheet3: I made a macro for Sheet3, the key combo to run it is CTRL+Shift+M.

    Select K18 on Sheet3, then press CTRL+Shift+M. M18:M22 will be transpose to N18:R18 -- rows 19:22 will be deleted -- cell O18 will be coloured red, which means "flagged for inspection" because it has 55 characters -- the next cell down will be selected for the next "pass".

    So in theory you can just keep pressing CTRL+Shift+M over and over till you get to the bottom, and then review the red cells. I have no idea how many of these you have to do. How about you?
     

    Attached Files:

  9. Kanai22

    Kanai22 Thread Starter

    Joined:
    Mar 31, 2010
    Messages:
    12
    As of right now I have 925 customers/vehicles. Thats just from 2/01/10-3/27/10. This is something that I will be doing frequently so the amount of customers/vehicles will differ each time.
     
  10. Kanai22

    Kanai22 Thread Starter

    Joined:
    Mar 31, 2010
    Messages:
    12
    Thank you again for your help!! That macro is absolutely perfect. So now I just need help with transfering it over to my worksheet that has all of my raw data. Any ideas?
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Would it be possible for you to import your data into Access rather than Excel? 55 characters is probably a limit from the other program, but possibly it is an artifact of the extraction process itself. In Access you could set the field length to be several hundred characters - or make the fields memo fields, which are nearly unlimited in size. Possibly that would eliminate the breakups.
     
  12. Kanai22

    Kanai22 Thread Starter

    Joined:
    Mar 31, 2010
    Messages:
    12
    Thank you very much! I will try that. :)
     
  13. Kanai22

    Kanai22 Thread Starter

    Joined:
    Mar 31, 2010
    Messages:
    12
    Bomb #21,
    Can you help me so that in the vehicle make column it strickly just says the make of the vehicle? Example; right now it says; FORD TRUCK and I want it to just say; FORD. Same with the other vehicle makes.
     
  14. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "That macro is absolutely perfect."

    Nope. Had to adjust it for row 62 (one action only, therefore no rows to delete).

    You can have the transposed "cell groups" overwrite column M by changing:

    ActiveCell.Offset(, 3).Resize(, Actions) = Application.Transpose(ActiveCell.Offset(, 2).Resize(Actions))

    to:

    ActiveCell.Offset(, 2).Resize(, Actions) = Application.Transpose(ActiveCell.Offset(, 2).Resize(Actions))

    and:

    For Each Cell In ActiveCell.Offset(, 3).Resize(, Actions)

    to:

    For Each Cell In ActiveCell.Offset(, 2).Resize(, Actions)

    "help me so that in the vehicle make column it strickly just says the make of the vehicle?"

    For that you can add:

    If InStr(ActiveCell, " ") <> 0 Then
    ActiveCell = Left(ActiveCell, InStr(ActiveCell, " ") - 1)
    End If


    immediately before:

    ActiveCell.Offset(1).Select

    Did you try slurpee's Access suggestion yet?
     

    Attached Files:

  15. Kanai22

    Kanai22 Thread Starter

    Joined:
    Mar 31, 2010
    Messages:
    12
    No I haven't. I have been working on something else and haven't had a chance yet. I will try it. Thank you so much for all of your help!! You have been a life savor!! :)
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/913873

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice