identify most common combination that gets to a particular value

[exceltripping]

I have a data sheet with 20 columns across 1000 lines.

The last column has various values in it and I am interested in, lets say value 'x'. Is there a way I can identify what are the most recurring value combinations in columns 1 - 19 that gave the highest number of the value 'x'?

I hope that makes sense. Grateful for any input.

[jeffreybrown]

The way I read what your asking, column 20 holds the 'x' you want to search for in columns 1 - 19.

In U1 >> =COUNTIF(A1:S1,T1) >> That will count how many time 'x' shows up in the row, but if you want to Sum those 'x' >> =SUMIF(A1:S1,T1) >> in V1

[exceltripping]

Thank you. Not quite and apologies for not being clear.

Columns 1 - 19 will have different values in diff. format. So lets just say (for example):

Column 1 = Dates
Columns 2 = Animal names
Column 3 = Month names
Column 4 = Latin words
etc etc

What I am trying to find out is what combination across columns 1 - 19 brings up the value 'x' in column 20 the most. Hope that makes better sense.

[jeffreybrown]

How about a sample file with some manually entered answers you expect? The sample file does not have to be big, just big enough to give us a good picture with different scenarios.

[shg]

You could calculate the correlation of column 2 = "dog" and column 20 = "x"

[exceltripping]

Ok so I have attached a very rough sample of the data. Column V in this case is the magic number and I am trying to establish is what combination across all the columns before column V brings up the number '1' the most.

In the data provided there may not actually be any pattern as such however my actual data is huge and there are likely to be patterns in the data.

Thank you

[jeffreybrown]

Sorry, but I don't quite see what you seek here. Can you give some insight into why for instance row 5 has a magic number of 2 and row 12 has a magic number of 5?

[shg]

For example,

	R	V	W	X	Y	Z
1	country	Magic Number		country	correl
2	FR	1		FR	-0.15	Y2: =CORREL(--($R$2:$R$12 = X2), --($V$2:$V$12 = "1"))
3	IRE	1		IRE	0.31
4	IRE	1		GB	-0.29
5	FR	2
6	IRE	3
7	IRE	1
8	IRE	0
9	GB	4
10	IRE	5
11	IRE	1
12	FR	5

A 1 is more likely to come from Ireland than France or Great Britain.

[exceltripping]

Sorry, but I don't quite see what you seek here. Can you give some insight into why for instance row 5 has a magic number of 2 and row 12 has a magic number of 5?

The example I had provided was random numbers/data inserted. There was no particular logic behind the sample data.

[exceltripping]

For example,

	R	V	W	X	Y	Z
1	country	Magic Number		country	correl
2	FR	1		FR	-0.15	Y2: =CORREL(--($R$2:$R$12 = X2), --($V$2:$V$12 = "1"))
3	IRE	1		IRE	0.31
4	IRE	1		GB	-0.29
5	FR	2
6	IRE	3
7	IRE	1
8	IRE	0
9	GB	4
10	IRE	5
11	IRE	1
12	FR	5

A 1 is more likely to come from Ireland than France or Great Britain.

Thank you. So this would be done with each column as opposed to a couple of columns combined? Could you combine correlation of 2 columns to assess outcome. in other words, which two column combinations are most likely to give you the magic number of '1' ?

Thank you

[shg]

So this would be done with each column as opposed to a couple of columns combined?

Perhaps there is a way, but the numbers of combinations of 20 columns explodes the problem.

There's no need to quote my posts back to me -- it's just clutter.

[exceltripping]

And that there is the problem..too many combinations..

Even if you could check two columns combination - how would you do that please? Thank you

[shg]

	Q	R	V	W	X	Y	Z	AA
1	Month	country	Magic Number		country	month	correl
2	Jan	FR	1		FR	Jan	0.04	Z2: {=CORREL(($R$2:$R$12 = X2) * ($Q$2:$Q$12 = Y2), --($V$2:$V$12 = "1"))}
3	Jan	IRE	1		IRE	Jan	0.52
4	Feb	IRE	1		GB	Jan	#DIV/0!
5	Nov	FR	2
6	Nov	IRE	3
7	Dec	IRE	1
8	Nov	IRE	0
9	Jun	GB	4
10	Dec	IRE	5
11	Jan	IRE	1
12	Jan	FR	5