Solved

excel formula or VBA

Posted on 2016-10-05
7
41 Views
Last Modified: 2016-10-06
I have two Columns in EXCEL

below are the scenarios i have to cover in the formula
  1. IF I have data in Col A that have to updated in Column D
  2. If I have Zero in Column A then do a vlookup based on Column C in Sheet2 and pick update results of column 5
  3. even after vlookup if i do not find anything Column D have to be updated with Column B
  4. in Column A if i have the rows contain FUSE DB then col B have to picked
  5. Delete Fuse db word in results column

I know this is little confusing I am attaching a sample file
How can i have formula or macro to do everythin in one column
sample1.xlsx
0
Comment
Question by:Nirvana
  • 4
  • 3
7 Comments
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
Try in column A:

=IFERROR(VLOOKUP(C2,Sheet2!$A$2:$E$6,5,0),B2)

copied down.

I think you are also talking about the formula in column D.  It looks like you had and \ in the formula causing error:

s/b =IF(A2="FUSE DB",B2,A2)

Does this cover your request?
0
 

Author Comment

by:Nirvana
Comment Utility
Thank you so much, just few changes

1. If i get a result as "Fuse DB" in Col "D" then it have to be treated as blank

which means after execute the below formula and if stil get "Fuse DB" it have to cosider ""(Blank)

=IFERROR(VLOOKUP(C2,Sheet2!$A$2:$E$6,5,0),A2)

2. If my Column "D' is zero and Column Has "Fuse DB" only then this Formula if I have data in Columd then Col E have to be blank

=IF(A2="FUSE DB",B2,A2)

In a nut shell i either have to have data in Col D or Col E
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
For part 1, try

=IF(VLOOKUP(C2,Sheet2!$A$2:$E$6,5,0)="Fuse DB","",IFERROR(VLOOKUP(C2,Sheet2!$A$2:$E$6,5,0),A2)

for part 2, our description got a little confusing...  can you elaborate on that.

I did notice that the 0 in column A is text, so the current formula in column C should have the 0 in quotes...

=IF(A2="0",C2,A2)  

copied down.
0
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

 

Author Comment

by:Nirvana
Comment Utility
for part 1 i am getting an error and i have attached file with changes highlighted in red. Hope I am clear. Thank you so much for your time
sample1.xlsx
0
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
Comment Utility
Try:

=IFERROR(IF(VLOOKUP(C2,Sheet2!$A$2:$E$6,5,0)="Fuse DB","",VLOOKUP(C2,Sheet2!$A$2:$E$6,5,0)),A2)
1
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
and change G2 to:

=IF(A2="FUSE DB",B2,A2) to remove the #NA error
1
 

Author Closing Comment

by:Nirvana
Comment Utility
Thank you so much. is there any way i can put it as a macro formula gets copy pasted based on the active cells in Col A?
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

772 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

9 Experts available now in Live!

Get 1:1 Help Now