Searching for data and inserting new rows

Hi
I have a spreadsheet from one of our clothing suppliers, which I am going to 
import into our database for ordering.  The format is:
Part No. / Description / Price / Size
The size is given as a range (i.e M-XL) for each item of clothing

What I want to do is analyse the size range and extract what sizes are 
available, then insert new rows for each of these sizes
So, M-XL becomes:
Part No/ Description / Price / M
Part No / Description / Price / L
Part No / Description / Price / XL

I've got over 800 individual lines to do, so any help on automating this 
task would be helpful!!

Thanks in advance
0
Kev427 (4)
1/31/2005 3:23:03 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
454 Views

Similar Articles

[PageSpeed] 35

So each row becomes 4 (the existing and 3 more).

(I'm guessing that the existing row would already show S (small).  Then you want
to add M,L,XL.)

If that's close:

Option Explicit
Sub testme01()

    Dim wks As Worksheet
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim iRow As Long
    Dim myNewSizes As Variant
    Dim TotalNewSizes As Long
    Dim ColsToCopy As Long
    
    Set wks = Worksheets("sheet1")
    
    myNewSizes = Array("M", "L", "XL")
    TotalNewSizes = UBound(myNewSizes) - LBound(myNewSizes) + 1
    
    ColsToCopy = 3 'A:C
    
    With wks
        FirstRow = 2 'headers in row 1???
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        For iRow = LastRow To FirstRow Step -1
            .Rows(iRow + 1).Resize(TotalNewSizes).EntireRow.Insert
            .Cells(iRow + 1, "A").Resize(TotalNewSizes, ColsToCopy).Value _
                = .Cells(iRow, "A").Resize(1, ColsToCopy).Value
            .Cells(iRow + 1, ColsToCopy + 1).Resize(TotalNewSizes, 1).Value _
                = Application.Transpose(myNewSizes)
        Next iRow
    End With
    
End Sub


Kev427 wrote:
> 
> Hi
> I have a spreadsheet from one of our clothing suppliers, which I am going to
> import into our database for ordering.  The format is:
> Part No. / Description / Price / Size
> The size is given as a range (i.e M-XL) for each item of clothing
> 
> What I want to do is analyse the size range and extract what sizes are
> available, then insert new rows for each of these sizes
> So, M-XL becomes:
> Part No/ Description / Price / M
> Part No / Description / Price / L
> Part No / Description / Price / XL
> 
> I've got over 800 individual lines to do, so any help on automating this
> task would be helpful!!
> 
> Thanks in advance

-- 

Dave Peterson
0
ec357201 (5290)
1/31/2005 11:30:41 PM
Thanks for your help, Dave.  Greatly appreciated

"Dave Peterson" wrote:

> So each row becomes 4 (the existing and 3 more).
> 
> (I'm guessing that the existing row would already show S (small).  Then you want
> to add M,L,XL.)
> 
> If that's close:
> 
> Option Explicit
> Sub testme01()
> 
>     Dim wks As Worksheet
>     Dim FirstRow As Long
>     Dim LastRow As Long
>     Dim iRow As Long
>     Dim myNewSizes As Variant
>     Dim TotalNewSizes As Long
>     Dim ColsToCopy As Long
>     
>     Set wks = Worksheets("sheet1")
>     
>     myNewSizes = Array("M", "L", "XL")
>     TotalNewSizes = UBound(myNewSizes) - LBound(myNewSizes) + 1
>     
>     ColsToCopy = 3 'A:C
>     
>     With wks
>         FirstRow = 2 'headers in row 1???
>         LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
>         
>         For iRow = LastRow To FirstRow Step -1
>             .Rows(iRow + 1).Resize(TotalNewSizes).EntireRow.Insert
>             .Cells(iRow + 1, "A").Resize(TotalNewSizes, ColsToCopy).Value _
>                 = .Cells(iRow, "A").Resize(1, ColsToCopy).Value
>             .Cells(iRow + 1, ColsToCopy + 1).Resize(TotalNewSizes, 1).Value _
>                 = Application.Transpose(myNewSizes)
>         Next iRow
>     End With
>     
> End Sub
> 
> 
> Kev427 wrote:
> > 
> > Hi
> > I have a spreadsheet from one of our clothing suppliers, which I am going to
> > import into our database for ordering.  The format is:
> > Part No. / Description / Price / Size
> > The size is given as a range (i.e M-XL) for each item of clothing
> > 
> > What I want to do is analyse the size range and extract what sizes are
> > available, then insert new rows for each of these sizes
> > So, M-XL becomes:
> > Part No/ Description / Price / M
> > Part No / Description / Price / L
> > Part No / Description / Price / XL
> > 
> > I've got over 800 individual lines to do, so any help on automating this
> > task would be helpful!!
> > 
> > Thanks in advance
> 
> -- 
> 
> Dave Peterson
> 
0
Kev427 (4)
2/1/2005 7:15:01 AM
Reply:

Similar Artilces:

Publisher 2003, inserting information into labels
I have created a label template in Publisher 2003. There are six labels per page. The only item that will change on each label will be the serial number. I have tried to use the catelog merge to get the respective serial numbers on each of the labels. Unfortunately, that has not worked. Instead of getting six different serial numbers on six different labels per page, I get one serial number on six different labels on one page. Is there a way to get different six different serial numbers on six different labels on one page? You may have to run this through twice or setup your names ...

hyperlink open in new window
How do you create a hyperlink and have it open in a new window? refer to http://www.davidbartosik.com/pub2002/pub2002_13.htm -- David Bartosik - MS MVP for Publisher help: www.davidbartosik.com enter to win Pub 2003: www.davidbartosik.com/giveaway.aspx "sharin" <anonymous@discussions.microsoft.com> wrote in message news:2180BE5B-1783-4F9A-A167-D8E3BF34B3CC@microsoft.com... > How do you create a hyperlink and have it open in a new window? ...

Using insert to paste a row--how done in Excel 2007
Hi, In my old version of Excel, I could copy a row or chunk of rows, move to a new spot and use the "insert row" icon to insert the rows and paste it automatically. Now in Office 2007 it just inserts a row instead of what I have copied. I want it the old way! How do I do it? -- Thanks, PTweety R-click, Insert Copied Cells. pickytweety wrote: > Hi, > In my old version of Excel, I could copy a row or chunk of rows, move to a > new spot and use the "insert row" icon to insert the rows and paste it > automatically. Now in Office 2007 it just inserts a r...

CRM 4.0 Report Deployment
Hi When I deploy a custom report (through Web UI) and run it, no data is displayed. The report header, footer, etc, display, but there is no data. To simplify the problem I created a very simple report with no parameters, which selects all from filteredsystemuser, and the problem was the same. This is in a test VPC, the whole environment is contained in the VPC. I am developing/publishing/viewing as the CRM administrator. The steps I took were: - Created a new report in Business Intelligence Development Studio using Wizard (installed in VPC) - Created a new DataSet called dsTest with a new D...

Moving my Outlook files to new computer
How can I move my Outlook 2003 files (Contacts, Calendar, mail, etc) to a new computer? ...

New appointment, nothing happens
Hi, suddenly I'm no longer able to add appointments to my calendar. Nothing happens when trying. I also tried to sync my mobile calendar to it and it seems to sync just fine, but it will not add any new appointments. Any suggestions ? (outlook 2003, winxp pro) Thanks, Jorgen Sorry, just figured out the (possible) cause: I had a microsoft update pending, after innstallation and reboot everything is fine. Jorgen "JGH" <nospam@thanks.org> wrote in message news:eyVH5TpwGHA.1296@TK2MSFTNGP02.phx.gbl... > Hi, suddenly I'm no longer able to add appointments to...

Can I make a database in Word with Excel Data
I know this may be a "DUH" question, but... I am making a list of area dentists that I have copied and pasted of of the Dex website into Excel. I am wondering if I can take this inf and make a database in Word for printing address labels. Does anyon know if this can be done, and HOW?:confused -- Myn77 ----------------------------------------------------------------------- Myn777's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2958 View this thread: http://www.excelforum.com/showthread.php?threadid=49283 For help on Word mail merge using Excel as the...

New Project, Different Problem: AutoFilter?
I'm creating a database of outside trainers. Each trainer can handle multiple categories of training. It is essential that the training manager be able to create lists for specific categories as the need arises, as well as being able to do more standard sorts such as Name. Rather than making multiple entries for each Trainer (one for each category, which may reach 10+), I'd like to allow multiple entries in the Category column utilizing a key for each category. It seems that AutoFilter would be the ideal method for creating this db. Well, actually Advanced AutoFilter, since...

data validation
Is it possible to create a similar function like data validation, from which I can select multiple options? (Background: I need a list of many names, from which I case by case can select). Not really. Maybe you could use a listbox. I added a listbox from the Forms toolbar to a worksheet. I called it "List Box 1". I rightclicked on that listbox and chose Format Control. On the Control tab, I chose Selection Type of Multi. I also pointed at the range that held the values that go in that listbox. Then I added a button from the Forms toolbar (I click the button when I'm d...

Create a new instance of a class that is a reference
Hi all, I am trying to make a class that when you create a 2nd instance the second instance will be a reference to the first. Public Class ClassA Private Shared tableList As Hashtable Public myInt as integer = 0 Public Sub New() MyBase.New() InitializeValues() End Sub Private Function openTable() As Boolean If (tableList Is Nothing) Then tableList = New Hashtable() End If If (tableList.ContainsKey(TABLE)) Then SetReference(Me, tableList(TABLE)) OpenCount += 1 Else ...

Updateing data when closing forms
I am using MS Access 2000 I have a table that has several required fields. I developed a form to add records to the table. In addition to the table fields, the form has a form-close button constructed with the button wizard. When I enter a record that does not have the required information and close the form with the “Close Window” (X) button on the tool bar, the appropriate error message appears telling me that there is missing data. (Just what I want) When I use the close button on the form, the form closes with no message and without adding the record. How do I give the button on the f...

Graph Data Values
I am trying to create a pick and mix graph that shows forecast spen against actual spend by a selected business area. I somehow want to create a graph on the fly based on the selections fo example: If I choose company one and want to see the contracted data I want th graph to pick up the forcasted contracted Labour, Passthru and Othe and show the combined contracted actuals; See attached; and if i then choose company 2 then graph will pick up the releven ranges? HELP!! Attachment filename: help.zip Download attachment: http://www.excelforum.com/attachmen...

How do I import data from a SECURED website into Excel?
I can import data from a regular web page directly into Excel spreadsheet, but when I tried importing them from a secured site I encountered problems. Does anyone have any suggestions? ...

Conditionally inserting image
Hi there, Does anyone out there know if its possible in Excel (2002) to conditionally insert an image into a file based upon the results of formula. So for instance... If a2 > 100 then [insert red traffic light image] Else If a2 > 50 then [insert amber traffic light image] Else [insert green traffic light image] Im familiar with VBA and wonder if that holds the solution but either way Im not sure how to reference each image (& dont know how each image is stored)? Any advice greatly appreciated. Phil You don't need an image. You can simply use conditional formatting on...

Lookup Wizard changing Data Type
I am trying to make Lookups from Table Field to Table Field and generally it seems OK. However the LookupWizard is changing the Data Type from Text to Number (I guess it's looking at the ID?) Data picked from Combo Box 'looks' OK in Table view A Query view is asking for a number but displays the Text field value when a valid ID number is added. Viewing Results in FrontPage Database Wizard, and using hand coded .asp querie, it's showing the ID field value. I can see why... Any ideas? Merci. Yes, the wizard isn't the problem it's the use of Lookup Fields in ...

retrieving data from Great Plains via eConnect
Hi, I've been working with eConnect for the last two weeks and have had success with importing a variety of transactions into Great Plains. However, I'd like to query Great Plains on demand and have data returned to me. For example, given a certain field value (like customer name = "Smith"), I'd like to have all customer records returned that match that specified value. Is there any way to do this easily with eConnect? I'm not so interested in the Transaction Requester Service because that seems to be focused more on automatically returning records that have...

Unread search folder unavailable
Good Aftenoon, Could anybody help tell me why in Outlook 2007 on a Windows 7 machine I get the following error "The Operation Failed. An object could not be found." when clicking on the Unread Mail search folder and also why it is greyed out? This also happens for the Large Mail folder, but NOT the Categorized mail and For Follow-up Folders. Tom Can you create a new search folder with the same functions? See if this helps: http://www.officeforlawyers.com/outlook/sf.html -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://...

Please help with New Database Query
Newbie to New Database Query - Can you change a spreadsheet to a table if so how? I have a lot of spreadsheets that I would like to work with in New Database Query. Can this be done. Any help will be greatly appreciated. In general, if you select a range and give it a name....MS Query will recognize it as a data source: Example for data in Cells A1:Z500, with column headings (EmpID, FName, Lname, etc): Select A1:Z500 In the Names box (just above the Col_A heading) type rngMyData1 Press [Enter] Next, save your workbook. Then...Data>Import External Data>New Database Query When y...

Keeping a range constant when inserting rows
Hello, I'm trying to keep a range of cells constant within a function when I insert a row (e.g. average(a1:a6) becomes average(a1:a7) but I want it to keep the a1:a6 range). Even if I use absolute cell references ($a$1:$a$6), it doesn't help. I would greatly appreciate any ideas. Thanks, Jeff Jeff, In your formula, use: =AVERAGE(INDIRECT("A1:A6")) Absolute cell references (dollar signs) do one thing only: They keep any copies you make of the cell references from changing relatively as they're copied. They still change when the cells to which they refer are m...

How do I link data to a chart from multiple worksheets?
I've created a graph that I want to show data from multiple sheets in the workbook. How do I do that? There are limits to how you can combine data from different sheets. This article describes them. http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Donna" <Donna@discussions.microsoft.com> wrote in message news:7DF7D186-9426-4B54-A322-FE485FD20529@microsoft.com... > I've created a graph that I want to...

Extracting data from one sheet to another
I have a speadsheet with 390 rows and 80 columns. For each column, I wish to copy a range of cells and place them in rows so I can create a separate table for each item represented by the column. For instance, I would like to take the data from the range B10:B15 and place that into a range A1:E1, and then data from range C10:C15 and place into a range G1:K1, D10:D15 into I1:M1 and then B16:B21 into A2:E2, C16:C21 into G2:K2, etc, etc, etc I could do this manually, but it could take a while. I was wondering if there was an easier way to do this. Any help would be greatly appreciated. Thanks...

References omit formatting and return cell address
In two cases of references between worksheets, the formatting from the original cell does not appear in the cell that it is referenced to. Case 1: Worksheet 1, A1 contains a currency formatted number - $2,000 Worksheet 2, A1 references the Workhseet 1, A1 cell using the = sign, yet it returns 2000 (unless I manually reformat the Workksheet 2 cell to Currency Case 2: Worksheet 3, A1 contains an apartment # - e.g. 4 Worksheet 4, A1 references this cell but returns the cell address - Worksheet2,!A1' - rather than the number 4. I tried different formats for the number 4,...

publisher 2003 will not insert MS clipart #2
Error message - Publisher cannot insert this picture into the publication. The appropriate graphic converter is not available. For information on running Setup to install converters, press F1. "Pub 2003 will not insert MS cliipart" wrote: > Error message - Publisher cannot insert this picture into the publication. > The appropriate graphic converter is not available. For information on > running Setup to install converters, press F1. p.s. to this message, F1 provides no information and I already cleared cache in IE tools. Did not work. Clipart will not insert. &qu...

How can I insert a cell reference in a footer (eg for variable foo
Any ideas on how to do this? I'm trying to create a template with the doc reference number in the footer However, I'm trying to avoid users having to edit the footer (because this just wont get done). Hi only possible with VBA using an event procedure. e.g. put the following code in your workbook module for cell A1 Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In Me.Worksheets With wkSht.PageSetup .CenterFooter = wksht.range("A1").value End With Next wkSht End Sub -- Regards Frank Kabel Frankfurt, Ger...

Cannot add new account to Money 2006
Hi all, I've bought Money 2006 std at last week, and first I've tried to import my Money 2000 file. The Money 2006 said that the file is not importable. OK. I've tried to create new file, which was successfully created, but I cannot add any new account. I've always got the same error message: The operation cannot be performed. Details: Product: Money ID: obres:34 Source: 15.0 Version: 15.0 Symbolic name: errUnknown Message: This operation cannot be performed. Reason Usually caused by a corrupt file. User Operation Run the Money File Repair tool to fix th...