Extracting Data from a Larger Body of Data

[Graemea]

Hi,


I have monthly sales data going back 20 years for a long list of products, arranged as:


Column A: Product Codes
Column B: Product Names
Column C: Currency Codes of Sales (e.g. USD)
Column D: Month-end Dates
Column E: Monthly Sales Growth Rates
Column F: Month-end Sales Values


So, for example, the product with code AAA may have 240 months of such data, while BBB has only 200 months of data.


In the range H1:H55 is a list of 55 product codes for which I'd like to extract their monthly data from the larger body of data in columns
A:F.


The long way to do this is to apply a filter to the data and select the fund codes one by one but this is quite time-consuming and will need
to be done monthly.


Can someone please suggest a better way to do this?


Thanks!

 

[sandy666]

maybe Power Query aka Get&Transform

 

[Akuini]

In the range H1:H55 is a list of 55 product codes for which I'd like to extract their monthly data from the larger body of data in columns A:F.

Do you want to extract the 55 product codes all at once to a sheet or each product code to separate sheet?

 

[Graemea]

All at once to a single sheet, please

 

[sandy666]

with Power Query

[SIZE=1]// EndResult
let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Result = Table.NestedJoin(Source1,{"Product Codes"},Source2,{"Product Codes"},"Table2",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Result, "Table2", {"Product Names", "Currency Codes", "Month-end Dates", "Monthly Sales Growth Rates", "Month-end Sales Values"}, {"Product Names", "Currency Codes", "Month-end Dates", "Monthly Sales Growth Rates", "Month-end Sales Values"})
in
    Expand[/SIZE]

where Table1 is your filter table
and Table2 is your large table

you can load EndResult wherever you want

 

[Graemea]

Thanks very much, Sandy.

Unfortunately I don't have access to Power Query at work

 

[sandy666]

it means there is Excel less than 2016

pity

have a nice day

 

[Akuini]

Ok, try this:
Run the code, it will put the result in sheet2, you may change that to suit.

[FONT=Lucida Console][COLOR=Royalblue]Sub[/COLOR] a1112986a()
[I][COLOR=Dimgray]'https://www.mrexcel.com/forum/excel-questions/1112986-extracting-data-larger-body-data.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] ary
ary = Application.Index(Application.Transpose(Range([COLOR=Darkcyan]"H1:H55"[/COLOR])), [COLOR=Brown]1[/COLOR], [COLOR=Brown]0[/COLOR])

    ActiveSheet.AutoFilterMode = False
    [COLOR=Royalblue]With[/COLOR] Range([COLOR=Darkcyan]"A1:F"[/COLOR] & Cells(Rows.count, [COLOR=Darkcyan]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
        .AutoFilter Field:=[COLOR=Brown]1[/COLOR], Criteria1:=ary, Operator:=xlFilterValues
        .SpecialCells(xlCellTypeVisible).Copy Sheets([COLOR=Darkcyan]"Sheet2"[/COLOR]).Range([COLOR=Darkcyan]"A1"[/COLOR])
        .AutoFilter
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[Graemea]

Thanks very much, Akuini!

Works perfectly

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.