Problem with sales, multiple values, postcodes, etc.

[Tashbbb]

Hi guys,


I came across this problem today and I'm not sure how to solve it..I'm sure this is easy for you but I'm a beginner so this is really stressing me out ((

I have two tabs which include:

1: Sales dates, postcodes and number of sales

2: postcodes, store number, store names

Each postcodes is associated with a certain store number and store numbers is associated with a store name.
Now....

1)How do I get total number of sales per store (name)?

2)How do I calculate which two months had the highest volume of sales taking only into consideration stores that had sales from September onwards? (and excluding stores which are not associate with a postcode)?

3) How do I figure out which store had the fastest growth?

Number 1 and 2 are giving me more headaches...I tried to look online but I cannot find a solution

I really appreciate your help !!!!

 

[alansidman]

crosspost: Problem with postcodes and sales for multiple locations

 

[Fluff]

@Tashbbb
While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[CA_Punit]

So it will troublesome pasting all the data in excel
So if you can use the XL2BB addin which is available in the Right hand side of Reply Box to provide us the data

 

[CA_Punit]

Is the Postcode prefix in Sheet 1 and Post code in sheet 2 same???

 

[Tashbbb]

Hi

@CA_Punit

Here it is (sorry took me ages to figure out XL2BB. ?)
- Postcodes are the same in both tabs

problem_forum.xlsx
	A	B	C	D
1	Data Month Sale	Post code Prefix	Confirmed Sales
2	2019-05	N7	1
3	2019-05	SE12	1
4	2019-05	BR6	1
5	2019-05	SW16	1
6	2019-05	BA1	1
7	2019-05	SN1	1
8	2019-05	M16	1
9	2019-05	E1	1
10	2019-05	NW6	1
11	2019-05	W9	1
12	2019-05	SW6	1
13	2019-05	W12	1
14	2019-05	KT14	2
15	2019-05	KT12	3
16	2019-05	N9	1
17	2019-05	E14	2
18	2019-05	CR3	1
19	2019-05	SG12	1
20	2019-05	B36	1
21	2019-05	N13	1
22	2019-05	N19	1
23	2019-05	OX2	1
24	2019-05	SE1	1
25	2019-05	W8	2
26	2019-05	RM4	1
27	2019-05	WD25	1
28	2019-05	SE3	1
29	2019-05	TN15	1
30	2019-05	G42	1
31	2019-05	M3	1
32	2019-05	BS24	1
Data set

problem_forum.xlsx
	A	B	C	D	E
1	Postcodes	Store number		Store number	Store name
2	BT1	113		113	Store Belfast
3	BT10	113		125	Store Southampton
4	BT11	113		140	Store Warrington
5	BT12	113		141	Store London - Wembley
6	BT13	113		142	Store Birmingham - Wednesbury
7	BT14	113		143	Store Newcastle - Gateshead
8	BT15	113		144	Store London - Croydon
9	BT16	113		150	Store Coventry
10	BT17	113		185	Store Milton Keynes
11	BT18	113		186	Store Manchester
12	BT19	113		255	Store London - Tottenham
13	BT2	113		261	Store Leeds
14	BT20	113		262	Store Lakeside
15	BT21	113		263	Store Nottingham
16	BT22	113		264	Store Bristol
17	BT23	113		265	Store Edinburgh - Straiton
18	BT24	113		266	Store Glasgow - Braehead
19	BT25	113		267	Store Cardiff
20	BT26	113		461	Store Reading
21	BT27	113		472	Store eCommerce Great Britain
22	BT28	113		519	Store Sheffield
23	BT29	113		548	Store Exeter
24	BT3	113		567	Store Greenwich
25	BT30	113
26	BT36	113
27	BT37	113
28	BT38	113
29	BT39	113
30	BT4	113
31	BT40	113
32	BT41	113
Lookup

 

[CA_Punit]

Queries

1. Can a post code be associated with different Stores Number.
2. None of the Postcode prefix matches with post code data?

 

[Tashbbb]

Queries

1. Can a post code be associated with different Stores Number.
2. None of the Postcode prefix matches with post code data?

Hi Ca-Unit,
1. I believe each postcode is associated with a specific store
2 In the picture I've only included the top values per tab (there are more then 1000 value)

 

[CA_Punit]

Consider This

Balance Quantity.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Postcodes	Store number		Store number	Store name	Store Sales								Data Month Sale	Post code Prefix	Confirmed Sales	Store Id	Required Sales	month		month	Sum of Required Sales
2	BT1	113		113	Store Belfast	2								01/09/2019	N7	1	265	1	9		8	0
3	BT10	113		125	Store Southampton	0								31/08/2019	SE12	1	266	0	8		9	5
4	BT11	113		140	Store Warrington	0								20/09/2019	BR6	1	277	1	9		10	0
5	BT12	113		141	Store London - Wembley	0								21/09/2019	SW16	1	567	1	9		Grand Total	5
6	BT13	113		142	Store Birmingham - Wednesbury	0								22/09/2019	BA1	1	113	1	9
7	BT14	113		143	Store Newcastle - Gateshead	0								23/09/2019	SN1	1	113	1	9
8	BT15	113		144	Store London - Croydon	0								24/09/2019	M16	1	#N/A	0	9
9	BT16	113		150	Store Coventry	0								25/09/2019	E1	1	#N/A	0	9
10	BT17	113		185	Store Milton Keynes	0								26/09/2019	NW6	1	#N/A	0	9
11	BT18	113		186	Store Manchester	0								27/09/2019	W9	1	#N/A	0	9
12	BT19	113		255	Store London - Tottenham	0								28/09/2019	SW6	1	#N/A	0	9
13	BT2	113		261	Store Leeds	0								29/09/2019	W12	1	#N/A	0	9
14	BT20	113		262	Store Lakeside	0								30/09/2019	KT14	2	#N/A	0	9
15	N7	265		263	Store Nottingham	0								01/10/2019	KT12	3	#N/A	0	10
16	SE12	266		264	Store Bristol	0								02/10/2019	N9	1	#N/A	0	10
17	BR6	277		265	Store Edinburgh - Straiton	1								03/10/2019	E14	2	#N/A	0	10
18	SW16	567		266	Store Glasgow - Braehead	1								04/10/2019	CR3	1	#N/A	0	10
19	BA1	113		267	Store Cardiff	0								05/10/2019	SG12	1	#N/A	0	10
20	SN1	113		461	Store Reading	0								06/10/2019	B36	1	#N/A	0	10
21	BT27	113		472	Store eCommerce Great Britain	0								07/10/2019	N13	1	#N/A	0	10
22	BT28	113		519	Store Sheffield	0								08/10/2019	N19	1	#N/A	0	10
23	BT29	113		548	Store Exeter	0								09/10/2019	OX2	1	#N/A	0	10
24	BT3	113		567	Store Greenwich	1								10/10/2019	SE1	1	#N/A	0	10
25														11/10/2019	W8	2	#N/A	0	10
26														12/10/2019	RM4	1	#N/A	0	10
27														13/10/2019	WD25	1	#N/A	0	10
28														14/10/2019	SE3	1	#N/A	0	10
29														15/10/2019	TN15	1	#N/A	0	10
30														16/10/2019	G42	1	#N/A	0	10
31														17/10/2019	M3	1	#N/A	0	10
32														18/10/2019	BS24	1	#N/A	0	10
Sheet6

Cell Formulas
Range	Formula
R2:R32	R2	=IFERROR((N2:N32>DATE(2019,8,31))*IF(MATCH(O2:O32,A2:A24,0),P2:P32),0)
F2:F24	F2	=SUMIF($Q$2:$Q$32,D2,$P$2:$P$32)
N5:N32	N5	=N4+1
Q2:Q32	Q2	=VLOOKUP(O2,A:B,2,0)
S2:S32	S2	=MONTH(N2)
Dynamic array formulas.

 

[Fluff]

@CA_Punit
The OP is using 2019 & therefore does not have dynamic arrays.