Transpose data based on two columns and criteria: Excel

Question

I have data that looks like this:

Group: Class:   Value:
A          1        51
A          2        60
B          1        55
B          2        67
B          3        70
C          1        53
C          3        65

Need the data to look like this:

Group:             1:           2:            3: 
    A             51            60             0
    B             55            67            70
    C             53             0            65

The code I am trying is doing two things wrong: 1. Skipping rows 2. not matching value to class column which causes an issue for Group C since it puts the 65 value into class 2 not and not in class 3 for the final row (row 3 in this example).
=IFERROR(IF(AND($B2=1,COLUMN()<3+MATCH($B2,$B3:$B11000,0)),OFFSET(B2,COLUMN()-3,2-COLUMN()),""),"")

Dang D. Khanh · Accepted Answer · 2020-01-13 07:27:02Z

1

This has worked for me.Hope it helps!

=SUMPRODUCT(($A$2:$A$8=$E2)*($B$2:$B$8=COLUMN(A:A))*$C$2:$C$8)

If the C column format is text, let's try:

=IFERROR(OFFSET($C$2,MATCH(1,INDEX(($A$2:$A$8=$E2)*($B$2:$B$8=COLUMN(A$1)),),0)-1,),"")

edited Jan 13, 2020 at 7:27

answered Jan 12, 2020 at 21:13

Dang D. Khanh

1,4618 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

basic · Accepted Answer · 2020-01-12 22:12:13Z

0

I think it's more efficient to use PivotTable for such tasks:

answered Jan 12, 2020 at 22:12

basic

12k2 gold badges15 silver badges29 bronze badges

Collectives™ on Stack Overflow

Transpose data based on two columns and criteria: Excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related