CountIf Issues 1

[zutfen]

I have been trying to get a countif statement to work, to no avail, and am hoping to get some assistance from some more code oriented folks. =)

Here's my dillema:

I track prescription faxes for my company. ie: We fax them to doctors, they sign them and fax them back. Each prescription (RX) has a barcode on it which our proprietary software uses to check errors / track said RX's. I intend to use those barcodes to track my faxes in Excel.

Column A is the "Outstanding RX" column; Column B will be the "Received" column. This is simple enough.

Rx's have 9 digit number ID's from the barcodes. (Side note.)
_____________________
Column A | Column B |
---------------------
1 | 2 |
---------------------
4 | 3 |
---------------------
7 | 4 |
---------------------

So what I'm trying to do is something similar to the [highlight]{=IF(COUNTIF($A$1:$A$10,B1)>0,B1,"")}[/highlight] code found on

http://www.cpearson.com/excel/duplicat.htm

Such that in the above example, 4 would be deleted from Column A, and the cells shifted up accordingly.

But I must be doing something wrong, basicly I want to be able to scan an RX number into column B, and have Excel remove the duplicate number in Column A, so it keeps a running tally of what RX's are outstanding still. This would make my life a lot easier, but trying to make it work is making life a lot harder. =P

Any help anyone can offer would be greatly appreciated!

Thanks for your time,
Eric

 

[GlennUK]

Hi Eric,

if you want column A to contain a running tally, then you need to have formulae elsewhere to calculate the current position, and then copy those results over column A, sorting the results if necessary.

So, in column C, you could have :
=IF(COUNTIF($B$1:$B$10,A1)=0,A1,"")
entered into C1 and copied down.

The results in column C would have "" instead of the corresponnding value in column A where a match exists in column B. Now, select the results in column C, do Edit/Copy. Then select cell A1, and do Edit/Paste Special/Values. This will overwrite the current list in column A with the updated results, as you require. You can now sort column A if desired, to put the "" values at the end of the list.

Good luck.




Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[zutfen]

Glenn,

That is absolutely perfect! Exactly what I was looking for! This is going to be incredibly useful. Thanks for your response and for your time, it's much appreciated!

Eric

 

[zutfen]

Just to stir the pot a little more. >
The barcodes I scan give me an 11 digit number, but I really only care about the first 7 of them. Is there a way to make Excel truncate the last four digits? IE: Totally disregard them.

Example:
Prescrition number 1234567 scans into Excel as 12345670001.

I really only care about 1234567, in fact, the "000X" on the end just makes it harder to work with the numbers, as they all begin to look the same. Could I concatenate it somehow, or is there a more straight-forward approach I can take, I've tried to do a Custom Cell Format, no dice. Any further assistance will be much appreciated! Maybe it'll be my last problem on this project. <chuckle> Yeah, right. =)

 

[bluedragon2]

=left(a1,7)



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...