CODEWITHSUNDEEP
excelexcel-formula
D41V30N
D41V30N

Reputation: 191

Need Help Transposing Different Values of the Same Criteria [Difficult to Describe, Example of What I Need Inside]

So let's imagine a table where the same column value appears multiple times, but every time it happens, a value in a different column on each of these rows is different:

Item QTY Box Number
Mango 50 1
Apple 100 1
Orange 60 2
Mango 50 6
Watermelon 10 3
Mango 100 9
Orange 150 10

I want to create another table in another sheet that references this table whereby I want an "Item" column, a "Total QTY" column (using SUMIF to calculate the total number of items pertaining to the fruit on the row), and then for the rest of the columns in the row:

Item TotalQTY - - -
Apple 100 1 - -
Mango 200 1 6 8
Orange 210 2 10 -
Watermelon 10 3 - -

Upvotes: 1

Views: 95

Answers (1)

Harun24hr
Harun24hr

Reputation: 36870

In my Excel365 I have used below formulas as per below screenshot.

E2=SORT(UNIQUE(A2:A8))
F2=SUMIFS(B2:B8,A2:A8,E2#)
G2=TRANSPOSE(FILTER($C$2:$C$8,$A$2:$A$8=E2))

enter image description here

If you are not using O365 then could try below. F2 cell fornula.

=SUMIFS($B$2:$B$8,$A$2:$A$8,E2)

Then drag down. G2 cell formula.

=IFERROR(INDEX($C$2:$C$8,AGGREGATE(15,6,ROW($1:$7)/($A$2:$A$8=$E2),COLUMNS($A$1:A$1))),"")

Then drag down and across till need.

Upvotes: 2

Related Questions