VBA code for extracting data from a list as per given criteria

**daksh1981** · 04-20-2009, 09:46 AM

Hi I need a help on a VBA code. Suppose I have a list of data in which I want to retrieve or extract specific row from the list as per operator name & relative comments (Done or Not Done) from the given list as per the operator name, now what I want that my VBA code should be able to get the operator name & the relative comment data from the list according to the operator name in a new sheet. My data keeps changing so I need a code which can work on any size of the data. I need to retrieve only operator name & Comment column in a new sheet to run my Pivot table.
My Excel data is something like in the attachment & the result I want through VBA Code.

**Norie** · 04-20-2009, 10:20 AM

Can't you just copy columns A & C in the first worksheet to A & B in the 2nd?

**daksh1981** · 04-21-2009, 02:27 AM

Thanks for replying

. The attached excel file is only a example I have huge data with multiple columns & rows. Using macro will definitely reduce time which I want. I need a VBA code for this. Which will produce the desired result as mentioned in the sheet.

**Norie** · 04-21-2009, 03:23 AM

There's only one attachment and that's in your 1st post.

And all you seem to be doing is deleting/moving columns.

I don't see any criteria happening or the data being manipulated in any way.

**daksh1981** · 04-21-2009, 10:08 AM

Hi friend, the attached sheet has only sample data with desired result in the next sheet which I want to get from the list. Criteria means criteria for the VBA code should be operator's name in the VBA code to match/compare in the list.What I want is that the result which showing in red highlighted color in next sheet should be extract or come via using a VBA code. The VBA code should work like this first it should look for the operator name in the given list then should pulled all the comment data matching with the operator name in a new sheet.

Like this
#
Operator Name Comment
NBKS2QI James Done
NBKSLVE Albert Not Done
NBKTZ1P John Not Done
NBKS2QI James Not Done
NBKSLVE Albert Done
NBKS2QI James Done
NBKSLVE Albert Not Done

**Norie** · 04-21-2009, 02:33 PM

Sorry but I must be missing something - the two sets of data are basically the same, all that's been done is some columns have been removed.

And I don't see any criteria.

**daksh1981** · 04-22-2009, 12:46 AM

You are right, I have just checked my attached file. I missed to include my criteria. Here is the updated sheet attached with the criteria included. Now what I want that the VBA code should pick those data from the Master Data whose operator name is in Criteria list , Operator's Name can be increase or decrease later, not other than that which falls under this list. I hope this time it is ok for you to understand what I actually want from the data. This time I have also used comments to make it more easy to understand

**squiggler47** · 04-22-2009, 01:00 AM

If you are going to use a pivot table anyway, why not use that on your original data to produce your result, its a lot faster than VBA will be!

Since you can choose what data to include and filter on names?

**daksh1981** · 04-22-2009, 01:27 AM

Thanks for replying squiggler47. You are right I want to run a Pivot table. But the problem lies in my master file it is such a hug size, so if I have a VBA code it will automatically pull that data from the master file into result sheet & I will only run the create Pivot Macro from the result sheet for creating a pivot table. I tried many codes but I am not that much advance programmer. Please help me out to get a perfect VBA code for this.

**Norie** · 04-22-2009, 06:02 AM

Why not use advanced filter?

Please Login or Register  to view this content.

**daksh1981** · 04-22-2009, 10:12 AM

Thanks for the code, it is working fine for only 50% it is pulling the operator's name in the Result Sheet , which what exactly I want, but I want the corresponding comments also in the next column for the operator's. I tired to customize the code but fail to get the desire result. Please help on it. I have attached the file, please have a look again on the Result file I have added a new comment & column.

**Norie** · 04-22-2009, 10:26 AM

Well this code works fine for me - I don't know where the 3rd delete came from in your code.

Please Login or Register  to view this content.

**daksh1981** · 04-23-2009, 10:11 AM

Hi, Norie. Now I have used your code as it is in the attached sheet, but even after this this code is just giving me the Operator's name it is not giving the Comment for that operator in Column C , which I need in column C of my Result file. Please see the attached file.

**Norie** · 04-23-2009, 10:19 AM

Could you try the code with the 'Result File' worksheet blank?

**daksh1981** · 04-24-2009, 01:07 AM

After deleting the value in Range A1:B8 in Result File Sheet, the code is working fine, but every time I have to manually delete the data from the range A1:B8 in Result File Sheet before running the code, so I used the below code before using the code for extracting data from the list. If you have any other suggestion please advice.

Please Login or Register  to view this content.

**Norie** · 04-24-2009, 04:37 AM

Why not just clear the cells in the worksheet 'Result File'?

Please Login or Register  to view this content.

This should work, unless you've other stuff happening on that sheet.

**daksh1981** · 04-29-2009, 03:34 AM

I hope that this will ok. My query has resolved. Thanks for the support.

**Norie** · 04-29-2009, 05:56 AM

Could you please tell us how it was resolved?

All the code I posted was tested, so the only thing I can think is that you were doing something else that you didn't mention.

This might be because you thought it wasn't relevant but you shouldn't assume something isn't relevant.

**daksh1981** · 05-01-2009, 03:23 AM

Yes you are right, this was only a sample data. I was looking for a solution of a problem. Which I got upto some level, I am working on how to use criteria from a given list to get the desired result. For example in my sheet I was using Operator's name as a criteria for my result, now what I am testing that Is it possible to look for partially match criteria in the list. Like if Operator's name is some thing like this : James Smith NBKLV8P; California. Now I am testing will it be possible for my code that it will look for "NBKLV8P" as it's criteria for doing advance search for my result? If you have any suggestion please advice. You always proved a great help for me.

**George Martin** · 05-25-2021, 04:58 AM

hi need a code to copy-paste data from one sheet to another
Sheet 1 has A:M data, I need to filter column D particular criteria and select the data of Columns A & D to paste in Sheet 2.
again repeat - need to filter column D another criteria and select the data of Columns A & D to paste in Sheet 2 with next available cell.
again repeat - need to filter column E another criteria and select the data of Columns A & E to paste in Sheet 2 with next available cell.Capture.PNG

**FDibbins** · 05-26-2021, 01:03 AM

Originally Posted by George Martin

hi need a code to copy-paste data from one sheet to another...

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

VBA code for extracting data from a list as per given criteria

LinkBack

Thread Tools

Rate This Thread

Display

VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Re: VBA code for extracting data from a list as per given criteria

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions