# Removing a value

#### Yusuf

##### Active Member
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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
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.

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","")

Only one word for you BrianB, "AWESOME"!!

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

Replies
11
Views
159
Replies
23
Views
425
Replies
1
Views
192
Replies
4
Views
313
Replies
1
Views
173

1,196,328
Messages
6,014,669
Members
441,834
Latest member
GHOSTOF309

### 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.

### Which adblocker are you using?

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

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