Sales Data - Did Customer Exchange Up or Down?

[GrampsOnGrass]

Hi Gurus,

I've been requested to run a report for work, that looks at exchanges of apparel for our online store, and we're wanting to look at if the customer is exchanging up a size, or down a size.

I have the following data, and have applied a numerical value to a size (XL = 6, 2XL = 7), but wanting to subtract one value from the other within the pivot; and then at some point, be able to filter on a particular product, and find that "X amount of exchanges were for going down a size" and "X amount of exchanges were for going up a size"

(I've copied/pasted a part of the Pivot into another sheet, and changed some of the sensitive information)

https://imgur.com/oZYioSj

For the first Order, we can see that the customer exchanged an 1x XL Navy/White Check Mens Tee for 1x 2XL Navy/White Check Mens Tee

In the second Order, we can see that the customer exchanged 1x Large Forest Green Womens Tee for 1x 2XL Forest Green Womens Tee, and 1x Large Navy Womens Tee for 1x 2XL Forest Womens Tee.

Ideally, it would look something like this:

https://imgur.com/JrabDbq

Thanks in advance

 

[Skybluekid]

What about if the customer is having the same size but different colour?

 

[Skybluekid]

On your original Data you could try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	SO Cust Order Number	SO Original	SO Order Date	SO Trans Type	Prod Code	Prod Name	Cir Code	Cir Name	SKU Size	SKU Size Seq	Exchange Up/Down	Value	Key
2	ORDER0000879123	123671623	02/01/2022	Credit Note	1234	Mens Tee	NAVWHT	Navy/white Check	XL	6		-1	ORDER00008791231236716231234NAVWHT
3	ORDER0000879123	123671623	02/01/2022	Invoice	1234	Mens Tee	NAVWHT	Navy/white Check	2XL	7	Up	1
4	ORDER0000079456	123687356	04/02/2022	Credit Note	5678	Womens Tee	FOR	Forest Green	L	5		-1	ORDER00000794561236873565678FOR
5	ORDER0000079456	123687356	04/02/2022	Credit Note	5678	Womens Tee	NAV	Navy	L	5		-1	ORDER00000794561236873565678NAV
6	ORDER0000079456	123687356	04/02/2022	Invoice	5678	Womens Tee	FOR	Forest Green	2XL	7	Up	1
7	ORDER0000079456	123687356	04/02/2022	Invoice	5678	Womens Tee	NAV	Navy	2XL	7	Up	1
8	ORDER0000879124	123892330	05/02/2022	Credit Note	9023	Child Tee	FOR	Forest Green	M	4		-1	ORDER00008791241238923309023FOR
9	ORDER0000879124	123892330	05/02/2022	Invoice	9023	Child Tee	FOR	Forest Green	S	3	Down	1
10	ORDER0000910221	123980010	06/02/2022	Credit Note	9023	Child Tee	FOR	Forest Green	M	4		-1	ORDER00009102211239800109023FOR
11	ORDER0000910221	123980010	06/02/2022	Invoice	9023	Child Tee	NAV	Navy	M	4	No Change	1
Sheet1

Cell Formulas
Range	Formula
K2:K11	K2	=IF([@[SO Trans Type]]="Invoice",IF(J2>INDEX([SKU Size Seq],MATCH(A1&B1&E1&G1,[SO Cust Order Number]&[SO Original]&[Prod Code]&[Cir Code],0)),"Up",IF(J2<INDEX([SKU Size Seq],MATCH(A1&B1&E1&G1,[SO Cust Order Number]&[SO Original]&[Prod Code]&[Cir Code],0)),"Down","No Change")),"")
M2:M11	M2	=IF([@[SO Trans Type]]="Credit Note",CONCAT([@[SO Cust Order Number]],[@[SO Original]],[@[Prod Code]],[@[Cir Code]]),"")

I have created a helper column for comparision. This could be hidden