Copy data from one sheet and paste to another based on multiple criteria

**Hem** · 08-07-2010, 05:44 AM

Hey guys,

I'm new on this forum. I am urgently seeking help for the problem mentioned below:

I want to copy data from "sheet 4" (D8:D1000)&(E8:E1000)&(k8:k1000)

and paste to "sheet 2" (B6) onwards.

Criteria:-

Criterion (1)
Criterion range "sheet 4" (C8:C1000), criterion: should match with the value entered in "sheet 2" (B3)

Criterion (2)
Criterion range "sheet 4" (G8:G1000), criterion: Should be bigger than or equal to the date (mm/dd/yyyy) entered in ''sheet 2'' (M3)

Criterion (3)
Criterion range "sheet 4" (G8:G1000), criterion: Should be smaller than or equal to the date (mm/dd/yyyy) entered in ''sheet 2'' (N3)

I have tried everything but no success. Formula or macro, any help would be really appreciated. Need help urgently.

Look forward to the helping hands of this famous forum.

Cheers!!!!

**Richard Buttrey** · 08-07-2010, 06:44 AM

Hi,

I suggest you need to use a Data Filter but first ask yourself the question do you really want to extract data from one sheet to another, or do you just want to see the data that meets your 3 criteria?

If the latter then a simple Data Autofilter should be sufficient, and as I see you have Excel 2007 there are more user friendly filter selections available than in previous versions of Excel/

If you do want to extract data to another sheet then use Data Filter Advanced with a copy to another location selected. You can do this either manually or have a macro do it automatically. If you want help with the latter I suggest you upload your workbook so that we can see the layout.

Regards

**Hem** · 08-07-2010, 07:34 AM

Dear Richard,

Thanks a lot for a prompt reply.

I want to extract the data from one sheet and paste to the other. When i use advanced filter then it pastes the criteria range as well but i am looking forward to pasting data only from the target ranges.

I would upload an example of my workbook, but not sure how to do that, could you please help me?

Cheers!!
Hem

**Richard Buttrey** · 08-07-2010, 07:59 AM

Hi,

You must be doing something wrong if you say the criteria range is appearing on the target sheet.

Put the Data on one sheet, the labels you require from the data sheet (can be a subset and in a different order if you prefer) on another sheet and use this single row of labels as your Output range, and then on a third sheet just your criteria labels and values.

To upload a workbook, look just below the 'Submit Reply' button in the Additional Options area and click the 'Manage attachment buttons.

I'm away now for about 18 hours so won't be able to respond further until then, but you may find someone else chips in.

Rgds

**Hem** · 08-07-2010, 10:00 AM

Lemme try again then.
Thanks mate!!

**Hem** · 08-08-2010, 04:08 AM

Hey Richard,

I tried as suggested by you mate, but no success.

**Hem** · 08-08-2010, 05:14 AM

Hi Richard,

Finally worked man!!!!!

Thanks a ton mate!!

I shall close the thread now.

Cheers!!

**Hem** · 08-08-2010, 06:08 AM

Hi,

Came across one more problem.

Example:

(Animal) (Colour) (No. of hunts)

Tiger Yellow 2
Tiger Yellow 3
Tiger Yellow 5
Panther Black 4
Panther Black 3
Cat Pink 2
Cat Pink 3

How can i make it as mentioned below with a formula?

Tiger Yellow 10
Panther Black 7
Cat Pink 5

PLEASE HELP, would really appreciate it....

**teylyn** · 08-08-2010, 06:10 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

To post a workbook, click "Go advanced" below the Quick Reply box and then the Paper clip icon.