Biggest Challenge For Excel Experts

[Satya Ranjan]

Each line is to be repeated by the nos of qty and in each line qty will be 1.


Is there any way we can do this in excel?

Thanks

 

[Michael M]

Re: If you are not a super expert in excel then this question is not for you.

I guess one way would be....as long as quantities were consistent ......which is different to the other post you submitted

http://www.mrexcel.com/forum/excel-questions/930859-biggest-challenge-excel-experts.html

Select the Job required from column "A" then run the code...then select the next job...otherwise I can't see any logic to splitting them up.

Sub MM1()
Dim ans As Integer
ans = InputBox("please input qty amount for job !!")
counter = 1
With Sheets("Sheet3")
    Do While counter < ans + 1
    Selection.Copy Destination:=Range("E" & Rows.Count).End(xlUp).Offset(1)
    counter = counter + 1
    Loop
Range("G3:G" & Range("E" & Rows.Count).End(xlUp).Row).Value = 1
End With
End Sub

 

[pgujju143]

Re: If you are not a super expert in excel then this question is not for you.

Hi Mr Satya,

Try below code this code useful to you.

as per your above explanation your qty column qty reflect 4 and a,b,c are copy and pasted 4 time. that means you need to how much qty in qty column you need to paste this job code that time

Sorry my bad english

Sub Multiple_Qty()
Dim lRow As Long
Dim RepeatFactor As Variant


lRow = 1
Do While (Cells(lRow, "A") <> "")

RepeatFactor = Cells(lRow, "CP")
If ((RepeatFactor > 1) And IsNumeric(RepeatFactor)) Then

Range(Cells(lRow, "A"), Cells(lRow, "D")).Copy
Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor - 1, "D")).Select
Selection.Insert Shift:=xlDown

lRow = lRow + RepeatFactor - 1
End If

lRow = lRow + 1
Loop
End Sub

 

[Michael M]

Re: If you are not a super expert in excel then this question is not for you.

You code doesn't collate it simply creates

Excel 2007
	A
3	a
4	a
5	a
6	a
7	b
8	b
9	b
10	b
11	c
12	c
13	c
14	c
Sheet1

 

[Satya Ranjan]

Re: If you are not a super expert in excel then this question is not for you.

I have a question... how do we tell where on job ends and the next begins? I mean it is kind of obvious for the example you posted, but what if the price and quantity for the two jobs are the same, there would appear to be no way to tell the breakpoint between them from the data you posted.

Good Question! May be we can input a blank line in source data. If still problematic may be we can approach one by one.

I've given a better example on my next post, requesting you to follow the link and give your valuable solution there

Ref: http://www.mrexcel.com/forum/excel-...an-solve-problem-get-10-10-a.html#post4474654

 

[Satya Ranjan]

Re: If you are not a super expert in excel then this question is not for you.

Dear Friend,
I'm giving you a better example. pls follow the link and give your expert suggestion there.

http://www.mrexcel.com/forum/excel-...an-solve-problem-get-10-10-a.html#post4474654

 

[Satya Ranjan]

Re: If you are not a super expert in excel then this question is not for you.

Hi Mr Satya,

Try below code this code useful to you.

as per your above explanation your qty column qty reflect 4 and a,b,c are copy and pasted 4 time. that means you need to how much qty in qty column you need to paste this job code that time

Sorry my bad english

Sub Multiple_Qty()
Dim lRow As Long
Dim RepeatFactor As Variant


lRow = 1
Do While (Cells(lRow, "A") <> "")

RepeatFactor = Cells(lRow, "CP")
If ((RepeatFactor > 1) And IsNumeric(RepeatFactor)) Then

Range(Cells(lRow, "A"), Cells(lRow, "D")).Copy
Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor - 1, "D")).Select
Selection.Insert Shift:=xlDown

lRow = lRow + RepeatFactor - 1
End If

lRow = lRow + 1
Loop
End Sub

Pls give your expert suggestion for this expample
http://www.mrexcel.com/forum/excel-...an-solve-problem-get-10-10-a.html#post4474654

 

[Satya Ranjan]

Re: If you are not a super expert in excel then this question is not for you.

I guess one way would be....as long as quantities were consistent ......which is different to the other post you submitted

http://www.mrexcel.com/forum/excel-questions/930859-biggest-challenge-excel-experts.html

Select the Job required from column "A" then run the code...then select the next job...otherwise I can't see any logic to splitting them up.

Sub MM1()
Dim ans As Integer
ans = InputBox("please input qty amount for job !!")
counter = 1
With Sheets("Sheet3")
    Do While counter < ans + 1
    Selection.Copy Destination:=Range("E" & Rows.Count).End(xlUp).Offset(1)
    counter = counter + 1
    Loop
Range("G3:G" & Range("E" & Rows.Count).End(xlUp).Row).Value = 1
End With
End Sub

you are so kind and so helping. I am glad to have experts like you aside me.
pls give your solution for a better example on the below link
http://www.mrexcel.com/forum/excel-...an-solve-problem-get-10-10-a.html#post4474654

 

[Satya Ranjan]

Re: If you are not a super expert in excel then this question is not for you.

What will seperate job 1 from job 2,etc?
Will the qty's always be the same for each job, ie, A,B and C will always be the same Qty ??

may be we can put a blank row between two jobs or we can approach one by one job if not possible all at a time.

 

[Satya Ranjan]

Re: If you are not a super expert in excel then this question is not for you.

Dear Friends, Thank you for your suggestion. but still now my problem is not solved. This is may be insufficient of information in previous example.
requesting you to ignore all previous example and give your solution on basis of this example only.

This is the exact example of the case.

 

[Satya Ranjan]

Re: If you are not a super expert in excel then this question is not for you.

Dear Friends, Thank you for your suggestion. but still now my problem is not solved. This is may be insufficient of information in previous example.
requesting you to ignore all previous example and give your solution on basis of this example only.

This is the exact example of the case.