Frequency...?

[How_Do_I]

Hello all, how could I use frequency to look at the following data please…<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I’d only want suggested formulas to work when my data columns (numbers in white cells) add up to 12… All do in the sample set.<o></o>
<o> </o>
I’d want to know how many different sets there are. (i.e. 7 1 4 in G, N and AE are a set which appears 3 times)..<o></o>
<o> </o>
Finally, could I pull and list the sets say in AU1:AV1… The next set would go underneath but ideally I’d want the set with the largest amout of combinations in AU1… I’m say column AU ranked…<o></o>
<o> </o>
Any ideas please?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV
1	FT	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44		3	7, 1, 4
2	W	5	3	8	7	6	7	4	8	6	2	4	4	7	6	4	2	5	7	6	5	6	8	3	5	7	5	6	4	5	7	4	5	6	2	7	6	5	6	9	6	2	7	4	5
3	D	1	6	3	2	4	1	4	2	4	6	3	1	1	2	3	6	2	3	4	3	3	2	4	3	3	4	4	6	3	1	3	6	3	4	2	2	4	3	1	2	6	3	5	3
4	L	6	3	1	3	2	4	4	2	2	4	5	7	4	4	5	4	5	2	2	4	3	2	5	4	2	3	2	2	4	4	5	1	3	6	3	4	3	3	2	4	4	2	3	4
Sheet1

 

[How_Do_I]

Beautiful Brian... that works a treat, thanks mate...!

 

[snoopyhr]

Not a very clean solution but maybe as an alternative just in case you want to locate the position of repeated reading and also want to know how many unique readings you may have.

Excel Workbook
	C	D	E	F	G	H	I
1	FT	1	2	3	4	5	6
2	W	5	3	8	7	6	7
3	D	1	6	3	2	4	1
4	L	6	3	1	3	2	4
5		5,1,6	3,6,3	8,3,1	7,2,3	6,4,2	7,1,4
6
7
8	Count of unique readings	23
9	Readings	Frequency	FT No, Location
10	5,1,6	1	1
11	3,6,3	1	2
12	8,3,1	1	3
13	7,2,3	2	4	35
14	6,4,2	4	5	9	19	27
15	7,1,4	3	6	13	30
16	4,4,4	1	7
17	8,2,2	2	8	22
18	2,6,4	3	10	16	41
19	4,3,5	3	11	15	31
20	4,1,7	1	12
21	6,2,4	3	14	36	40
22	5,2,5	1	17
23	7,3,2	3	18	25	42
24	5,3,4	4	20	24	29	44
25	6,3,3	3	21	33	38
26	3,4,5	1	23
27	5,4,3	2	26	37
28	4,6,2	1	28
29	5,6,1	1	32
30	2,4,6	1	34
31	9,1,2	1	39
32	4,5,3	1	43
Sheet2

 

[How_Do_I]

Thanks snoopyhr I'll certainly have a look at that...

If I understand your formula for the count, I note with interest how you and Brian both counted the results... I took the count from the data...

Excel Workbook
	A	B
7		23
Sheet1

Not sure which way to go as I have 3 working solutions for the count now!

 

[How_Do_I]

Out of interest, here is another way to do this... I found something that Aladin had shown me... you need morefunc for this one...

Excel Workbook
	B	C
5	5, 3, 1	5, 2, 2
6
7	2	2
8
9	5, 3, 1	1
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[jasonb75]

As a sidenote, not much use now as this is done, but for future reference.

If you have morefunc you don't need the CONCATRANGE udf, morefunc comes with MCONCAT which serves the same purpose.

 

[How_Do_I]

Thanks Jason... that IS helpful as I can remove more VBA and use MCONCAT... thanks for pointing that out.