Number combinations

[bmag]

In column A, I have number different combinations in each first cell, each combination has 5 numbers ranging from 1-30, i have the numbers separated by just a space. Is there a way to select the cell range in column A, and find different number combinations, for example: how many times the numbers 1 and 15 appear together in a sequence? This is how each cell looks:

1 11 12 15 28
2 5 6 17 29
3 8 19 21 22

 

[BenMiller]

First, insert four new (blank) columns to the right of your data. Then highlight your column with the data and do a text-to-column with a space as your delimiter. You now have each number in its own column, which makes everything a lot easier.

 

[bmag]

-- removed inline image ---
ok this is what it looks like now, if i highlight my five columns, is there a formula to determine how many times the #1 and #7 are in a sequence?

 

[BenMiller]

Your spreadsheet isn't showing up...

 

[bmag]

I believe it would be a Countif function, say i wanted to know how many times 1 and 32 appear "in the same row", not just how many times they appear by themselves.

 

[BenMiller]

Yes. I would say to do a COUNTIF function in the next column then total the column.

 

[AdamL]

Here's two ways, one using the space-delimited in one cell, the other reading values across five columns:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	1 11 12 15 28*	1	11	12	15	28		1st value	2nd value	Column A	Columns B:F
2	2 5 6 17 29	2	5	6	17	29		1	32	2	2
3	3 8 19 21 22	3	8	19	21	22
4	1 4 5 32 35	1	4	5	32	35
5	32 32 32 32 32	32	32	32	32	32
6	1 1 1 1 1	1	1	1	1	1
7	1 7 32 1 8	1	7	32	1	8
Sheet1

HTH
Adam

 

[bmag]

yessssss! that's exactly what i'm looking for. Thank you so much!