How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask NewITZone Your Own Question
NewITZone, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 90
Experience:  I have been using Office Pro for 10 years and implemented applications for business use.
Type Your Microsoft Office Question Here...
NewITZone is online now
A new question is answered every 9 seconds

In excel, how do if I use index match formula or it can be a

Customer Question

In excel, how do if I use index match formula or it can be a vlook up. How do I change the data only if it doesn't match but if it matches to keep the information in that cell?
Submitted: 9 months ago.
Category: Microsoft Office
Expert:  Jess M. replied 9 months ago.

Hi Jill, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.

Can you send me a copy of your Excel file that you are working on and what do you want to accomplish?
Please let me know by replying to me here so that I can help you further.

Best regards,

Customer: replied 9 months ago.
attached is an example, i want to merge sheet 2 columns into sheet 1 but if sheet 2 information is different i want it to change what is in sheet 1. If they match and is the same then I want it to stay as is.
Expert:  Jess M. replied 9 months ago.

Thanks. Please give me a moment to check

Expert:  The-PC-Guy replied 9 months ago.

so what exactly is it you want to do?

Combine the 2 sheets into a single list, but without any duplicates?

Customer: replied 9 months ago.
Expert:  The-PC-Guy replied 9 months ago.

ok, what determines a duplicate? If all columns are the same across both sheets?

Customer: replied 9 months ago.
After thinking about it I can use the index match formula match by ID but here is a problem I run into. If I filter and try to copy in filtered list. It doesn't copy in the right fields. Does this make sense
Expert:  The-PC-Guy replied 9 months ago.

the ids are the same across bot hsheets.

I could create I guess a reverse index match formula that would work if the value wasn't in the 2nd sheet type thing.

But I think in this case you would be better off with a macro. That would just look at each entire row and once and copy rows to a 3rd sheet that aren't on both sheets.

Customer: replied 9 months ago.
No I dont want to do a macro. If you filter large amounts of data how do you paste your formula in there and drag it all the way down with it just hitting the filter fields. Is there a way around that?
Expert:  The-PC-Guy replied 9 months ago.

unfortunately formulas look at all rows not just the filtered ones which is why I suggested the macros. However a reverse index match may make the filters unecessary.

But again formulas are limited which is why I suggested a macro.

Any particular reason why you don't like macros?

Customer: replied 9 months ago.
I just dont think the other analysts are using macros. So there is no vlook up formula or index match formula to use that will change column information for example Addresses and if it is different information it will change it but if it is the same the informations will stay the same.
Customer: replied 9 months ago.
Ok what if I inserted a column next to address column brought my information over with an index match forumlas. Is there a way to copy and paste into filtered fields?
Expert:  The-PC-Guy replied 9 months ago.

again when you filter any filtered row will be filtered for the entire row even if it has formulas.

so if you add a vlookup that brings information over, then when you filter the sheet that contains the vlookup formulas the results from those formulas can be filtered.

but if you are asking for a way to search a pre-filtered list, then I know of no such formula.

Also not sure what you mean by change column information if you mean by matching one column on one sheet to another.

Also not sure what exactly you are trying to filter.

I have been at this for a long time and for certain things I will reccomend formulas. And certain things macros. Only difference is the macro is not automatic you just have to run it. But that shouldn't take too long to do, and is not difficult to do.

Customer: replied 9 months ago.
Can you paste information in cells when it's filtered just to put the information in the filtered cells only??
Expert:  The-PC-Guy replied 9 months ago.

certainly could do that with a macro. But then when you refiltered it would have to be run again to put the information in new filtered cells.

could you provide an example of the output you want, I already have example of the data.

Also please describe filtering, do you plan to filter after the data is added, or before?

Customer: replied 9 months ago.
I need to brush up on how marcros work. Ok go ahead and create a macro. So any time I filter and sort data after I run marcro, I have to rerun a marcro again? After I add data with the marcro I would filter and sort to view things. Is that ok to do that? I made a third tab called Output
Expert:  The-PC-Guy replied 9 months ago.

i will have to charge extra to do the macro because it is more complicated.

the second list looks exactly the same as the first sheet. So not sure what I am missing here.

But the idea with the macro would be.

You would only need to run it when you add new data. Then you should be able to sort and filter all you like

Customer: replied 9 months ago.
Ok about pay. The id's will remain the same but on Output Tab the rest of the columns are updated by sheet 2
Expert:  The-PC-Guy replied 9 months ago.

not sure I am following.

Also need to ask what version of excel you use?

Customer: replied 9 months ago.
Excel 2013I just need sheet 2 to merge into sheet 1. if any column is different than sheet 2 I need sheet 1 to update to that.
Customer: replied 9 months ago.
Never mind. I just figured it out without doing a macro
Expert:  The-PC-Guy replied 9 months ago.

well, good for you, I was prepared to help, was just trying to get all the information so I could point you in the right direction.

Related Microsoft Office Questions