View original (French)

Need to copy a line x times according to quantities in the column.

Solved
lamuccapazza Posted messages 4 Status Member -  
lamuccapazza Posted messages 4 Status Member -
Hello,

I hope you can help me.
I'm not really an expert in Excel macros.
I have a client who is asking me to copy a row x times based on the quantity ordered in an Excel file.
That is to say:
the client ordered the item BLH2455FA in 12 quantities, I need to have 12 consecutive rows
then they ordered the item BLH2510F 8 times, I need to have 8 rows
Is there a simple formula to ask EXCEL to insert the desired number of rows based on the quantities indicated in the column and below the relevant row?
Attached is the file in question.
Any help would be appreciated!

4 answers

  1.
    Zoul67 Posted messages 2001 Status Member 149
     
    Hello,

    I would have liked to propose a macro, but if you are allergic to it:
    https://www.cjoint.com/c/EJgoX2NmxkJ
    in the frames there are formulas and conditional formatting.

    See you!
    0
  2. lamuccapazza Posted messages 4 Status Member
     
    Hello Zoul67
    I don't understand how to use the formula???
    =IF(H8>1,H8-1,0)
    I don't understand how to apply it
    can you give me more explanations or, if not, the macro...

    thank you anyway for your quick response and your help
    0
    1. Zoul67 Posted messages 2001 Status Member 149
       
      There is no macro (and a macro is not magical either).
      My idea is that in column H we list the number of rows to fill, so for each row we decrement. It works in combination with the formulas in column G.
      If you input a new item, you overwrite the existing formulas and the new item is copied n times.
      0
  3. lamuccapazza Posted messages 4 Status Member
     
    Ok
    I found this formula
    Sub dupLigne()
    Rows("1:1").Copy
    Rows("2:" & 1 + [F1]).Insert Shift:=xlDown
    End Sub
    that works halfway.
    Every time, I have +1 extra line and I have to copy and paste the processed reference -1 line in another page, so that the next one can be processed too.
    Do you have a trick to process all the lines at the same time?
    Thanks a lot ;-)
    0
    1. Zoul67 Posted messages 2001 Status Member 149
       
      Pffffffffff, copying and pasting from one page to another for something like this... Might as well pull the designation over the right number of lines.
      I prefer the formulas from my first response!
      0
  4. ccm81 Posted messages 11033 Status Member 2 434
     
    Hello to both of you

    A little example of what we can do with a macro
    http://www.cjoint.com/c/EJgq2OKZr8T

    Best regards
    0
    1. Zoul67 Posted messages 2001 Status Member 149
       
      Good evening ccm81,

      I hesitated to create a macro given the warning in the question... but I like your code.
      Note: Application.ScreenUpdating = True at the end is optional.

      See you!
      0
    2. lamuccapazza Posted messages 4 Status Member
       
      ok thanks to Zoul 67 & ccm81
      0