| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
PULLING OUT CONSECUTIVE ROWS
The cells in Col A contain either "NBR" or "STATUS" or "ITEM".
For example: NBR STATUS NBR STATUS NBR < STATUS < ITEM < NBR < STATUS < ITEM < NBR STATUS NBR STATUS NBR STATUS NBR STATUS NBR STATUS NBR < STATUS < ITEM < NBR STATUS NBR STATUS NBR < STATUS < ITEM < How can I pull out only the CONSECUTIVE cells containing "NBR" and "STATUS" and "ITEM"? (In my example, I've marked these cells with " < "). |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
PULLING OUT CONSECUTIVE ROWS
Try using an Advanced Filter:
With Your list beginning in A5 A4: Category (the column heading) A1: Test (or any text that is not a column heading from the list) A2: =IF(A5="NBR",AND(A6="STATUS",A7="ITEM"),IF(A5="STA TUS",AND(A4="NBR",A6="ITEM"),IF(A5="ITEM",AND(A3=" NBR",A4="STATUS"),FALSE))) Note1: in case text wrap impacts the display, there are NO spaces in that formula. Note2: that formula refers to the 1st DATA CELL in the List Range. NOT the column heading (Category). From the Excel main menu: <data<filter<advanced filter List Range: $A$4:$C$34 Criteria Range: $A$1:$A$2 Click the [OK] button That will return only consecutive NBR, STATUS, ITEM values. If you want the list of matching items returned to a different range... Before starting the Advanced Filter, copy the coumn headings from the list to the destination range. Then use that range in the Copy To field of the Advanced Filter dialog. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "GARY" wrote: The cells in Col A contain either "NBR" or "STATUS" or "ITEM". For example: NBR STATUS NBR STATUS NBR < STATUS < ITEM < NBR < STATUS < ITEM < NBR STATUS NBR STATUS NBR STATUS NBR STATUS NBR STATUS NBR < STATUS < ITEM < NBR STATUS NBR STATUS NBR < STATUS < ITEM < How can I pull out only the CONSECUTIVE cells containing "NBR" and "STATUS" and "ITEM"? (In my example, I've marked these cells with " < "). |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
PULLING OUT CONSECUTIVE ROWS
In case you're interested in the Advanced Filter method I posted, this
criteria is a bit easier to follow: A2: =OR(AND(A5="NBR",A6="STATUS",A7="ITEM"),AND(A5="ST ATUS",A4="NBR",A6="ITEM"),AND(A5="ITEM",A3="NBR",A 4="STATUS")) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try using an Advanced Filter: With Your list beginning in A5 A4: Category (the column heading) A1: Test (or any text that is not a column heading from the list) A2: =IF(A5="NBR",AND(A6="STATUS",A7="ITEM"),IF(A5="STA TUS",AND(A4="NBR",A6="ITEM"),IF(A5="ITEM",AND(A3=" NBR",A4="STATUS"),FALSE))) Note1: in case text wrap impacts the display, there are NO spaces in that formula. Note2: that formula refers to the 1st DATA CELL in the List Range. NOT the column heading (Category). From the Excel main menu: <data<filter<advanced filter List Range: $A$4:$C$34 Criteria Range: $A$1:$A$2 Click the [OK] button That will return only consecutive NBR, STATUS, ITEM values. If you want the list of matching items returned to a different range... Before starting the Advanced Filter, copy the coumn headings from the list to the destination range. Then use that range in the Copy To field of the Advanced Filter dialog. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "GARY" wrote: The cells in Col A contain either "NBR" or "STATUS" or "ITEM". For example: NBR STATUS NBR STATUS NBR < STATUS < ITEM < NBR < STATUS < ITEM < NBR STATUS NBR STATUS NBR STATUS NBR STATUS NBR STATUS NBR < STATUS < ITEM < NBR STATUS NBR STATUS NBR < STATUS < ITEM < How can I pull out only the CONSECUTIVE cells containing "NBR" and "STATUS" and "ITEM"? (In my example, I've marked these cells with " < "). |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Rows & Columns in Excel | Excel Worksheet Functions | |||
| Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
| Automatically inserting rows | Excel Worksheet Functions | |||
| Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
| flexible paste rows function that inserts the right number of rows | Excel Discussion (Misc queries) | |||
Linear Mode
