Closed Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Excel question

  1. #1
    Barn Loyal Rebelle will become famous soon enough Rebelle's Avatar
    Join Date
    Mar 2008
    Posts
    868
    Rep Power
    3

    Excel question

    Ok, I export some data from another application where the data is layed out like so:

    desc. - pn - so - qty
    tool1 - 123 - so383-4
    tool2 - 223 - so383-3
    tool1 - 123 - so444-2
    tool4 - 334 - so444-3


    Is there a way to take this data and easy make rows according the the qty in excel? or maybe put it into access and have it spit out the rows.

    make it into:
    desc. - pn - so - qty
    tool1 - 123 - so383 -1
    tool1 - 123 - so383 -1
    tool1 - 123 - so383 -1
    tool1 - 123 - so383 -1
    tool2 - 223 - so383 -1
    tool2 - 223 - so383 -1
    tool1 - 123 - so444-1
    tool1 - 123 - so444-1
    tool4 - 334 - so444-1
    tool4 - 334 - so444-1
    tool4 - 334 - so444-1

  2. #2
    Administrator richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich's Avatar
    Join Date
    Mar 2008
    Location
    Somewhere only we know...
    Posts
    1,724
    Blog Entries
    10
    Rep Power
    11

    Do you mean during the export or just manually?

  3. #3
    Barn Loyal Rebelle will become famous soon enough Rebelle's Avatar
    Join Date
    Mar 2008
    Posts
    868
    Rep Power
    3

    Quote Originally Posted by richyrich View Post
    Do you mean during the export or just manually?
    manually after i've got the data.

  4. #4
    Administrator richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich's Avatar
    Join Date
    Mar 2008
    Location
    Somewhere only we know...
    Posts
    1,724
    Blog Entries
    10
    Rep Power
    11

    Go to Tools...Sort...

    You select whether you have a header row or not with the radio buttons..

    That'll either show the column headings in the dropdown if you have one or just Column A, B etc. if you don't.

    Then just select what you want to sort by and which way.

    Hope that helps.

  5. #5
    Barn Loyal Rebelle will become famous soon enough Rebelle's Avatar
    Join Date
    Mar 2008
    Posts
    868
    Rep Power
    3

    Quote Originally Posted by richyrich View Post
    Go to Tools...Sort...

    You select whether you have a header row or not with the radio buttons..

    That'll either show the column headings in the dropdown if you have one or just Column A, B etc. if you don't.

    Then just select what you want to sort by and which way.

    Hope that helps.
    But that doesn't make the rows? instead of having one row (qty4), i want to make that one row into 4.

    am i missing something?

  6. #6
    Lightning Master AOG123 is a jewel in the rough AOG123 is a jewel in the rough AOG123 is a jewel in the rough AOG123 is a jewel in the rough AOG123's Avatar
    Join Date
    Mar 2008
    Location
    Fortress Of Solitude
    Posts
    221
    Rep Power
    6

    Hi Rebelle,

    This is the best i can come up with,. i've attached an example for you to look at,.. long story short,. just make sure the value is held in column D,.. obviously you can change the range though

    Code:
        Dim lngRow As Long
        Dim intInsertRows As Integer
         
        Application.ScreenUpdating = False
        lngRow = 1
        Do Until IsEmpty(Range("D" & lngRow))
            If Range("D" & lngRow).Value > 1 Then
                intInsertRows = Range("D" & lngRow).Value - 1
                Range("D" & lngRow + 1 & ":D" & lngRow + intInsertRows).EntireRow.Insert
                Range("A" & lngRow & ":D" & (lngRow + intInsertRows)).FillDown
                Range("D" & lngRow & ":D" & (lngRow + intInsertRows)).Value = 1
                lngRow = lngRow + intInsertRows
            End If
            lngRow = lngRow + 1
         Loop
    End Sub
    
    Attached Files
    If i helped you, make me famous by clicking the

  7. #7
    Barn Frequenter BLaaaaaaaaaarche will become famous soon enough BLaaaaaaaaaarche will become famous soon enough BLaaaaaaaaaarche's Avatar
    Join Date
    Mar 2008
    Posts
    165
    Rep Power
    4

    Or you can simply use the "text to columns" feature of Excel. I believe you can find that under Data -> Text to Columns.
    "You'll never be as perfect as BLaaaaaaaaarche."

  8. #8
    Lightning Master AOG123 is a jewel in the rough AOG123 is a jewel in the rough AOG123 is a jewel in the rough AOG123 is a jewel in the rough AOG123's Avatar
    Join Date
    Mar 2008
    Location
    Fortress Of Solitude
    Posts
    221
    Rep Power
    6

    Quote Originally Posted by BLaaaaaaaaaarche View Post
    Or you can simply use the "text to columns" feature of Excel. I believe you can find that under Data -> Text to Columns.
    I can't see how that would work in this case,..

    Out of curiosity i had a look into your sugestion but couldn't get the results as required above,. So for the moment lets assume i'm losing the plot,

    I thought Text to Column command allows you to break text in one column into several columns using a delimiter such as <space> <hyphen> etc.

    I.e "delimiter being <space> in this case"

    Code:
    Cel 1
    Developer Barn
    
    Into

    Code:
    Cel 1          Cell 2
    Developer      Barn
    
    If i helped you, make me famous by clicking the

  9. #9
    Barn Frequenter BLaaaaaaaaaarche will become famous soon enough BLaaaaaaaaaarche will become famous soon enough BLaaaaaaaaaarche's Avatar
    Join Date
    Mar 2008
    Posts
    165
    Rep Power
    4

    With the data provided, all you have to do is the following:

    1. Copy and paste the values into Excel.
    2. Select column A.
    3. Click on Data -> Text to Columns
    4. Check 'Delimited'
    5. Check 'Other' and enter in '-' as your delimiter.
    6. Click next.
    7. If data looks formatted correctly, click Finish.

    I just did it and it works perfectly okay.
    "You'll never be as perfect as BLaaaaaaaaarche."

  10. #10
    Super Sarcasm Mistress mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere's Avatar
    Join Date
    Mar 2008
    Location
    Wide Awake In Dreamland
    Posts
    436
    Rep Power
    7

    but what she wants to do ... is take this line in excel:
    Code:
    tool1 - 123 - so383-4 (no dash delimiters ... she was showing the columns)
    
    and then paste it to 4 different lines in excel like so:
    Code:
    tool1 - 123 - so383 -1
    tool1 - 123 - so383 -1
    tool1 - 123 - so383 -1
    tool1 - 123 - so383 -1
    
    where the last number, in this case - 4, tells it how many times to write the data. i don't believe using your method will work for that.
    Quote of the Month:
    Leaders: Leaders are like eagles. We don't have either of them here.

    Questions to Ponder:
    Why do banks charge you a "non-sufficient funds fee" on money they already know you don't have?

    iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm")
    copyright © 2008 sbenj69

    Sarchasm: The gulf between the author of sarcastic wit and the person who doesn't get it.

Closed Thread
Page 1 of 2 1 2 LastLast

Similar Threads

  1. C# database question
    By peebman2000 in forum .Net Development
    Replies: 19
    Last Post: April 21st, 2008, 03:23 PM

Bookmarks

Posting Permissions

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

SEO by vBSEO