Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
automatic color change in cells using a drop down list | Excel Worksheet Functions | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) | |||
Adding Rows to Master Sheet | New Users to Excel | |||
Copying Rows when hiding other rows | Excel Worksheet Functions |