really squidgy list to sort

[invisigirl]

Let me preface this question with this statement: "I do not know visual basic at all." Just so you know that if you give me some code to use, it's gotta be exact so I can just copy/paste it in.

Okay, here's the deal. I have a huge download listing billing codes for lab tests. Y'all know that when you download out of gigantoid databases, you sometimes get a lot of crap that repeats at the top of every "page break." Well, I need to remove all that crap, and I'd generally just sort by the billing code column, get all the extraneous junk in one chunk of the spreadsheet and then delete it.

The problem is that there are test panels in this list that show a billing code for the panel name but not the test included within it. Therefore, I can't sort by the billing code, which is what it needs to be sorted by even AFTER I remove all the unnecessary info. See below:

SOMEONE'S MEDICAL LABORATORIES
UNIT CODE MASTER LIST
ACCOUNTS RECEIVABLE FEE SCHEDULE(S)
UNIT CODE BILLING NAME CODE PRICE
--------------- -------------------------- ------------ ----------

12345 ACETAZOLAMIDE 80000 12.00

12346 ACETONYLACETONE 80001 40.00

12347 ACHR BLOCKING ANTIBODY 80002 29.50

12348 ACHR BINDING ANTIBODY 80003 15.65

12349 MYASTHENIA GRAVIS AB PNL80004 95.72

12350 CULTURE,ACANTHAMOEBA
CULTURE,ACANTHAMOEBA 80005 41.12
ACANTHAMOEBA SMEAR 80006 19.36
60.48


Well, you see what a mess it is, but I'm used to dealing with it. Anyway, what I need to do is somehow remove all the lines where BOTH a test name AND a price are missing (I know how to remove the line if ONE particular column is not null, but don't know how to add another variable).

After that, I need to be able to fill the billing code down through all the blank cells beneath it (because with the blank lines removed, I won't have to worry about assigning a code to a line w/ no info in it).

FINALLY, I will then be able to safely sort by billing code & remove everything that's not necessary.

I really hope that list came in okay - if not, I'll post a reply & try again.

Can this be done or am I just dreaming?

(Sorry for the length of this question)

[invisigirl]

Well, the data example came in pretty bad, but I don't know how else to do it. What I wanted to get across is that the last entry (12350) is a panel, with the billing code assigned to the name to its right, and two tests listed below it that do not have a code assigned to them. That's where I'll need to fill down.

Thanks for any help you can give.

[daleyman]

Okay, shouldn't be a problem, and better use functions instead of code if that is what you will understand and be able to customise...

save it all first unless you get in a mess...

If the only blank column A cells are ones that need filling and the junk lines contain a value here then probably best to deal with filling the blanks first. lets suppose you have your four columns already:

col A: unit code
col B: billing name
col C: Code
col D: Price

...lets fill a new unit code column into column E.

in cell E1 put the equation =A1
in cell E2 put the equation =IF(A2="",E1,A2)

...and fill this equation down to the bottom of the list. Then copy this to values to remove the equations (select the whole column by clicking on the grey column header E, then using the RIGHT MOUSE BUTTON on the EDGE of the selection, move the column away and drop it onto column A to replace the broken list by dragging with then releasing the right mouse button, a menu will appear... select "Copy Here as Values Only"). You can then delete column E.

...then if i understand you correctly you can do the sort and delete as you would have done beforehand. make sure you select all columns with data before you do the sort.

[Mark W.]

Too easy... With your supplied data pasted into columns A:D...

1. Select columns A:D and choose the Data | Filter | AutoFilter menu command
2. Choose "(Blanks)" from column B's filter arrow drop down list
3. Select and delete all rows that have a blue row number
4. Save your worksheet
5. Select column A and choose the Edit | Go To... Special... Blanks menu command
6. Enter a formula with a reference to the cell above the active cell (e.g., =A10) and press the Control+Enter key combination.
7. Select column A once again and perform a Copy/Paste Special Values.

...continue, by filtering (displaying) repeating headers and then deleting them. You'll also want to filter "(Blanks)" in column D to remove records that don't have a PRICE.

[ This Message was edited by: Mark W. on 2002-04-03 16:33 ]

[Paul-Johnson]

I agree with the last post Filters and advanced filters woulb be the easiset and the most flexible.

[invisigirl]

Well, the thing with autofiltering is that on the panel name (top line of a panel), there is not a price. But THEN there IS a total price (sum) in the price column in the row underneath the last test in the panel. THAT row doesn't contain a name. The only thing in it is the price.

Now, I suppose what I could do is forget about the totals and do subtotals later, but I wanted to know if it was possible (for future reference) to put in a condition that removes the row if both B and D are null.

For now, I'll use your other suggestions, which were great. If it's possible to do the other thing, let me know.

Thanks for your quick help!

[Henry]

Send me a portion of the mess. I think I've ran into similar problems
before.

henry@compuvision.net

[invisigirl]

Thanks, I will!