Hi,
I have already the vba codes but i'm only using one criteria to lookup from another workbook or worksheet. so far this is working, but i would like to add additional criteria, " labels" and "qty".
may i ask your help guys on how to do this in vba macro or can you help me to modify this codes to provide the said requirements. thank you in advance.
Please Login or Register to view this content.
I think.. Advanced filter is best in this situation...
Have a look at this..
Range.advancedfilter is all what you need
used the copytorange property get the result at other sheet...
Last edited by Vikas_Gautam; 09-25-2014 at 11:17 PM.
Regards,
Vikas Gautam
Excel-buzz.blogspot.com
Excel is not a matter of Experience, its a matter of Application.
Say Thanks, Click * Add Reputation
Hi Vikas, actually this is also the given alternative solution to use advance filter. i'm a beginner on this vba macro, may i ask your assistance on how to do this. i'm looking a sample at the net but could not get a sample that similar with my requirements. thank you.
Provide a non sensitive sample of your workbook..
rest I will do..
Thank you for your time Vikas,
requirements:
1. filter data from working with all "INTRANSIT".
2. check data of working file sheet column trackno, labels and qty if exist to status summary sheet.
3. if found or exist in status summary sheet evaluate if the the column status is "Done" or "Not Yet"
4. if Done replace the working file sheet column status as "Received" while if "not yet" replace as "INTRANSIT"
attached sample data.
This is possible with simple Index and Match..
why do you need a macro..
Formula:
Please Login or Register to view this content.
Last edited by Vikas_Gautam; 09-27-2014 at 10:55 PM.
Yes. Vikas if possible a macro.
btw, thank you for for your time answering my query.
Hi Vikas,
Thank you very much. The formula that you have given is working (awesome). btw, My question is what if the records does not exist in status summary but in working file the data is exist? the result given by formula is #N/A, it should be consider as intransit.
by the way, can you please provide me also a code using vba macro. This is an oppurtunity for me to learn new ideas. I would love to this vba macro, i think its a big help on my side especially i have a lot of requirements that can be solved by your code. again thank you.
Last edited by Jovillanueva; 09-27-2014 at 08:36 AM.
Okay..
I will provide you that..
Here is the adjusted formula for.. NA error..
Formula:Please Login or Register to view this content.
Check the attached file..
Re: Find multiple values with multiple criteria in vba macro
Here is the macro..
I again suggest you to use formula....Please Login or Register to view this content.
Check the attached file..
Hi Vikas,
Thank you very much for the codes. The formula that you have given is working like a charm. thank you for sharing this one. I would like to ask if possible to call aworkbook in that formula which is the status summary. right now, i have to open the workbook and create a vba code to replicate/dump the data to my workingfile.
Btw, I'm trying to run the vba codes i got from you and i encounter an error saying "wrong number of arguments or invalid property assignment"
It was stuck up in Evaluate function. Even i used the actual code without modification still got this error. May i know what is the issue. thanks.
see below the codes that I modified.
Please Login or Register to view this content.
Last edited by Jovillanueva; 09-28-2014 at 09:19 AM.
Can you provide the workbook..? you are working with..
that is what needed here..
Workbook Name "MVP WIP REPORT.xlsm" - This is the main excel file with vba macro code. "Intransit_" this is the worksheet that contains search for data.
Workbook Name "Status Summary 2014.xlsx" - this is the workbook that contains worksheet name "CoresStatus" were use to search in data to Intransit_
Hope this is good enough as your reference . thanks.
Hi Vikas,
Here is formula of index match using workbook. this formula was copied under column F of target file.
When i modify the source data let say i change to Not yet the column F data did not change.
Then when i tried to click the corner of the cell of column F, the open file dialog box will also open to select the file Cores Status Summary 2014.xlsx.
Then when i perform ctrl + Shift +enter it aslo open the dialog box to select an excel file. Kindly please what wrong with the modified formula. Thanks.
Btw, the Cores Status Summary 2014.xlsx was stored in C:\Users\Sample\Desktop\Dev\Report\Intransit
Please Login or Register to view this content.
Try this..
I hope this will help...
=IFERROR(IF(INDEX('C:\Users\Sample\Desktop\Dev\Report\Intransit\[Cores Status Summary2014.xlsx]Core Status'!$J$2:$J$25000,MATCH($A11240&$B11240&$C11240,INDEX('C:\Users\Sample\Desktop\Dev\Report\Intransit\[Cores Status Summary 2014.xlsx]Core Status'!$B$2:$B$25000&'C:\Users\Sample\Desktop\Dev\Report\Intransit\[Cores Status Summary 2014.xlsx]Core Status'!$F$2:$F$25000&'C:\Users\Sample\Desktop\Dev\Report\Intransit\[Cores Status Summary 2014.xlsx]Core Status'!$D$2:$D$25000,),0))="Done","RECEIVED","IN-TRANSIT"),"IN-TRANSIT")
Formula:Please Login or Register to view this content.
Regards,
Last edited by Vikas_Gautam; 10-01-2014 at 08:09 AM.
Hi Vikas, I got it already the error. its already working. The filename should have a space between summary2014, summary 2014.
btw, I would like to say thank you for the codes. (Solved)
Thanks for the response..
Hi Vikas, the formula codes is already working. THank you very much.\
Sorry, I did not see the second page. anyway thank you.
Hi Vikas,
I have another question on index match. how can i change the result if the index match is TBA the result is TBA if not i will return the value of cell is is date.
=IFERROR(IF(INDEX('[Cores Status Summary 2014.xlsx]Core Status'!$G$2:$G$25000,MATCH($A9934&$B9934&$C9934,INDEX('[Cores Status Summary 2014.xlsx]Core Status'!$B$2:$B$25000&'[Cores Status Summary 2014.xlsx]Core Status'!$F$2:$F$25000&'[Cores Status Summary 2014.xlsx]Core Status'!$D$2:$D$25000,),0))<>"TBA","Return the value of cell","TBA"),"TBA").. thanks.
Btw, I would like to ask your opinion or idea. right now most of our reports was prepared in Excel file 2010. all raw data is coming from SSRS and exported to excel and dump to excel file and the user will perform data manipulation. we wanted to automate all the process from dumping data to calculation. I have plan to render a training in Excel VBA to enhance my skills and apply to my future projects. aside from vba what can you suggest for reporting tools in automating the report. i have an skills in SQL, SSRS but limited permission to access in DB, this is my restriction using the SSRS and SQL. what about Powerpivot?
Last edited by Jovillanueva; 10-02-2014 at 11:05 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks