"filtration" of dataset

[gordom]

Hi everyone.
I've got 2 spreadsheets. The first one, named "DATA" consists of 4 columns:
column A: product ID (thousands of records; unique numbers)
column B: name of group where each product belongs to (there are
hundreds of product groups; each group consists of several products)
column C: product description
column D: product price

It's more less like this:

product ID |name of group |product desc.| price
| | |
Product_1 | Group_1 | abc |x_EURO
Product_2 | Group_1 | def |x_EURO
Product_3 | Group_1 | fgh |x_EURO
Product_4 | Group_1 | ijkl |x_EURO
Product_5 | Group_2 | mn |x_EURO
Product_6 | Group_2 | op |x_EURO
Product_7 | Group_3 | xyz |x_EURO
Product_8 | Group_3 | fg |x_EURO
Product_9 | Group_3 | por |x_EURO
..
..
..


There is also a second spreadsheet named "GROUPS". There is a combo box
with a list of all groups. Now, after selecting a name of group, I would
like to receive all of its members (product ID will be sufficient).
Unfortunately I don't know what kind of formula or formulas should be
used to do so. I don't want to use a filtration from the main menu. Any
clues?

Regards,

gordom

 

[Max]

Try a pivot - its fast n easy to set-up, and it gives you the
functionalities you seek in a matter of seconds

Steps in xl2003
Assume the 2 key col headers in your source data's cols A & B are ProdID,
Group
Select cols A & B, click Data > Pivot table
Drag n drop ProdID into both the ROW and DATA areas
Drag n drop Group into PAGE area
Click OK > Finish. That's it.

Hop over to the pivot sheet for the desired results
The pivot's "filter" droplist for the Group is at the top (in B1). It'll
show as (All), but you can individually select each group as desired via the
droplist menu, and display the full listing of the product ids associated
with it.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik

 

[Max]

Missing one line in steps earlier, injected:

Select cols A & B, click Data > Pivot table

Click Next > Next. In step 3 of the wiz., click layout, then:

Drag n drop ProdID into both the ROW and DATA areas
Drag n drop Group into PAGE area
Click OK > Finish. That's it.

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---

 

[Lars-Åke Aspelin]

Hi everyone.
I've got 2 spreadsheets. The first one, named "DATA" consists of 4 columns:
column A: product ID (thousands of records; unique numbers)
column B: name of group where each product belongs to (there are
hundreds of product groups; each group consists of several products)
column C: product description
column D: product price

It's more less like this:

product ID |name of group |product desc.| price
| | |
Product_1 | Group_1 | abc |x_EURO
Product_2 | Group_1 | def |x_EURO
Product_3 | Group_1 | fgh |x_EURO
Product_4 | Group_1 | ijkl |x_EURO
Product_5 | Group_2 | mn |x_EURO
Product_6 | Group_2 | op |x_EURO
Product_7 | Group_3 | xyz |x_EURO
Product_8 | Group_3 | fg |x_EURO
Product_9 | Group_3 | por |x_EURO
.
.
.


There is also a second spreadsheet named "GROUPS". There is a combo box
with a list of all groups. Now, after selecting a name of group, I would
like to receive all of its members (product ID will be sufficient).
Unfortunately I don't know what kind of formula or formulas should be
used to do so. I don't want to use a filtration from the main menu. Any
clues?

Regards,

gordom

If you want a macro you may try this:

Sub DropDown1_Change()

first_row = 2 'change this to 1 if you don't have a header row
last_row = Worksheets("DATA").Range("A65536").End(xlUp).Row

group_name = ActiveSheet.DropDowns("Drop Down
1").List(ActiveSheet.DropDowns("Drop Down 1").ListIndex)

output_row = Selection.Row 'change to a constant if you want
output_column = Selection.Column 'change to a constant if you want

Worksheets("GROUPS").Cells(output_row, output_column).Value =
"Products in group " & group_name

For input_row = first_row To last_row
If Worksheets("DATA").Cells(input_row, "B") = group_name Then
output_row = output_row + 1
Worksheets("GROUPS").Cells(output_row, output_column) =
Worksheets("DATA").Cells(input_row, "A")
End If
Next input_row

End Sub

Hope this helps / Lars-Åke

 

[gordom]

W dniu 2008-12-30 09:58, Max pisze:

Missing one line in steps earlier, injected:
Click Next > Next. In step 3 of the wiz., click layout, then:

Thanks Max for your help.

gordom

 

[gordom]

thanks Lars-Ã…ke,

gordom

 

[Max]

welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik