Seeking Gurus: Create a dynamic and unique sublist, based on multiple criteria. I'm stuck...

[pau11y]

Excel 2007, Windows 7.

Most details are shown in the spreadsheet below. I would like it to be dynamic because the quarterly and annual data dumps I'm working with are are hundreds to thousands of lines.

Have the list be sorted, which is a part of the first attempt, would be nice but is not necessary. At this point, just being able to generate the dynamic list would be fantastic.

Any thoughts or suggestions are much appreciated!

Excel 2007
	A	B	C	D	E
1	product lines:	consist of these product subtypes
2	Widget series:	Widget.type1	Widget.type2
3	Fidget series:	Fidget.type
4	Gidget series:	Gidget.type1	Gidget.type2	Gidget.type3
5
6	data dump of parts sold or used in repairs
7	product subtype repaired	part number shipped
8	Widget.type1	a1
9	Fidget.type	a2
10	Gidget.type1	a3
11	Gidget.type2	a4
12	Gidget.type3	a5
13	Fidget.type	a6
14	Widget.type2	a7
15	Gidget.type2	a8
16	Gidget.type2	a9
17	Gidget.type3	a10
18	Widget.type2	a11
19	Widget.type1	a12
20
21
22	My attempted calculations to summarize the instances of sales/repairs for each product line (i.e. "Widget series", etc):
23
24
25	First step, calculated the number of instances for each series
26	I defined three array constants containing the product subtypes for each product line:
27		below names are defined	"actual" array constant text saved to the names
28	Widget series:	Widget.list	={"Widget.type1","Widget.type2"}
29	Fidget series:	Fidget.list	="Fidget.type"
30	Gidget series:	Gidget.list	={"Gidget.type1","Gidget.type2","Gidget.type3"}
31
32	This worked fine:
33		Widget series	Fidget series	Gidget series
34	# of instances of each series	4	2	6
35
36
37
38	FAILED ATTEMPT NUMBER 1: Generate a dynamic, sorted and unique list of the part numbers shipped/used for each product line:
39
40
41
42		Widget series	Fidget series	Gidget series
43		a1	a2	a3
44		FALSE	a6	FALSE
45		FALSE		FALSE
46		a12		FALSE
47				FALSE
48				FALSE
49
50	Comments: It works for the 'Fidget' series, but not the others because the 'Fidget.list' array constant only has one element (only one product subtype). The logic of the 'IF' function in the 'SMALL' function is flawed for the other two ".list"s, that have multiple elements in the array constants. The 'IF' statement "logical test" has 'TRUE'S in the right spots (of the TWO dimensional array) for the 'Widget' and 'Gidget' lists (that will match the top level 'INDEX' function), but the 'ROW' function (in the [value if TRUE] spot of the 'IF') doesn't point to the right spot in the two dimensional array and therefore returns the wrong value (a "FALSE"). I need a "return the array location if TRUE" function.
51
52
53
54
55
56
57
58
59	FAILED ATTEMPT NUMBER 2: Gave up on sorted. Generate a dynamic and unique list of the part numbers shipped/used for each product line:
60
61
62		Widget series	Fidget series	Gidget series
63		#NUM!	#NUM!	#NUM!
64		#NUM!	#NUM!	#NUM!
65		#NUM!		#NUM!
66		#NUM!		#NUM!
67				#NUM!
68				#NUM!
69
70	Comments: Without the sorting requirement, things could be much easier. I could just let 'SMALL' decide which is smallest to largest and report. But the 'SMALL' function apparantly isn't happy with things inside quotes, and I haven't found a clever way to remove them yet.
71
72
73
Sheet11

Cell Formulas
Range	Formula
B34	{=SUM(IF($A$8:$A$19=Widget.list,1)*1)}
B43	{=IF(ROWS(B$43:B43)<=B$34,INDEX(IF($A$8:$A$19=Widget.list,$B$8:$B$19),SMALL(IF($A$8:$A$19=Widget.list,ROW($A$8:$A$19)-ROW($A$8)+1),ROWS(B$43:B43))),"")}
B63	{=IF(ROWS(B$63:B63)<=B$34,SMALL(IF($A$8:$A$19=Widget.list,$B$8:$B$19,"zzz"),ROWS(B$63:B63)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Widget.list	={"Widget.type1","Widget.type2"}

Thanks!

 

[pau11y]

I should also add that everything above Row 22, is the type of data I'm messing with. From Row 22 and on, I'm attempting to share my efforts thus far.

This is my first attempt in this forum, took a couple of attempts to get that html table halfway useful. If you would like anything stated differently or clarified, please let me know.

Thanks!

 

[Aladin Akyurek]

I should also add that everything above Row 22, is the type of data I'm messing with. From Row 22 and on, I'm attempting to share my efforts thus far.

This is my first attempt in this forum, took a couple of attempts to get that html table halfway useful. If you would like anything stated differently or clarified, please let me know.

Thanks!

I suppose the data is in A7:B19. This is a dump from somewhere into Excel, possibly by means of a manual routine.
Would you please list one by one the results you want to see? Note that the formulas, working or not working, never fully describe a problem.

 

[Jerry Sullivan]

Hi and Welcome to the Board,

If your lists of unique sublists can be organized vertically as shown below instead of horizontally,
this could be done fairly easily with a PivotTable.

Excel Workbook
	I	J
7	Unique Sublists	Count
8	Fidget series:
9	a2	1
10	Gidget series:
11	a10	1
12	a3	1
13	a4	1
14	a5	1
15	a8	1
16	a9	1
17	Widget series:
18	a1	1
19	a11	1
20	a12	1
21	a7	1
Sheet

EDIT: Hi Aladin, I didn't see your post until after posting my reply. It would be helpful if the desired result was more explicit. I based my response on the assumption that the OP's desired result is shown in B42:D48 minus the True/False display.

 

[pau11y]

I suppose the data is in A7:B19. This is a dump from somewhere into Excel, possibly by means of a manual routine.
Would you please list one by one the results you want to see? Note that the formulas, working or not working, never fully describe a problem.

Hi and Welcome to the Board,

If your lists of unique sublists can be organized vertically as shown below instead of horizontally,
this could be done fairly easily with a PivotTable.


EDIT: Hi Aladin, I didn't see your post until after posting my reply. It would be helpful if the desired result was more explicit. I based my response on the assumption that the OP's desired result is shown in B42:D48 minus the True/False display.

Thank you two for the prompt responses. Some clarification is definitely needed. I was taken by surprise that the availability to 'edit' my OP went away after a couple of rounds. I spent those getting the table to look decent and hadn't gotten the text settled.

Rows 8-19 show the raw data, there will actually be many more columns (e.g. posting date, part cost, part price, etc); but these two are relevant to this post's question.

Rows 42-48 and 62-68 represent my two respective attempts at the desired result:
Attempt #1: The specific desired result for Rows 42-48 is defined in B38. The (failed) equation used is shown in the equation table at bottom for B44, C44 and D44. C44 and D44 contain versions of the B44 equation appropriate for those respective series. i.e. referencing columns C and D along with 'Fidget.list' and 'Gidget.list' as necessary. My comments on why, I think, it failed are in B50.

Attempt #2: The specific desired result for Rows 62-68 is defined in B59. The (failed) equation used is shown in the equation table at bottom for B63. Similarly, this is just the "Widget Series" equation. C43 and D43 contain versions of the B63 equation appropriate for those respective series. i.e. referencing columns C and D along with 'Fidget.list' and 'Gidget.list' as necessary. My comments on why, I think, it failed are in B70.

Excel 2007
	A	B	C	D	E
1	product lines:	consist of these product subtypes
2	Widget series:	Widget.type1	Widget.type2
3	Fidget series:	Fidget.type
4	Gidget series:	Gidget.type1	Gidget.type2	Gidget.type3
5
6	data dump of parts sold or used in repairs
7
8	product subtype repaired	part number shipped
9	Widget.type1	a1
10	Fidget.type	a2
11	Gidget.type1	a3
12	Gidget.type2	a4
13	Gidget.type3	a5
14	Fidget.type	a6
15	Widget.type2	a7
16	Gidget.type2	a8
17	Gidget.type2	a9
18	Gidget.type3	a10
19	Widget.type2	a11
20	Widget.type1	a12
21
22
23	My attempted calculations to summarize the instances of sales/repairs for each product line (i.e. "Widget series", etc):
24
25
26	First step, calculated the number of instances for each series
27	I defined three array constants containing the product subtypes for each product line:
28		below names are defined	"actual" array constant text saved to the names
29	Widget series:	Widget.list	={"Widget.type1","Widget.type2"}
30	Fidget series:	Fidget.list	="Fidget.type"
31	Gidget series:	Gidget.list	={"Gidget.type1","Gidget.type2","Gidget.type3"}
32
33	This worked fine:
34		Widget series	Fidget series	Gidget series
35	# of instances of each series	4	2	6
36
37
38
39	FAILED ATTEMPT NUMBER 1: Generate a dynamic, sorted and unique list of the part numbers shipped/used for each product line:
40
41
42
43		Widget series	Fidget series	Gidget series
44		a1	a2	a3
45		FALSE	a6	FALSE
46		FALSE		FALSE
47		a12		FALSE
48				FALSE
49				FALSE
50
51	Comments: It works for the 'Fidget' series, but not the others because the 'Fidget.list' array constant only has one element (only one product subtype). The logic of the 'IF' function in the 'SMALL' function is flawed for the other two ".list"s, that have multiple elements in the array constants. The 'IF' statement "logical test" has 'TRUE'S in the right spots (of the TWO dimensional array) for the 'Widget' and 'Gidget' lists (that will match the top level 'INDEX' function), but the 'ROW' function (in the [value if TRUE] spot of the 'IF') doesn't point to the right spot in the two dimensional array and therefore returns the wrong value (a "FALSE"). I need a "return the array location if TRUE" function.
52
53
54
55
56
57
58
59
60	FAILED ATTEMPT NUMBER 2: Gave up on sorted. Generate a dynamic and unique list of the part numbers shipped/used for each product line:
61
62
63		Widget series	Fidget series	Gidget series
64		#NUM!	#NUM!	#NUM!
65		#NUM!	#NUM!	#NUM!
66		#NUM!		#NUM!
67		#NUM!		#NUM!
68				#NUM!
69				#NUM!
70
71	Comments: Without the sorting requirement, things could be much easier. I could just let 'SMALL' decide which is smallest to largest and report. But the 'SMALL' function apparantly isn't happy with things inside quotes, and I haven't found a clever way to remove them yet.
72
73
74
Sheet11

Cell Formulas
Range	Formula
B35	{=SUM(IF($A$9:$A$20=Widget.list,1)*1)}
B44	{=IF(ROWS(B$44:B44)<=B$35,INDEX(IF($A$9:$A$20=Widget.list,$B$9:$B$20),SMALL(IF($A$9:$A$20=Widget.list,ROW($A$9:$A$20)-ROW($A$9)+1),ROWS(B$44:B44))),"")}
B64	{=IF(ROWS(B$64:B64)<=B$35,SMALL(IF($A$9:$A$20=Widget.list,$B$9:$B$20,"zzz"),ROWS(B$64:B64)),"")}
C35	{=SUM(IF($A$9:$A$20=Fidget.list,1)*1)}
C44	{=IF(ROWS(C$44:C44)<=C$35,INDEX(IF($A$9:$A$20=Fidget.list,$B$9:$B$20),SMALL(IF($A$9:$A$20=Fidget.list,ROW($A$9:$A$20)-ROW($A$9)+1),ROWS(C$44:C44))),"")}
C64	{=IF(ROWS(C$64:C64)<=C$35,SMALL(IF($A$9:$A$20=Fidget.list,$B$9:$B$20,"zzz"),ROWS(C$64:C64)),"")}
D35	{=SUM(IF($A$9:$A$20=Gidget.list,1)*1)}
D44	{=IF(ROWS(D$44:D44)<=D$35,INDEX(IF($A$9:$A$20=Gidget.list,$B$9:$B$20),SMALL(IF($A$9:$A$20=Gidget.list,ROW($A$9:$A$20)-ROW($A$9)+1),ROWS(D$44:D44))),"")}
D64	{=IF(ROWS(D$64:D64)<=D$35,SMALL(IF($A$9:$A$20=Gidget.list,$B$9:$B$20,"zzz"),ROWS(D$64:D64)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Fidget.list	="Fidget.type"
Gidget.list	={"Gidget.type1","Gidget.type2","Gidget.type3"}
Widget.list	={"Widget.type1","Widget.type2"}

This table below shows the desired result data. While this may be achievable easily with filter, and pivot tables. The ideal solution is a dynamically linked list that can "hard referenced" in other worksheets. I will explore pivot tables further however.

Excel 2007
	G	H	I
15	Widget series	Fidget series	Gidget series
16	a1	a2	a3
17	a7	a6	a4
18	a11		a5
19	a12		a8
20			a9
21			a10
Sheet11

 

[Jerry Sullivan]

If you are going to potentially have thousands of rows of data, you might be better off with a PivotTable or VBA macro solution due to the calculation load needed for array formulas. It sounds like your preference for a formula-based solution because it you want it to be "dynamic", but the other two alternatives would only require a Refresh or click of a Button.

If you need this to be formula-based, below is one appoach.
Many of the formulas are adapted from those Aladin used in this thread:
http://www.mrexcel.com/forum/excel-questions/470371-unique-list-based-criteria.html

Employ a helper column to lookup the product line associated with each product subtype.

CSE Enter formula in C8, then copy down.

Excel Workbook
	A	B	C
7	product subtype repaired	part number shipped	Series
8	Widget.type1	a1	Widget series:
9	Fidget.type	a2	Fidget series:
10	Gidget.type1	a3	Gidget series:
11	Gidget.type2	a4	Gidget series:
12	Gidget.type3	a5	Gidget series:
13	Fidget.type	a6	Fidget series:
14	Widget.type2	a7	Widget series:
15	Gidget.type2	a8	Gidget series:
16	Gidget.type2	a9	Gidget series:
17	Gidget.type3	a10	Gidget series:
18	Widget.type2	a11	Widget series:
19	Widget.type1	a12	Widget series:
Sheet

Define Rvec as referring to:
=ROW(Sheet1!$B$8:$B$19)-ROW(Sheet1!$B$8)+1

CSE enter the formulas shown in G14 then copy across
Enter the formula in G15 then copy across
CSE enter the formulas shown in G16 then copy across and down

These 3 formulas should be copied across to as many cells as the maximum number of series you expect
The formulas in G16 should be copied down to as many cells as the maximum number of product subtypes you expect

Excel Workbook
	G	H	I	J
14	4	2	6
15	Widget series:	Fidget series:	Gidget series:
16	a1	a2	a3
17	a7	a6	a4
18	a11		a5
19	a12		a8
20			a9
21			a10
22
23
Sheet