Help In Reporting

[Haree]

Hello All,
I require a small help in reporting my data would be segregated and arranged into monthly basis based on SKU. I have attached Xl2BB below for further understanding of the exact model, My requirements would be if i click SKU 2 from Dropdown box in the reporting worksheet it should show me that particular SKU's data for all the months , and colour grade it based on the total sales. For Example if i have sold 100 pieces in total for the year and i have sold 50 pieces in the month of june alone, june should be full green and the lowest month should be in red. The same way if i click All it should Show data for all the SKU. Hope i was clear. In other words i nedd to know which SKU moved fast overall and In which month one particular SKU moved fast. Kindly help me out. Thanks in advance

reporting trial.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW
1	Particulars	April				May				June				July				August				September				October				November				December				January				February				March
2	ITEM NAME	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W
3	SKU 1
4	SKU 2
5	SKU 3
6	SKU 4
7	SKU 5
8	SKU 6
9	SKU 7
10	SKU 8
11	SKU 9
12	SKU 10
13	SKU 11
14	SKU 12
15	SKU 13
16	SKU 14
17	SKU 15
18	SKU 16
19	SKU 17
20	SKU 18
21	SKU 19
22	SKU 20
23
24
25		Purchase P	Purchase Pieces
26		Purchase W	Purchase Weight
27		Sales P	Sales Pieces
28		Sales W	Sales Weight
Sheet1

 

[Toadstool]

Hi Haree,

So the sheet shown is the data sheet?
You want a similar looking Reporting sheet where you can select one or all SKU for pulling into the Reporting sheet?
Can I assume that if multiple months have the same lowest or highest "Sales P" then they should all be highlighted?

 

[Haree]

Hi Haree,

So the sheet shown is the data sheet?
You want a similar looking Reporting sheet where you can select one or all SKU for pulling into the Reporting sheet?
Can I assume that if multiple months have the same lowest or highest "Sales P" then they should all be highlighted?

Hi Toadstol, Thank you for taking out time to help me. Yes i would require a separate sheet and yes they all should be highlighted. and the highest and lowest should be based on pieces only.

 

[Toadstool]

Here's my solution as sheet Query. It requires the sheet with the data be called Data and I used your format but generated some test data.

You can select a SKU in the B1 dropdown or leave empty to list all SKU.

Haree.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX
1	SKU:		(Leave empty for all)
2	Particulars		April				May				June				July				August				September				October				November				December				January				February				March
3	ITEM NAME	Total Sales P	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W	Purchase P	Purchase W	Sales P	Sales W
4	SKU 1	652	217	6	26	0.72	68	8	65	7.65	203	6	10	0.3	16	6	4	1.5	125	9	89	6.41	76	5	21	1.38	113	12	94	9.98	93	20	29	6.24	128	20	95	14.84	41	12	73	21.37	200	22	82	9.02	41	4	64	6.24
5	SKU 2	751	40	15	89	33.38	148	11	33	2.45	136	13	86	8.22	96	8	59	4.92	196	8	73	2.98	58	19	71	23.26	107	15	18	2.52	118	11	44	4.1	112	5	57	2.54	200	19	48	4.56	125	17	94	12.78	213	21	79	7.79
6	SKU 3	691	75	4	45	2.4	53	9	85	14.43	87	17	87	17	51	3	59	3.47	2	12	41	246	99	15	60	9.09	175	16	7	0.64	52	15	61	17.6	143	20	80	11.19	46	2	93	4.04	10	8	13	10.4	45	20	60	26.67
7	SKU 4	594	97	12	91	11.26	109	8	8	0.59	142	6	40	1.69	137	19	53	7.35	107	16	38	5.68	20	1	97	4.85	94	15	56	8.94	29	20	87	60	168	12	9	0.64	87	12	47	6.48	172	8	38	1.77	100	4	30	1.2
Query

Cell Formulas
Range	Formula
C2,AU2,AQ2,AM2,AI2,AE2,AA2,W2,S2,O2,K2,G2	C2	=Data!B1
A4:A7	A4	=IFERROR(IF($B$1="",INDEX(Data!$A$3:$A$99,AGGREGATE(15,6,ROW(Data!$A$3:$A$99)-ROW(Data!$A$2)/(Data!$A$3:$A$99<>""),ROW()-ROW($A$3))),INDEX(Data!$A$3:$A$99,AGGREGATE(15,6,ROW(Data!$A$3:$A$99)-ROW(Data!$A$2)/(Data!$A$3:$A$99=$B$1),ROW()-ROW($A$3)))),"")
B4:B7	B4	=IF(SUMIFS($C4:$AX4,C$3:AX$3,"Sales P")=0,"",SUMIFS($C4:$AX4,C$3:AX$3,"Sales P"))
C4:AX7	C4	=IFERROR(INDEX(Data!$B$3:$AW$99,MATCH($A4,Data!$A$3:$A$99,0),COLUMN(Data!A$2)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:AX104	Expression	=AND(C$3="sales P",C4=MAX($E4,$I4,$M4,$Q4,$U4,$Y4,$AC4,$AG4,$AK4,$AO4,$AS4,$AW4))	text	NO
C4:AX104	Expression	=AND(C$3="sales P",C4=MIN($E4,$I4,$M4,$Q4,$U4,$Y4,$AC4,$AG4,$AK4,$AO4,$AS4,$AW4))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=OFFSET(Data!$A$3,,,COUNTA(Data!$A$3:$A$9999))

 

[Haree]

Hi Toadstool thank you so much for helping me out. It worked so well. Thank you so much