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]

Assuming data is on Sheet1

Sub MM1()
Dim i As Long
With Sheets("Sheet1")
    For i = 4 To Range("A" & Rows.Count).End(xlUp).Row
        .Range("A" & i & ":C" & i).Copy Destination:=Range("E" & Rows.Count).End(xlUp).Offset(1).Resize(.Range("C" & i).Value)
    Next i
End With
    Range("G4:G" & Range("G" & Rows.Count).End(xlUp).Row).Value = 1
End Sub

 

[Satya Ranjan]

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

I'll be having hundreds of lines in this format and I have to split them further.
Is there any way we can do this in excel?

Thanks[/QUOTE]

 

[Michael M]

Thats what my code does !!!
Is your source data in columns A to C ??

 

[Satya Ranjan]

Thats what my code does !!!
Is your source data in columns A to C ??

I'm sorry! I think I was not able to explain my requirement properly.

Consider the source Data is present on sheet 1 and there are 1000 lines. like below.

Item Qty
A 4
B 5
C 4
D 7

I want them to be splited in sheet 2 like below.

Item Qty
A 1
A 1
A 1
A 1
B 1
B 1
B 1
B 1
B 1
D 1
D 1
D 1
D 1
D 1
D 1
D 1

 

[bosco_yip]

I'm sorry! I think I was not able to explain my requirement properly.

Consider the source Data is present on sheet 1 and there are 1000 lines. like below.

Item Qty
A 4
B 5
C 4
D 7

I want them to be splited in sheet 2 like below.

Item Qty
A 1
A 1
A 1
A 1
B 1
B 1
B 1
B 1
B 1
D 1
D 1
D 1
D 1
D 1
D 1
D 1

Try,

1] Sheet 2 A2, formula copy down :

=LOOKUP(ROWS(A$1:A1),SUMIF(OFFSET(Sheet1!B$1,,,ROW($1:$1000),),"<>")+1,Sheet1!A$2:A$1000)&""

2] Sheet 2 B2, formula copy down :

=IF(A2<>"",1,"")

Regards

 

[Satya Ranjan]

Try,

1] Sheet 2 A2, formula copy down :

=LOOKUP(ROWS(A$1:A1),SUMIF(OFFSET(Sheet1!B$1,,,ROW($1:$1000),),"<>")+1,Sheet1!A$2:A$1000)&""

2] Sheet 2 B2, formula copy down :

=IF(A2<>"",1,"")

Regards

Thank you so much, we are almost there.
Now there is one more challenge we have to face.

I want the data in collated format means in stead of generating them one by one I want them in sets.


Source
A 3
B 3
C 3


Old requirement

A1
A1
A1
B1
B1
B1
C1
C1
C1


New Format required

A1
B1
C1
A1
B1
C1
A1
B1
C1

 

[Satya Ranjan]

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

Hi Friends,
I got some boring task from office. I have to do it everyday. So knowing the strength of excel I am looking for a solution so that I can make this task easier by the help of excel experts like you.

[/IMG]

Note: Source and output will be on same workbook and different sheets.
I know I will definitely get a solution in this platform. So Thanks in advance for him who will help me get out of this mess.

 

[Rick Rothstein]

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.

 

[Michael M]

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 ??