| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
removing single items from list
I'm trying to figure out if there is a way to remove the non duplicate items
from my list. I have a rather large list that is separated by blank lines. Sometimes there is a blank line, a line of data and another blank line. Other times there is a blank line, 2 or more lines of data, then a blank line. Can I write a formula in G5 that says if F4 and F6 are blank give me "*".. then filter for the stars and delete those lines? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
removing single items from list
Hi,
Put this in g1 and drag down and sort on tcolumn G and delete all rows that evaluate as TRUE =F1="" Mike "jenn" wrote: I'm trying to figure out if there is a way to remove the non duplicate items from my list. I have a rather large list that is separated by blank lines. Sometimes there is a blank line, a line of data and another blank line. Other times there is a blank line, 2 or more lines of data, then a blank line. Can I write a formula in G5 that says if F4 and F6 are blank give me "*".. then filter for the stars and delete those lines? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
removing single items from list
this seems to apply true to all the blank lines... I need a 'true' if the
line above and the line below are blank. "Mike H" wrote: Hi, Put this in g1 and drag down and sort on tcolumn G and delete all rows that evaluate as TRUE =F1="" Mike "jenn" wrote: I'm trying to figure out if there is a way to remove the non duplicate items from my list. I have a rather large list that is separated by blank lines. Sometimes there is a blank line, a line of data and another blank line. Other times there is a blank line, 2 or more lines of data, then a blank line. Can I write a formula in G5 that says if F4 and F6 are blank give me "*".. then filter for the stars and delete those lines? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
removing single items from list
Assuming your data starts in F1, put this in G2 (notice that is G2, not G1)
and copy it down... =IF(COUNTA(A1:A3)=1,TRUE,"") -- Rick (MVP - Excel) "jenn" wrote in message ... this seems to apply true to all the blank lines... I need a 'true' if the line above and the line below are blank. "Mike H" wrote: Hi, Put this in g1 and drag down and sort on tcolumn G and delete all rows that evaluate as TRUE =F1="" Mike "jenn" wrote: I'm trying to figure out if there is a way to remove the non duplicate items from my list. I have a rather large list that is separated by blank lines. Sometimes there is a blank line, a line of data and another blank line. Other times there is a blank line, 2 or more lines of data, then a blank line. Can I write a formula in G5 that says if F4 and F6 are blank give me "*".. then filter for the stars and delete those lines? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
removing single items from list
that's what I needed... thanks
"Rick Rothstein" wrote: Assuming your data starts in F1, put this in G2 (notice that is G2, not G1) and copy it down... =IF(COUNTA(A1:A3)=1,TRUE,"") -- Rick (MVP - Excel) "jenn" wrote in message ... this seems to apply true to all the blank lines... I need a 'true' if the line above and the line below are blank. "Mike H" wrote: Hi, Put this in g1 and drag down and sort on tcolumn G and delete all rows that evaluate as TRUE =F1="" Mike "jenn" wrote: I'm trying to figure out if there is a way to remove the non duplicate items from my list. I have a rather large list that is separated by blank lines. Sometimes there is a blank line, a line of data and another blank line. Other times there is a blank line, 2 or more lines of data, then a blank line. Can I write a formula in G5 that says if F4 and F6 are blank give me "*".. then filter for the stars and delete those lines? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
removing single items from list
Hi,
These are the result I get using the suggested formula and the following data layout: a a a a a TRUE a TRUE TRUE TRUE a a TRUE TRUE a a a It seem that repeating blanks get removed which wasn't what you stated and in the case of three consecutive blanks the outer two get remove, that doesnt jive either. So my point is be sure this is what you want, because it is not what you stated. Good luck -- Thanks, Shane Devenshire "jenn" wrote: that's what I needed... thanks "Rick Rothstein" wrote: Assuming your data starts in F1, put this in G2 (notice that is G2, not G1) and copy it down... =IF(COUNTA(A1:A3)=1,TRUE,"") -- Rick (MVP - Excel) "jenn" wrote in message ... this seems to apply true to all the blank lines... I need a 'true' if the line above and the line below are blank. "Mike H" wrote: Hi, Put this in g1 and drag down and sort on tcolumn G and delete all rows that evaluate as TRUE =F1="" Mike "jenn" wrote: I'm trying to figure out if there is a way to remove the non duplicate items from my list. I have a rather large list that is separated by blank lines. Sometimes there is a blank line, a line of data and another blank line. Other times there is a blank line, 2 or more lines of data, then a blank line. Can I write a formula in G5 that says if F4 and F6 are blank give me "*".. then filter for the stars and delete those lines? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
removing single items from list
Man, did I screw that one up! Thanks for catching it; I sure hope the OP
comes back to check this thread. This much more straight-forward formula looks like it will actually do what the OP asked for... =IF(AND(F1="",F2<"",F3=""),TRUE,"") Again, it is placed in G2 and copied down (notice I corrected the references to Column F as the OP originally asked for also). -- Rick (MVP - Excel) "ShaneDevenshire" wrote in message ... Hi, These are the result I get using the suggested formula and the following data layout: a a a a a TRUE a TRUE TRUE TRUE a a TRUE TRUE a a a It seem that repeating blanks get removed which wasn't what you stated and in the case of three consecutive blanks the outer two get remove, that doesnt jive either. So my point is be sure this is what you want, because it is not what you stated. Good luck -- Thanks, Shane Devenshire "jenn" wrote: that's what I needed... thanks "Rick Rothstein" wrote: Assuming your data starts in F1, put this in G2 (notice that is G2, not G1) and copy it down... =IF(COUNTA(A1:A3)=1,TRUE,"") -- Rick (MVP - Excel) "jenn" wrote in message ... this seems to apply true to all the blank lines... I need a 'true' if the line above and the line below are blank. "Mike H" wrote: Hi, Put this in g1 and drag down and sort on tcolumn G and delete all rows that evaluate as TRUE =F1="" Mike "jenn" wrote: I'm trying to figure out if there is a way to remove the non duplicate items from my list. I have a rather large list that is separated by blank lines. Sometimes there is a blank line, a line of data and another blank line. Other times there is a blank line, 2 or more lines of data, then a blank line. Can I write a formula in G5 that says if F4 and F6 are blank give me "*".. then filter for the stars and delete those lines? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| removing duplicate data from a single list | Excel Worksheet Functions | |||
| Removing items from worksheet by account number question | New Users to Excel | |||
| How do I link all the items in a single row? | Excel Discussion (Misc queries) | |||
| named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
| Removing single quote from an excel sheet | Excel Discussion (Misc queries) | |||
Linear Mode
