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]

Any ideas with this one please?

 

[meldoc]

Hello gus,

This isn't elegant, but if you can add a helper Row try this.
Note: lots of columns hidden, my Htlm maker won't display more than 20 columns by default.

Excel Workbook
	A	G	H	N	AD	AE	AT	AU	AV
1	FT	6	7	13	29	30		3	7,1,4
2	W	7	4	7	5	7
3	D	1	4	1	3	1
4	L	4	4	4	4	4
5		7,1,4	4,4,4	7,1,4	5,3,4	7,1,4
Sheet1

Excel 2010

Cell Formulas
Range	Formula
AU1	=COUNTIF(B5:AS5,AV1)
AV1	=ConCatRange(N2:N4)
G5	=ConCatRange(G2:G4)

 

[How_Do_I]

Hi Brian, your formula certainly got me started on this one thanks...

 

[How_Do_I]

Hi all…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
How can I sort the following out please…
<o> </o>
The first Jeanie is working presumably because all my ranges in Row5 are full of data. (Note I haven’t shown all 46 columns of data)
<o> </o>
The second Jeanie isn’t working because I presumably need an expanding range on Row5…
<o> </o>
I know how to find my last column in Row5 =MATCH(BigStr,B5:AU5) where BigStr = =REPT("z",255)
<o> </o>
However I can’t work out how to get my expanding range in to my original formula.
<o> </o>
Any takers please?

Excel Workbook
	A	B	C	D
5		5, 1, 6	3, 6, 3	8, 3, 1
6
7		23
8
9	1	2, 4, 6	1
10	2	2, 6, 4	3
11	3	3, 4, 5	1
Sheet1

Excel Workbook
	A	B	C	D
5		5, 1, 6	3, 6, 3
6
7		2
8
9	1	#N/A	0
10	2	#N/A	0
Sheet1

 

[How_Do_I]

Can anyone see what is wrong with this please...

I've created my expanding range AND it works on all occasions except when there's only ONE piece of data in "LongR"...

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

#VALUE!

</td></tr></table></td></tr></table> [/B]

 

[jasonb75]

Delete everything except A1:AS4 inclusive.

Enter a row heading, such as "Concat" in A5

Enter your rangeconcat formula in B5 and fill to AS5

Select the range AV1:AV45

Press F2

Enter =TRANSPOSE($A$5:$AS$5) in the formula bar and array confirm with Shift Ctrl Enter.

Insert a pivot table, with the Concat field used for row labels and values.

The values column should default to count, which you should then be able to sort ascending in the table.

 

[How_Do_I]

Thanks Jason... I'll try that but I really don't want to use a pivot table...

If after looking at this for most of the afternoon and having cells 2 to 46 of my range of interest giving me the expected return would you give in at this point and switch to a pivot table!!!

 

[How_Do_I]

I’ll start this again from scratch to try and eliminate the confusion I've caused
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
My data starts in B5 and goes across my workbook… The data can extend to AU5…
<o> </o>
There could be no returns, i.e. range blank but ALL the range contains formulas.
<o> </o>
What I want to do is count the different combinations (7 in the Jeanie below as 3. 6, 3 appears twice)…
<o> </o>
List the combinations by formula
<o> </o>
Count the combinations…
<o> </o>
So my expected returns are as the Jeanie…
<o> </o>
Any ideas please? Formula solution only.

Excel Workbook
	B	C	D	E	F	G	H	I
5	5, 1, 6	3, 6, 3	3, 6, 3	7, 2, 3	6, 4, 2	7, 1, 4	4, 4, 4	8, 2, 2
6
7	7
8	List	Count
9	3, 6, 3	2
10	4, 4, 4	1
11	5, 1, 6	1
12	6, 4, 2	1
13	7, 1, 4	1
14	7, 2, 3	1
15	8, 2, 2	1
Sheet1

 

[meldoc]

Hi Gus

How about.

Excel Workbook
	A	B	C	D	E	F	G	H
5	5, 1, 6	3, 6, 3	3, 6, 3	7, 2, 3	6, 4, 2	7, 1, 4	4, 4, 4	8, 2, 2
6
7	7
8	List	Count
9	5, 1, 6	1
10	3, 6, 3	2
11	7, 2, 3	1
12	6, 4, 2	1
13	7, 1, 4	1
14	4, 4, 4	1
15	8, 2, 2	1
Sheet1

Excel 2010

Cell Formulas
Range	Formula
A7	=COUNT(B9:B21)
B9	=IF(A9="","",COUNTIF($A$5:$H$5,A9))

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Copy the formulas is A9:B9, down as far as you need.