Zero chemist sale in dec

[Muhammad Uzair Uddin]

Need your help for below data from raw data

CHEMIST NAME	BrickReferenceName	ZERO SALES IN DEC

Raw data

Zone	Region	RepName	BrickCode	BrickReferenceName	CustomerCode	Chemist Name	ProductName	Month	SaleValue
S	KHI	amin	Q02	ADA BASIR ABAD	30378066	SHAHID MEDICAL STORE	abc	DEC	99.95​
S	KHI	amin	10	MITHADAR/KHARADAR/W.	70112019	JILANI MEDICAL STORE	abc	DEC	299.85​
S	KHI	amin	10	MITHADAR/KHARADAR/W.	30810427	NAGORIS MEDICOS	abc	DEC	299.85​
S	KHI	amin	10	MITHADAR/KHARADAR/W.	30810427	NAGORIS MEDICOS	abc	DEC	199.9​
S	KHI	amin	10	MITHADAR/KHARADAR/W.	30810894	PAK MEDICAL STORE	abc	DEC	499.75​
S	KHI	amin	101	NAWABAD/MEMON SOCIET	30925118	BILAL MEDICAL STORE	abc	DEC	599.7​
S	KHI	amin	101	NAWABAD/MEMON SOCIET	7375000	DR RANA LOHANA CLINIC	abc	DEC	399.8​
S	KHI	amin	101	NAWABAD/MEMON SOCIET	30810733	GHOSIA MEDICAL STORE	abc	DEC	199.9​
S	KHI	amin	101	NAWABAD/MEMON SOCIET	30810711	LUCKY MEDICAL STORE	abc	DEC	299.85​
S	KHI	amin	101	NAWABAD/MEMON SOCIET	30810711	SHAHID MEDICAL STORE	abc	NOV	299.85​
S	KHI	amin	101	NAWABAD/MEMON SOCIET	30810711	LUCKY MEDICAL STORE	abc	NOV	199.9​
S	KHI	amin	101	NAWABAD/MEMON SOCIET	30810721	NEW IMRAN MEDICAL STORE	abc	NOV	199.9​
S	KHI	amin	10	MITHADAR/KHARADAR/W.	70112019	JILANI MEDICAL STORE	abc	NOV	0​
S	KHI	amin	101	NAWABAD/MEMON SOCIET	30810721	NEW IMRAN MEDICAL STORE	abc	NOV	399.8​
S	KHI	amin	101	NAWABAD/MEMON SOCIET	3091210	RAHEEL MEDICAL STORE	abc	NOV	299.85​
S	KHI	amin	Q02	ADA BASIR ABAD	30378066	SHAHID MEDICAL STORE	abc	NOV	0​

 

[alansidman]

How about a explanation in addition to the data. We are not mind readers and need a little bit of help. What are you expecting the results to look like? What is your criteria?

 

[Muhammad Uzair Uddin]

thaks for reply

we need data in below format

Chemist name	Brick ref name	area
?	?	?

Raw data previosly send.

 

[Toadstool]

Hi Muhammad Uzair Uddin,

You have not made it clear what you want so here's my guess.

If you update your profile with your Excel version you may receive a better solution. I first did this with a single formula but the execution time was much too slow so I've added a Worker column. You don't say how many rows of Raw Data you have so I've assumed up to 9,999 and you would need to copy down the Worker formula in D2 down 9,998 rows.

Here is your Raw Data sheet.

Muhammad Uzair Uddin3.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Zone	Region	RepName	BrickCode	BrickReferenceName	CustomerCode	Chemist Name	ProductName	Month	SaleValue
2	S	KHI	amin	Q02	ADA BASIR ABAD	30378066	SHAHID MEDICAL STORE	abc	DEC	99.95
3	S	KHI	amin	10	MITHADAR/KHARADAR/W.	70112019	JILANI MEDICAL STORE	abc	DEC	299.85
4	S	KHI	amin	10	MITHADAR/KHARADAR/W.	30810427	NAGORIS MEDICOS	abc	DEC	299.85
5	S	KHI	amin	10	MITHADAR/KHARADAR/W.	30810427	NAGORIS MEDICOS	abc	DEC	199.9
6	S	KHI	amin	10	MITHADAR/KHARADAR/W.	30810894	PAK MEDICAL STORE	abc	DEC	499.75
7	S	KHI	amin	101	NAWABAD/MEMON SOCIET	30925118	BILAL MEDICAL STORE	abc	DEC	599.7
8	S	KHI	amin	101	NAWABAD/MEMON SOCIET	7375000	DR RANA LOHANA CLINIC	abc	DEC	399.8
9	S	KHI	amin	101	NAWABAD/MEMON SOCIET	30810733	GHOSIA MEDICAL STORE	abc	DEC	199.9
10	S	KHI	amin	101	NAWABAD/MEMON SOCIET	30810711	LUCKY MEDICAL STORE	abc	DEC	299.85
11	S	KHI	amin	101	NAWABAD/MEMON SOCIET	30810711	SHAHID MEDICAL STORE	abc	NOV	299.85
12	S	KHI	amin	101	NAWABAD/MEMON SOCIET	30810711	LUCKY MEDICAL STORE	abc	NOV	199.9
13	S	KHI	amin	101	NAWABAD/MEMON SOCIET	30810721	NEW IMRAN MEDICAL STORE	abc	NOV	199.9
14	S	KHI	amin	10	MITHADAR/KHARADAR/W.	70112019	JILANI MEDICAL STORE	abc	NOV	0
15	S	KHI	amin	101	NAWABAD/MEMON SOCIET	30810721	NEW IMRAN MEDICAL STORE	abc	NOV	399.8
16	S	KHI	amin	101	NAWABAD/MEMON SOCIET	3091210	RAHEEL MEDICAL STORE	abc	NOV	299.85
17	S	KHI	amin	Q02	ADA BASIR ABAD	30378066	SHAHID MEDICAL STORE	abc	NOV	0
18
Raw Data

Here is the formulae to list the Chemist and Brick Ref Name for sales in any other month but DEC which has no sales in Dec.

Muhammad Uzair Uddin3.xlsx
	A	B	C	D
1	CHEMIST NAME	BrickReferenceName	ZERO SALES IN DEC	Worker
2	NEW IMRAN MEDICAL STORE	NAWABAD/MEMON SOCIET
3	RAHEEL MEDICAL STORE	NAWABAD/MEMON SOCIET
4
5
6
7
8
9
10
11
12
13				13
14
15
16				16
Below Data

Cell Formulas
Range	Formula
A2:A16	A2	=IFERROR(INDEX('Raw Data'!$G$2:$G$9999,AGGREGATE(15,6,ROW($D$2:$D$9999)-ROW('Raw Data'!$G$1)/(($D$2:$D$9999>0)*($D$2:$D$9999<>"")),ROW()-ROW($A$1))),"")
B2:B16	B2	=IFERROR(INDEX('Raw Data'!$E$2:$E$9999,AGGREGATE(15,6,ROW($D$2:$D$9999)-ROW('Raw Data'!$G$1)/(($D$2:$D$9999>0)*($D$2:$D$9999<>"")),ROW()-ROW($A$1))),"")
D2:D16	D2	=IF(OR('Raw Data'!G2="",'Raw Data'!I2=RIGHT($C$1,3)),"",IF(COUNTIF('Raw Data'!$G$1:$G2,'Raw Data'!G2)>1,"",ROW()*(ISNA((MATCH(1,INDEX(('Raw Data'!$G$2:$G$9999='Raw Data'!G2)*('Raw Data'!$I$2:$I$9999=RIGHT($C$1,3)),0,1),0))))))