Drop subsequent ref rows if(condition)
 

I would like the specifics of 10 rows in sheet 1 to be copied into rows in
sheet 2. Column A will contain the # of items the remainder of row
describes. (a1="25 pcs", b1=blue, c1=widget). If the number of "pcs" in a1
exceeds 25 pcs I would like for the info in row 1 to not only show in row 1
but also row 2. I've got it working for first 3 rows in sheet 2 but after
that with all different scenarios becoming more difficult. Is there a
function that will help simplify this process so that all the subsequent rows
will drop down one row if one or multiple orders exceed 25 pieces? I have
tried to simplify as much as possible.

Thanks Sincerely, Steven

One way

Assume data below is in Sheet1
cols A to C, from row1 down

(the phrase "pcs" is assumed input
together with the number in col A)

25 pcs blue widget1
26 pcs red widget2
27 pcs yellow widget3
24 pcs blue widget4
29 pcs brown widget5

Use a helper col to the right, say col E

Put in E1:
=IF(A1="","",IF(SUBSTITUTE(A1,"pcs","")+025,ROW() ,""))
Copy down to say, E1000, to cover max expected data in cols A to C

In Sheet2
------
Put in A1:

=IF(ISERROR(SMALL(Sheet1!$E:$E,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))

Copy A1 across to C1, fill down to C1000
(cover the same range as in Sheet1)

The above will return what you're after

For the sample data, you'll obtain:

26 pcs red widget2
27 pcs yellow widget3
29 pcs brown widget5
(rest are blanks: "")

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
StevenL wrote in message
...
I would like the specifics of 10 rows in sheet 1 to be copied into rows in
sheet 2. Column A will contain the # of items the remainder of row
describes. (a1="25 pcs", b1=blue, c1=widget). If the number of "pcs" in
a1
exceeds 25 pcs I would like for the info in row 1 to not only show in row
1
but also row 2. I've got it working for first 3 rows in sheet 2 but after
that with all different scenarios becoming more difficult. Is there a
function that will help simplify this process so that all the subsequent
rows
will drop down one row if one or multiple orders exceed 25 pieces? I have
tried to simplify as much as possible.

Thanks Sincerely, Steven

Max, your solution worked like a charm.

Thanks Much

"Max" wrote:

One way

Assume data below is in Sheet1
cols A to C, from row1 down

(the phrase "pcs" is assumed input
together with the number in col A)

25 pcs blue widget1
26 pcs red widget2
27 pcs yellow widget3
24 pcs blue widget4
29 pcs brown widget5

Use a helper col to the right, say col E

Put in E1:
=IF(A1="","",IF(SUBSTITUTE(A1,"pcs","")+025,ROW() ,""))
Copy down to say, E1000, to cover max expected data in cols A to C

In Sheet2
------
Put in A1:

=IF(ISERROR(SMALL(Sheet1!$E:$E,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))

Copy A1 across to C1, fill down to C1000
(cover the same range as in Sheet1)

The above will return what you're after

For the sample data, you'll obtain:

26 pcs red widget2
27 pcs yellow widget3
29 pcs brown widget5
(rest are blanks: "")

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
StevenL wrote in message
...
I would like the specifics of 10 rows in sheet 1 to be copied into rows in
sheet 2. Column A will contain the # of items the remainder of row
describes. (a1="25 pcs", b1=blue, c1=widget). If the number of "pcs" in
a1
exceeds 25 pcs I would like for the info in row 1 to not only show in row
1
but also row 2. I've got it working for first 3 rows in sheet 2 but after
that with all different scenarios becoming more difficult. Is there a
function that will help simplify this process so that all the subsequent
rows
will drop down one row if one or multiple orders exceed 25 pieces? I have
tried to simplify as much as possible.

Thanks Sincerely, Steven

You're welcome, Steve!
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"SteveT" wrote in message
...
Max, your solution worked like a charm.

Thanks Much