DeveloperBarn Forums

DeveloperBarn

Programming & IT forum

Excel question

This is a discussion on Excel question within the Microsoft Office forums, part of the Operating Systems, Servers & Software category; Ok, I export some data from another application where the data is layed out like so: desc. - pn - ...

Go Back   DeveloperBarn Forums > Operating Systems, Servers & Software > Microsoft Office

Excel question Excel question

Register FAQ Social Groups Calendar Search Today's Posts Mark Forums Read

Closed Thread
Page 1 of 2 1 2

 

LinkBack Thread Tools Display Modes
  #1  
Old May 23rd, 2008, 03:26 PM
Rebelle's Avatar
Barn Loyal
  
Join Date: Mar 2008
Posts: 748
Rep Power: 2
Rebelle will become famous soon enough
Red face 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  
Old May 23rd, 2008, 03:30 PM
richyrich's Avatar
Administrator
  
Join Date: Mar 2008
Real name: Rich
Location: Somewhere only we know...
Posts: 1,342
Blog Entries: 6
Rep Power: 8
richyrich is a name known to allrichyrich is a name known to allrichyrich is a name known to allrichyrich is a name known to allrichyrich is a name known to allrichyrich is a name known to all
Default
Do you mean during the export or just manually?
  #3  
Old May 23rd, 2008, 03:33 PM
Rebelle's Avatar
Barn Loyal
  
Join Date: Mar 2008
Posts: 748
Rep Power: 2
Rebelle will become famous soon enough
Default
Quote:
Originally Posted by richyrich View Post
Do you mean during the export or just manually?
manually after i've got the data.
  #4  
Old May 23rd, 2008, 03:43 PM
richyrich's Avatar
Administrator
  
Join Date: Mar 2008
Real name: Rich
Location: Somewhere only we know...
Posts: 1,342
Blog Entries: 6
Rep Power: 8
richyrich is a name known to allrichyrich is a name known to allrichyrich is a name known to allrichyrich is a name known to allrichyrich is a name known to allrichyrich is a name known to all
Default
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  
Old May 23rd, 2008, 03:47 PM
Rebelle's Avatar
Barn Loyal
  
Join Date: Mar 2008
Posts: 748
Rep Power: 2
Rebelle will become famous soon enough
Default
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  
Old May 27th, 2008, 04:59 AM
AOG123's Avatar
Lightning Master
  
Join Date: Mar 2008
Location: Fortress Of Solitude
Posts: 218
Rep Power: 5
AOG123 is a jewel in the roughAOG123 is a jewel in the roughAOG123 is a jewel in the roughAOG123 is a jewel in the rough
Default
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
File Type: zip Excel Append Records Based on Value.zip (7.7 KB, 4 views)
__________________
If i helped you, make me famous by clicking the

Status: Currently Unemployed - Looking for Work, Can be contacted on thethresher@hotmail.co.uk
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)
  #7  
Old May 27th, 2008, 08:24 AM
BLaaaaaaaaaarche's Avatar
Barn Frequenter
  
Join Date: Mar 2008
Posts: 157
Rep Power: 3
BLaaaaaaaaaarche will become famous soon enoughBLaaaaaaaaaarche will become famous soon enough
Default
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  
Old May 27th, 2008, 11:19 AM
AOG123's Avatar
Lightning Master
  
Join Date: Mar 2008
Location: Fortress Of Solitude
Posts: 218
Rep Power: 5
AOG123 is a jewel in the roughAOG123 is a jewel in the roughAOG123 is a jewel in the roughAOG123 is a jewel in the rough
Default
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
  #9  
Old May 27th, 2008, 01:59 PM
BLaaaaaaaaaarche's Avatar
Barn Frequenter
  
Join Date: Mar 2008
Posts: 157
Rep Power: 3
BLaaaaaaaaaarche will become famous soon enoughBLaaaaaaaaaarche will become famous soon enough
Default
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.
  #10  
Old May 27th, 2008, 02:03 PM
mehere's Avatar
Super Sarcasm Mistress
  
Join Date: Mar 2008
Real name: Joanne
Location: Wide Awake In Dreamland
Posts: 375
Rep Power: 6
mehere is just really nicemehere is just really nicemehere is just really nicemehere is just really nicemehere is just really nice
Default
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:
Mistakes: It could be that the purpose of your life is only to serve as a warning to others.

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

  DeveloperBarn Forums > Operating Systems, Servers & Software > Microsoft Office

Bookmarks

« Previous Thread | Next Thread »
Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads

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


All times are GMT -4. The time now is 06:21 PM.

Contact Us - DeveloperBarn.com - Top

Copyright ©2008-2010, DeveloperBarn

Content Relevant URLs by vBSEO 3.3.2