I have a sheet with only 2 columns (id and flag). a single id can have up to 17 rows . if an id has a 12 in the flag on any of its rows then the rows for that id are to be removed.
How can I do it? or should I do it in ACCESS?
Sort by flag and delete all rows with 12 in it.
BUt you have to delete all the other rows for the id that had a 12 flag.
example:
id flag
1 4
1 5
1 6
2 3
2 12
3 1
3 3
You would need to remove id=2 (both rows) because it contains a 12 in the second row.
A couple of questions.
How automated are you trying to make this? How large is the file?
The first thing I thought of was to do an autofilter on the 2 columns and filter the flag column by the 12 you are looking for. Then, note what numbers show up in the id column, remove the filter, sort by id, and delete the appropriate rows. Sorta manual but it is the first thing I thought of without actually going into Excel and trying anything else.
If you can run Excel IV Macros, then write this one out and run with the cursor starting in the first cell of the first column:
(Note: “A#” is the cell address of the Macro Sheet)
A1 Macro1()
A2 =if(active.cell()=12,goto(A6))
A3 =select(“rc[1]”)
A4 =if(active.cell()=12,goto(A8))
A5 =select(“r[1]c[-1]”)
A6 =if(cell(“type”)=“b”,halt(),goto(A1))
A7 =edit.delete(3)
A8 =goto(A5)
A9 =edit.delete(3)
A10 =select(“rc[-1]”)
A11 =goto(A5)
I still prefer these macros to visual basic, but it’s difficult to write one in newer versions. I just save some from older versions and copy the sheets over, then rewrite.