Return Header if specified row contains a value

[Powkiwi90]

I have a data set that looks like this:

A B C D E F
P 2 0 2 0 0 3
Q 0 3 1 1 1 0
R 6 0 0 0 1 0
S 0 5 0 3 0 0
T 0 0 5 0 0 2
U 0 0 7 0 0 0


I need to be able to search that table by putting selecting an input value from P-Q then if that selected row has a value greater than 0.
I need it to return any other rows that also have a non-zero value in that column, how many of the columns share a non-zero value and which ones they are.

So for example:

Value:
P
Result :
Q - 1 - C
R - 1 - A
T - 2 - C, F

(another way of thinking would be P shares 1 similar column with Q which is C, 1 similar column with R which is A and 2 similar columns with T which are C and F)

Value:
R
Result :
P - 1 - A
Q - 1 - E

Value:
S
Result:
Q - 2 - B, D

Ideally these three results would be in separate cells if possible. So the result for the last value, S, would look like | Q | 2 | B, D |

If I can get that going, that would be most amazing. I thought a pivot table might be the way, but its not really going at all.

Many many many thanks in advance!

 

[Powkiwi90]

Just realized i could have made that more simple. The numbers in the cells of the table are unimportant. If need be i could replace any numbers with 1.

 

[Marcelo Branco]

You need complex formulas to accomplish what you want. Something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[TD="bgcolor: #DCE6F1"]

M​

[/TD]
[TD="bgcolor: #DCE6F1"]

N​

[/TD]
[TD="bgcolor: #DCE6F1"]

O​

[/TD]
[TD="bgcolor: #DCE6F1"]

P​

[/TD]
[TD="bgcolor: #DCE6F1"]

Q​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD][/TD]
[TD]

Criteria​

[/TD]
[TD]

Results​

[/TD]
[TD]

Count​

[/TD]
[TD]

Header1​

[/TD]
[TD]

Header2​

[/TD]
[TD]

Header3​

[/TD]
[TD]

Header4​

[/TD]
[TD]

Header5​

[/TD]
[TD]

Header6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

P​

[/TD]
[TD]

2​

[/TD]
[TD]

0​

[/TD]
[TD]

2​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

P​

[/TD]
[TD]

Q​

[/TD]
[TD]

1​

[/TD]
[TD]

C​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Q​

[/TD]
[TD]

0​

[/TD]
[TD]

3​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

R​

[/TD]
[TD]

1​

[/TD]
[TD]

A​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

R​

[/TD]
[TD]

6​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

T​

[/TD]
[TD]

2​

[/TD]
[TD]

C​

[/TD]
[TD]

F​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

S​

[/TD]
[TD]

0​

[/TD]
[TD]

5​

[/TD]
[TD]

0​

[/TD]
[TD]

3​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

U​

[/TD]
[TD]

1​

[/TD]
[TD]

C​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

T​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

5​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

U​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

7​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Criteria in I2

Array formula in J2 copied down till J6
=IFERROR(INDEX($A$2:$A$7,SMALL(IF(MMULT((INDEX($B$2:$G$7,MATCH($I$2,$A$2:$A$7,0),0)>0)*($B$2:$G$7>0)*($A$2:$A$7<>$I$2),{1;1;1;1;1;1})>0,ROW(A$1:A$6)-ROW(A$1)+1),ROWS(J$2:J2))),"")
Ctrl+Shift+Enter

Regular formula in K2 copied down
=IF(J2="","",COUNTIFS(INDEX($B$2:$G$7,MATCH($I$2,$A$2:$A$7,0),0),">0",INDEX($B$2:$G$7,MATCH(J2,$A$2:$A$7,0),0),">0"))

Array formula in L2 copied across till Q2 and down
=IFERROR(INDEX($B$1:$G$1,SMALL(IF(INDEX($B$2:$G$7,MATCH($I$2,$A$2:$A$7,0),0)>0,IF(INDEX($B$2:$G$7,MATCH($J2,$A$2:$A$7,0),0)>0,COLUMN($B$1:$G$1)-COLUMN($B$1)+1)),COLUMNS($L2:L2))),"")
Ctrl+Shift+Enter

Hope this helps

M.