Extract Data To Other Workbook With Criteria

[hrayani]

Hello Friends,

I need help to Extract DATA from one workbook to another....

Here is the DATA Sample

[TABLE="width: 1239"]
<tbody>[TR]
[TD]REF[/TD]
[TD]P.O #[/TD]
[TD]P.O.DATE[/TD]
[TD]Customer[/TD]
[TD]Factory[/TD]
[TD]Article[/TD]
[TD]Quality[/TD]
[TD]Dyed or Printed[/TD]
[TD]Fiber Content[/TD]
[TD]Construction[/TD]
[TD]Size[/TD]
[TD] Quantity[/TD]
[TD]UNIT[/TD]
[/TR]
[TR]
[TD]132[/TD]
[TD]78118[/TD]
[TD]19-Dec-13[/TD]
[TD]ABC[/TD]
[TD]GA[/TD]
[TD]Bed Sets[/TD]
[TD]CVC Renforce - 90 Gsm[/TD]
[TD]Pigment Printed Rotary[/TD]
[TD]60% Cotton 40% Polyester[/TD]
[TD]100D X 22 ctn 76x52[/TD]
[TD]1[/TD]
[TD] 372,472[/TD]
[TD]Set(s)[/TD]
[/TR]
[TR]
[TD]132[/TD]
[TD]78186[/TD]
[TD]19-Dec-13[/TD]
[TD]ABC[/TD]
[TD]GA[/TD]
[TD]Bed Sets[/TD]
[TD]CVC Renforce - 90 Gsm[/TD]
[TD]Pigment Printed Rotary[/TD]
[TD]60% Cotton 40% Polyester[/TD]
[TD]100D X 22 ctn 76x52[/TD]
[TD]2[/TD]
[TD] 176,976[/TD]
[TD]Set(s)[/TD]
[/TR]
[TR]
[TD]132[/TD]
[TD]78581[/TD]
[TD]19-Dec-13[/TD]
[TD]ABC[/TD]
[TD]GA[/TD]
[TD]Bed Sets[/TD]
[TD]CVC Renforce - 90 Gsm[/TD]
[TD]Pigment Printed Rotary[/TD]
[TD]60% Cotton 40% Polyester[/TD]
[TD]100D X 22 ctn 76x52[/TD]
[TD]1[/TD]
[TD] 26,088[/TD]
[TD]Set(s)[/TD]
[/TR]
[TR]
[TD]133[/TD]
[TD]76424[/TD]
[TD]20-Jan-14[/TD]
[TD]ABC[/TD]
[TD]GA[/TD]
[TD]Bed Sets[/TD]
[TD]CTN Renforce - 115 Gsm[/TD]
[TD]Pigment Printed Rotary[/TD]
[TD]100% Cotton[/TD]
[TD]30x30 / 76x62[/TD]
[TD]2[/TD]
[TD] 524,320[/TD]
[TD]Set(s)[/TD]
[/TR]
[TR]
[TD]133[/TD]
[TD]76535[/TD]
[TD]20-Jan-14[/TD]
[TD]ABC[/TD]
[TD]GA[/TD]
[TD]Bed Sets[/TD]
[TD]CTN Renforce - 115 Gsm[/TD]
[TD]Pigment Printed Rotary[/TD]
[TD]100% Cotton[/TD]
[TD]30x30 / 76x62[/TD]
[TD]1[/TD]
[TD] 192,840[/TD]
[TD]Set(s)[/TD]
[/TR]
[TR]
[TD]133[/TD]
[TD]76546[/TD]
[TD]20-Jan-14[/TD]
[TD]CDE[/TD]
[TD]GA[/TD]
[TD]Bed Sets[/TD]
[TD]CTN Renforce - 115 Gsm[/TD]
[TD]Pigment Printed Rotary[/TD]
[TD]100% Cotton[/TD]
[TD]30x30 / 76x62[/TD]
[TD]2[/TD]
[TD] 48,360[/TD]
[TD]Set(s)[/TD]
[/TR]
[TR]
[TD]133[/TD]
[TD]76627[/TD]
[TD]20-Jan-14[/TD]
[TD]CDE[/TD]
[TD]GA[/TD]
[TD]Pillow Pair[/TD]
[TD]CTN Renforce - 115 Gsm[/TD]
[TD]Pigment Printed Rotary[/TD]
[TD]100% Cotton[/TD]
[TD]30x30 / 76x62[/TD]
[TD]1[/TD]
[TD] 372,552[/TD]
[TD]Pair(s)[/TD]
[/TR]
[TR]
[TD]133[/TD]
[TD]76634[/TD]
[TD]20-Jan-14[/TD]
[TD]CDE[/TD]
[TD]GA[/TD]
[TD]Side Pillow[/TD]
[TD]CTN Renforce - 115 Gsm[/TD]
[TD]Pigment Printed Rotary[/TD]
[TD]100% Cotton[/TD]
[TD]30x30 / 76x62[/TD]
[TD]2[/TD]
[TD] 224,364[/TD]
[TD]Pc(s)[/TD]
[/TR]
[TR]
[TD]146[/TD]
[TD]101031[/TD]
[TD]25-Mar-14[/TD]
[TD]CDE[/TD]
[TD]LM[/TD]
[TD]Bed Sets[/TD]
[TD]CVC Renforce - 90 Gsm[/TD]
[TD]Pigment Printed Rotary[/TD]
[TD]60% Cotton 40% Polyester[/TD]
[TD]100D X 22 ctn 76x52[/TD]
[TD]1[/TD]
[TD] 107,040[/TD]
[TD]Set(s)[/TD]
[/TR]
[TR]
[TD]146[/TD]
[TD]101032[/TD]
[TD]25-Mar-14[/TD]
[TD]CDE[/TD]
[TD]LM[/TD]
[TD]Bed Sets[/TD]
[TD]CVC Renforce - 90 Gsm[/TD]
[TD]Pigment Printed Rotary[/TD]
[TD]60% Cotton 40% Polyester[/TD]
[TD]100D X 22 ctn 76x52[/TD]
[TD]2[/TD]
[TD] 115,222[/TD]
[TD]Set(s)[/TD]
[/TR]
[TR]
[TD]146[/TD]
[TD]101033[/TD]
[TD]25-Mar-14[/TD]
[TD]CDE[/TD]
[TD]LM[/TD]
[TD]Bed Sets[/TD]
[TD]CVC Renforce - 90 Gsm[/TD]
[TD]Pigment Printed Rotary[/TD]
[TD]60% Cotton 40% Polyester[/TD]
[TD]100D X 22 ctn 76x52[/TD]
[TD]1[/TD]
[TD] 31,860[/TD]
[TD]Set(s)[/TD]
[/TR]
[TR]
[TD]147[/TD]
[TD]201905 A[/TD]
[TD]2-Apr-14[/TD]
[TD]EFG[/TD]
[TD]GA[/TD]
[TD]Bed Sets[/TD]
[TD]CTN Flannel - 140 Gsm[/TD]
[TD]Yarn Dyed[/TD]
[TD]100% Cotton[/TD]
[TD][/TD]
[TD]2[/TD]
[TD] 6,720[/TD]
[TD]Set(s)[/TD]
[/TR]
[TR]
[TD]147[/TD]
[TD]201905 B[/TD]
[TD]2-Apr-14[/TD]
[TD]EFG[/TD]
[TD]GA[/TD]
[TD]Pillow Pair[/TD]
[TD]CTN Flannel - 140 Gsm[/TD]
[TD]Yarn Dyed[/TD]
[TD]100% Cotton[/TD]
[TD][/TD]
[TD]1[/TD]
[TD] 1,616[/TD]
[TD]Pair(s)[/TD]
[/TR]
[TR]
[TD]153[/TD]
[TD]102756[/TD]
[TD]10-Apr-14[/TD]
[TD]EFG[/TD]
[TD]GA[/TD]
[TD]Bed Sets[/TD]
[TD]CTN Flannel - 140 Gsm[/TD]
[TD]Yarn Dyed[/TD]
[TD]100% Cotton[/TD]
[TD][/TD]
[TD]2[/TD]
[TD] 65,504[/TD]
[TD]Set(s)[/TD]
[/TR]
[TR]
[TD]153[/TD]
[TD]102757[/TD]
[TD]10-Apr-14[/TD]
[TD]EFG[/TD]
[TD]GA[/TD]
[TD]Bed Sets[/TD]
[TD]CTN Flannel - 140 Gsm[/TD]
[TD]Yarn Dyed[/TD]
[TD]100% Cotton[/TD]
[TD][/TD]
[TD]1[/TD]
[TD] 38,604[/TD]
[TD]Set(s)[/TD]
[/TR]
[TR]
[TD]153[/TD]
[TD]102755[/TD]
[TD]10-Apr-14[/TD]
[TD]EFG[/TD]
[TD]GA[/TD]
[TD]Pillow Pair[/TD]
[TD]CTN Flannel - 140 Gsm[/TD]
[TD]Yarn Dyed[/TD]
[TD]100% Cotton[/TD]
[TD][/TD]
[TD]2[/TD]
[TD] 88,416[/TD]
[TD]Pair(s)[/TD]
[/TR]
[TR]
[TD]160[/TD]
[TD]104685[/TD]
[TD]18-Jun-14[/TD]
[TD]EFG[/TD]
[TD]HT[/TD]
[TD]Fitted Sheet[/TD]
[TD]CVC Jersey - 115 Gsm[/TD]
[TD]Dis/Reactive Dyed[/TD]
[TD]50/50 Poly / Cotton[/TD]
[TD]30's PC[/TD]
[TD]1[/TD]
[TD] 134,658[/TD]
[TD]Pc(s)[/TD]
[/TR]
[TR]
[TD]160[/TD]
[TD]104686[/TD]
[TD]18-Jun-14[/TD]
[TD]ABC[/TD]
[TD]HT[/TD]
[TD]Fitted Sheet[/TD]
[TD]CVC Jersey - 115 Gsm[/TD]
[TD]Dis/Reactive Dyed[/TD]
[TD]50/50 Poly / Cotton[/TD]
[TD]30's PC[/TD]
[TD]2[/TD]
[TD] 127,962[/TD]
[TD]Pc(s)[/TD]
[/TR]
</tbody>[/TABLE]


I need to Extract Data to another workbook with following criterias:

1) Date (Month Wise)
2) Customer
3) Factory
4) Quality
5) Article
6) Dyed or Printed
7) Units

The extracted Data should show a sum of Quantity which matches the above criterias

I would be gratefull if anyone can provide a Solution.

Regards,

Humayun

 

[SpillerBD]

You could use the Get And Transform capabilities to bring the data over and analyze. Then have that data rolled into a Pivot Table for summary and analysis.

You can also use the Advanced Filter feature or you might consider the Drill Down Feature of a Pivot Table and then move the resulting Sheet to the Target Workbook.
Both cases would require adding your summation line.

 

[hrayani]

Thanks for the reply,

I tried the pivot table but to no avail as i dont know much about it - i mean how to use it.

I do have an option of filtering the record and copying it to the required sheet but i will have to do it every time the criteria changes.

I noticed in my post that there is need to explain myself a bit more.

On the Report sheet i will select

1) Customer
2) Factory
3) Date.

Now as soon as these 3 criterias are selected - i want the data from the mother source to be extracted to the reports sheet with showing Sum of Quantity of Quality, Article, Dyed or Printed & Units.

 

[SpillerBD]

Excel version your using? (Just in case you're on something terribly old like 2007 or before.)

 

[SpillerBD]

Watch Mike Girvin
Advanced Filter: https://www.youtube.com/watch?v=SU1qGbN6Rs8
Pivot Table and Slicers : https://www.youtube.com/watch?v=Sv9-P50CGog

Mike has a lot of videos and there may be one that may more nearly fit your use-case.

 

[hrayani]

Thanks.... I will look into that