CODEWITHSUNDEEP
excelif-statementexcel-formulamatch
Adriana TIMIS
Adriana TIMIS

Reputation: 35

Represent a list in matrix format

I have an Excel file with these columns and values:

sheet11

enter image description here <-To be:

I can easily insert a PivotTable and make the array but I need a formula that will add an x if a is present in 1, 3, 4, 5 and the same for b: if is present in 1 add an x, if is present in 5 add an x etc.

Is it something that can be done using an INDEX/MATCH or should a VB script be used instead?

Upvotes: 1

Views: 80

Answers (2)

pnuts
pnuts

Reputation: 59485

An alternative to COUNTIFS: 

=IF(SUMPRODUCT((B$1=Sheet1!$A$2:$A$10)*($A2=Sheet1!$B$2:$B$10)),"x","")

Upvotes: 0

Excel Hero
Excel Hero

Reputation: 14764

Based on the layout from your images, you can enter this formula in cell B2 of the report sheet:

=REPT("x",0<COUNTIFS(Sheet1!$A:$A,B$1,Sheet1!$B:$B,$A2))

...and then copy over and down as far as needed.

Note: this assumes that the source list is on Sheet1.

enter image description here

Upvotes: 4

Related Questions