Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and instance of "X" and all rows between the next instance of "X"
Column A contains the text "Root" and then rows of data before the next
"Root" in column A. I need to find the first instance of "Root" copy that row and all other rows below it until the next "Root" is found. These copied rows need to go into a new worksheet and have the worksheet renamed to the same thing as found in Column C of the row that "Root" came from. The range that "Root" can be found is A2:A5000 Example: A 1 2 3 4 Root 5 6 7 8 9 10 11 12 13 Root 14 15 16 17 Root In the above example, I need Rows A4 - A12 copied to a new worksheet and the worksheet name changed to the content of C4 -Then- Row A13 - A16 copied to new worksheet and worksheet name changed to content of C13 Loop until all of the data in the master worksheet called "Monday Project Report" has been accounted for. Note: Row 1 contains column titles Columns used = A-J Thank you in advance for any help provided. I searched for quite some time on this form and was suprised I could not find anything similar to this request that I could modify. Regards, Mark. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and instance of "X" and all rows between the next instance of
Pilotflyby,
Consider the following... 1. In Col K, title it "Related Records" and insert the following formula =IF(RIGHT(A2,4)="Root",A2,K3) for all record rows. I think you will get the general idea of the formula. Modify it as needed to fit your case. This will give your records a key that ties them together. Copy & paste col K as values. 2. determine all the unique keys in the Related Records column 3. create a loop using the Related Records column 4. Autofilter the Related Records column for the 1st value (ex. 4 Root) 5. Copy & paste visible cells to desired location 6. Delete Column K 7. Save w/b or any other steps you need to perform 8. Continue until completed The key is to create a relationship between the various rows. Hope this gives you a starting point. -- Thx MSweetG222 "Pilotflyby" wrote: Column A contains the text "Root" and then rows of data before the next "Root" in column A. I need to find the first instance of "Root" copy that row and all other rows below it until the next "Root" is found. These copied rows need to go into a new worksheet and have the worksheet renamed to the same thing as found in Column C of the row that "Root" came from. The range that "Root" can be found is A2:A5000 Example: A 1 2 3 4 Root 5 6 7 8 9 10 11 12 13 Root 14 15 16 17 Root In the above example, I need Rows A4 - A12 copied to a new worksheet and the worksheet name changed to the content of C4 -Then- Row A13 - A16 copied to new worksheet and worksheet name changed to content of C13 Loop until all of the data in the master worksheet called "Monday Project Report" has been accounted for. Note: Row 1 contains column titles Columns used = A-J Thank you in advance for any help provided. I searched for quite some time on this form and was suprised I could not find anything similar to this request that I could modify. Regards, Mark. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and instance of "X" and all rows between the next instance of
Thank you MSweetG222 for your suggestion. I will work with your idea this
weekend and see what happens. -Mark. MSweetG222 wrote: Pilotflyby, Consider the following... 1. In Col K, title it "Related Records" and insert the following formula =IF(RIGHT(A2,4)="Root",A2,K3) for all record rows. I think you will get the general idea of the formula. Modify it as needed to fit your case. This will give your records a key that ties them together. Copy & paste col K as values. 2. determine all the unique keys in the Related Records column 3. create a loop using the Related Records column 4. Autofilter the Related Records column for the 1st value (ex. 4 Root) 5. Copy & paste visible cells to desired location 6. Delete Column K 7. Save w/b or any other steps you need to perform 8. Continue until completed The key is to create a relationship between the various rows. Hope this gives you a starting point. Column A contains the text "Root" and then rows of data before the next "Root" in column A. I need to find the first instance of "Root" copy that [quoted text clipped - 43 lines] Mark. -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Last Instance of "Text" in a column | Excel Discussion (Misc queries) | |||
Find Last Instance of "Text" in a column | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) |