Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"filtration" of dataset
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"filtration" of dataset
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 --- "gordom" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"filtration" of dataset
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 --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"filtration" of dataset
On Tue, 30 Dec 2008 09:21:07 +0100, gordom
wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"filtration" of dataset
W dniu 2008-12-30 09:58, Max pisze:
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. Thanks Max for your help. gordom |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"filtration" of dataset
thanks Lars-Ã…ke,
gordom |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"filtration" of dataset
welcome
-- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "gordom" wrote in message ... Thanks Max for your help. gordom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) |