Solved

EXCEL 2010

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

696 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