?
Solved

EXCEL 2010

Posted on 2016-10-06
7
Medium Priority
?
89 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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 1000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

770 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