Removing a value

Yusuf

Active Member
Joined
Jun 1, 2004
Messages
337
Hi all

I have a spreadsheet that has two columns (A & B) with the first containing account numbers and the second containing many item IDs for three different kinds of items.
I know what the items are because of what the IDs start with
eg; pen = 0011..., pencil = AAA.. and paper = 75...

Because the items appear in only one column, if one account had all three items then the Account number would appear thrice in column A and have the item IDs beside it.

My predicament,
I have been tasked to remove all the Paper Only accounts.

This means that I have to overlook the Accounts that contain Paper and something(s) else.


I am totally struggling with this one and would appreciate any help you might have with this
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
By remove do you mean physically remove (delete)?

If so, you can filter for lines containing Paper and delete them all there and then remove filter to see your updated list.

DATA|FILTER|AUTOFILTER from the drop downs that appear in the column headers, click the one at the top of the column containing the item names to be removed. Then select Custom. From the dialog drop down selects Is Equal To or Contains (if you want anything containing Paper), and enter the string Paper. Click Ok.

You should see only items of interest you can delete the rows and then remove the filter, via the Data|Autofilter menu.
 
Upvote 0
I hope I have got it right :-
Copy this formula down Column C or whatever. It checks for "75" in the ID and also that there is only 1 instance of the account number.
Sort the data on that column and delete the rows with "PAPER ONLY"

=IF(AND(LEFT(B1,2)="75",COUNTIF(A1:$A$100,A1)=1),"PAPER ONLY","")
(change A1:A100 to your range)
 
Upvote 0
Only one word for you BrianB, "AWESOME"!!

Thanks for your help guys and have a good weekend
Cheers
Yusuf
 
Upvote 0

Forum statistics

Threads
1,223,430
Messages
6,172,065
Members
452,444
Latest member
ShaImran193

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top