Report Generator (Upload Certain Fields)

[James thunderbolt]

Hi,

Wondering if you can help me with 2 problems I am having issues with.

Firstly, I have created a sheet that generates a report (Basically copies data from certain fields) that are within the same selected region and product I select.

I have attached a sheet to show you what it is I need to do. You can select the region and products, I'm basically need the populate report button to copy all the data from the "data" tab, from the relevant fields (those fields are highlighted below).

The 2nd issue I also wondering if this is possible to do, I would like if possible to create a data validation list, taking a whole range but only showing unique values -- is that possible ?

Thanks
Bolt

 

[Ashhu]

Hi James
I worked your 1st request. please validate whether your requirement is matched with the attached solution sheet.

Thanks
Ashwin

 

[James thunderbolt]

Hi

Thanks for this, it just poses 2 problems for me.

My original dataset is many thousands of times larger, I have tried to apply your formulas to the larger dataset and it just takes up too much time and memory - it is struggling to handle such a large workload, which is why I'd prefer to be able to upload (or simply copy) the data over, and that way it is just static data.

2ndly, the data set I am using is external and constantly changing - so exporting it and converting it to a different table each time creates new problems - which is why I believe it would be easier not as a table.

Please let me know your thoughts

 

[Ashhu]

Hi James,
Sorry! I understand these issue with larger dataset.
I believe we can avoid table but speed issue will remain.
I request Ninjas here to look into this and sure they will come up with better approach to avoid.
@Hui @Marc L @Luke M @Khalid NGO

-Regards
Ashwin

 

[Khalid NGO]

@Khalid NGO

-Regards
Ashwin

I am zero in VBA

 

[Ashhu]

I am zero in VBA

Khalid Bhai

You can start then, i am sure you fligh high like your DP in no time.

 

[Craig Hatmaker]

Use Advanced Filter

Sheets("Report").Activate
Sheets("Data").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
  CriteriaRange:=Range("Criteria"), CopyToRange:=Range("Extract"), Unique:=False

1) Change Report!B2 from "Product" to "Product Type" to agree with data headings
2) Name Report!A5:G5 "Extract"
3) Report!A2:B3 was already named "Criteria"

See attached file.

 

[Chihiro]

For the unique list. You'd either need to use helper column populated by formula.
Or you can use vba to populate range with unique values.

See attached for VBA example (I created "List" sheet, this can be hidden as well). I didn't make one for "Product Type".

Sub UniqueList()
Dim dict As Object
Dim x As Variant
Dim i As Long, lRow As Long
Dim lws As Worksheet, dws As Worksheet
'Create dictionary object
Set dict = CreateObject("Scripting.Dictionary")
Set dws = Sheets("Data")
Set lws = Sheets("List")

lRow = dws.Range("A" & Rows.Count).End(xlUp).Row

'Put range into array
x = dws.Range("E2:E" & lRow)

'Create dictionary item, using value as key. By design, dictionary only allows unique values for keys
For i = 1 To UBound(x, 1)
    dict(x(i, 1)) = 1
Next i

'Transpose dictionary keys to range and sort alphabetically
With lws
    .Cells.Clear
    .Range("B2").Resize(dict.Count) = Application.Transpose(dict.keys)
    .Cells(1, 2) = "Region"
    lRow = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("B1:B" & lRow).Sort Key1:=Range("B1"), Order1:=xlAscending, _
    Header:=xlYes
End With

'Add the range to named list or overwrite existing
ThisWorkbook.Names.Add _
    Name:="lstRegion", _
    RefersTo:=lws.Range("B2:B" & lRow)

End Sub

Edit: FYI - You can sort dictionary by keys and directly add to named range. But I don't recommend it if you are not comfortable in VBA & Array/Dictionary. If you don't need the list sorted, you can replace part after "RefersTo:=" with "dict.keys"

 

[Marc L]

Hi !

Unique list can be extracted also via an advanced filter …
​

 

[Chihiro]

Oh right! Forgot about Unique argument for the advanced filter.

 

[Marc L]

But you are not wrong ! In case of huge data, a Dictionary may be faster …
​

 

[Craig Hatmaker]

Just to mention - since my #1 Rule of VBA is to not use VBA to do what XL does natively - This can be achieved in XL 2010 and higher using Auto-Filters and/or Slicers.

1) Click on any data cell in sheet "Data" and use CTRL-T to turn it into a table. Check "My Table has Headers".
2) In the TABLE TOOLS DESIGN tab check the Total Row option and using the dropdown in the total row, set the "Name" column's subtotal function to COUNTA.
3) If auto-filters are not displayed, click on any cell in the table and use SHIFT-CTRL-L to turn them on.
4) Optionally, use TABLE TOOLS DESIGN Insert Slicer and create them for Region and Product Type.

No need for a second sheet. No macros. Fully dynamic. As fast as it gets. Simple.