Solved

EXCEL 2010

Posted on 2016-10-06
7
42 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 31

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 31

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now