Possibly using pivot table to sort this matrix?

[namy77]

Good morning,

I have a matrix of hundreds of columns and rows, thow:

Column A: list of our phones
Row 1: List of microprocessors that are compatible with these phones

If there is an "X" where the row and column intersects, that means that this phone and that microprocessor are compatible.

For the mock-up sheet, Phone 1 is compatible with Microprocessor C, Phone 2 with A,C,and Z, and so on an so forth.

While this is graphically easy for people to look and learn about compatibility, but since we have hundreds of rows and columns, is there anyway we can:

a. Filter and generate a list of which phones are compatible with any given microprocessor?
b. Filter and generate a list of which microprocessors are compatible with any phone?

The user will come in, and query, let's say "Micro C", then Phone 1,2 and 1000 will show up

or "Phone 1000", and a list of Micro C and Z will be populated.

Would a pivot table help with this case?

Thanks!

[xlnitwit]

Hi,

It would be much simpler if you had just two columns in the data. You could then create a simple pivot table with slicers for each field.

[63falcondude]

With your matrix in A1:D5, and "Phone 2" in F2, try these:

G2 =IFERROR(INDEX(B$1:D$1,SMALL(IF((A$2:A$5=F$2)*(B$2:D$5="X"),COLUMN(B$1:D$1)-(COLUMN(B$1)-1)),ROWS(A$1:A1))),"") Ctrl Shift Enter

With "Micro C" in I2:

J2 =IFERROR(INDEX(A$2:A$5,SMALL(IF((B$1:D$1=I$2)*(B$2:D$5="X"),ROW(A$2:A$5)-(ROW(A$2)-1)),ROWS(A$1:A1))),"") Ctrl Shift Enter

Drag both formulas down as far as needed. Adjust the ranges to match your data.

See attachment for clarification.

[José Augusto]

Hi
You can use anoter approach.

Use a list of Micros (from F11 to F...) and use in G11 the following formula

Please Login or Register  to view this content.

Filter the table with 1 in column G

See the file (@63falcondude base)