Discuss "Excel question" in the Microsoft Office forum.

Rebelle · #1 May 23rd, 2008, 03:26 PM

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

richyrich · #2 May 23rd, 2008, 03:30 PM

Do you mean during the export or just manually?

Rebelle · #3 May 23rd, 2008, 03:33 PM

Quote:

Originally Posted by richyrich

Do you mean during the export or just manually?

manually after i've got the data.

richyrich · #4 May 23rd, 2008, 03:43 PM

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.

Rebelle · #5 May 23rd, 2008, 03:47 PM

Quote:

Originally Posted by richyrich

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?

AOG123 · #6 May 27th, 2008, 04:59 AM

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

BLaaaaaaaaaarche · #7 May 27th, 2008, 08:24 AM

Or you can simply use the "text to columns" feature of Excel. I believe you can find that under Data -> Text to Columns.

AOG123 · #8 May 27th, 2008, 11:19 AM

Quote:

Originally Posted by BLaaaaaaaaaarche

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

BLaaaaaaaaaarche · #9 May 27th, 2008, 01:59 PM

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.

mehere · #10 May 27th, 2008, 02:03 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
C# database question	peebman2000	.Net Development	19	April 21st, 2008 03:23 PM

The Following 3 Users Say Thank You to AOG123 For This Useful Post:
jmurrayhead (May 27th, 2008), mehere (May 27th, 2008), Rebelle (May 27th, 2008)

Discuss "Excel question" in the Microsoft Office forum.

Microsoft Office - Learn helpful tips and tricks to get the best out of Office suite, using Excel, Word, PowerPoint and more. Discuss how to automate repetitive tasks and more.