Solved

EXCEL 2010

Posted on 2016-10-06
7
71 Views
Last Modified: 2016-11-15
I would like to create a if than else function in excel.  I I have a large file of 7000 records I would like to set.
Example
if column a1 = "not mapped",
set column b1 = blank
and
set column c1 = blank
else
leave values to current status.

attaching a spreadsheet.
Excell_ifThenelseook1.xlsx
0
Comment
Question by:centralmike
  • 2
  • 2
7 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41833046
I can think of two ways to do this.

1. No VBA. Use columns D and E for the result and hide columns B and C
2. With VBA

Which way do you want to go?
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 250 total points
ID: 41833049
Just thought of a third way.

Use Autofilter and filter the list for NOT MAPPED

Select the data in front of all the displayed rows and clear it

Unfilter the data
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 250 total points
ID: 41833244
With Saqib's suggestion of a formula.

Column D:

=IF($A1="Not mapped","",B1)   copy across to column E and down as far as required.

As suggested then hide columns B & C or copy and paste values from D & E overwriting B & C.  When copying a cell containing a formula resulting in "", the result of the value paste will not be an empty cell but will be '.

If you want the cells to be truly blank, an autofilter will recognise these as blank so you can filter to show only blank and delete them. When a filter is in place you can select the whole range of cells as one block but only visible cells will be affected by any actions taken. If you're going to do an autofilter to correct this, you might as well just do an autofilter in the first place as suggested by Saqib in his second comment.
0
 

Author Comment

by:centralmike
ID: 41850427
The following function worked great.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41887826
Maybe should share 50/50 with Saqib. Comment from OP does not state which answer was used.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to strip out formula but retain value in cell 2 24
Clear a Text Box 7 27
Do Wend Macro not working 22 36
Countdown Timer 2 16
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question