Organising Data - Is pivot Table best option?

[Sineadd]

Hi

I have the below data that I have for over 20 stores and want to have tables set up so that once the main data is updated all the relevant table will just update accordingly. I was trying to do this by using Pivot table but I cannot get the data to display the way I have been requested to lay it out


Below is the Data the way it currently is

		Store 1	Store 2
Keyword	Search Volume	CURRENT	DIFFERENCE	CURRENT	DIFFERENCE	Tags
Keyword 1	1000​	16​	-4​	63​	-14​	Tag 1
Keyword 2	900​	14​	0​	0​	0​	Tag 2
Keyword 3	900​	4​	0​	17​	0​	Tag 4
Keyword 4	800​	16​	1​	48​	-4​	Tag 3
Keyword 5	700​	5​	0​	11​	-1​	Tag 1
Keyword 6	700​	29​	-7​	0​	0​	Tag 2
Keyword 7	700​	4​	0​	37​	-5​	Tag 6
Keyword 8	700​	3​	1​	69​	-6​	Tag 1
Keyword 9	600​	27​	-2​	11​	3​	Tag 7
Keyword 10	600​	2​	1​	19​	35​	Tag 8
Keyword 11	600​	4​	0​	15​	4​	Tag 1
Keyword 12	600​	29​	-5​	0​	0​	Tag 3
Keyword 13	600​	1​	3​	60​	2​	Tag 4
Keyword 14	600​	1​	0​	15​	2​	Tag 1
Keyword 15	600​	41​	59​	0​	0​	Tag 5
Keyword 16	600​	3​	-1​	26​	-1​	Tag 9
Keyword 17	600​	1​	0​	21​	3​	Tag 1
Keyword 18	600​	17​	-1​	14​	0​	Tag 1
Keyword 19	500​	7​	-1​	2​	-1​	Tag 5
Keyword 20	500​	16​	-1​	17​	2​	Tag 1
Keyword 21	500​	8​	0​	3​	0​	Tag 1
Keyword 22	500​	9​	-3​	1​	1​	Tag 1
Keyword 23	500​	1​	1​	13​	7​	Tag 3
Keyword 24	500​	17​	0​	23​	0​	Tag 3
Keyword 25	500​	7​	0​	66​	-1​	Tag 1
Keyword 26	400​	2​	-1​	6​	0​	Tag 6
Keyword 27	400​	4​	2​	8​	-1​	Tag 6
Keyword 28	400​	7​	-3​	11​	-2​	Tag 1
Keyword 29	400​	5​	1​	44​	-3​	Tag 1
Keyword 30	400​	9​	1​	42​	-2​	Tag 7
Keyword 31	400​	2​	0​	5​	0​	Tag 1
Keyword 32	400​	10​	2​	15​	6​	Tag 10
Keyword 33	400​	10​	0​	53​	-3​	Tag 1
Keyword 34	400​	10​	-1​	73​	-2​	Tag 1
Keyword 35	400​	8​	-1​	0​	0​	Tag 1

And Below is how we want it to dispaly - Basically there will be a tab for eacg Tag ( there could be 50 of these) and I want each tags results filtered onto each tab like below

		Store 1	Store 2
Keyword	Search Volume	CURRENT	DIFFERENCE	CURRENT	DIFFERENCE	Tags
Keyword 1	1000​	16​	-4​	63​	-14​	Tag 1
Keyword 5	700​	5​	0​	11​	-1​	Tag 1
Keyword 8	700​	3​	1​	69​	-6​	Tag 1
Keyword 11	600​	4​	0​	15​	4​	Tag 1
Keyword 14	600​	1​	0​	15​	2​	Tag 1
Keyword 17	600​	1​	0​	21​	3​	Tag 1
Keyword 18	600​	17​	-1​	14​	0​	Tag 1
Keyword 20	500​	16​	-1​	17​	2​	Tag 1
Keyword 21	500​	8​	0​	3​	0​	Tag 1
Keyword 22	500​	9​	-3​	1​	1​	Tag 1
Keyword 25	500​	7​	0​	66​	-1​	Tag 1
Keyword 28	400​	7​	-3​	11​	-2​	Tag 1
Keyword 29	400​	5​	1​	44​	-3​	Tag 1
Keyword 31	400​	2​	0​	5​	0​	Tag 1
Keyword 33	400​	10​	0​	53​	-3​	Tag 1
Keyword 34	400​	10​	-1​	73​	-2​	Tag 1
Keyword 35	400​	8​	-1​	0​	0​	Tag 1

Appreciate any direction in the right path

 

[Sineadd]

Any one able to help

 

[keef2]

I would probably use the filter formula. Basically type

=FILTER(Select entire range, tag column="tag X") For each tab you can change the X to whatever tag # you want. Hope this helps.

Book1
	A	B	C	D	E	F	G
1		Store 1	Store 2
2	Keyword	Search Volume	CURRENT	DIFFERENCE	CURRENT	DIFFERENCE	Tags
3	Keyword 1	1000	16	-4	63	-14	Tag 1
4	Keyword 5	700	5	0	11	-1	Tag 1
5	Keyword 8	700	3	1	69	-6	Tag 1
6	Keyword 11	600	4	0	15	4	Tag 1
7	Keyword 14	600	1	0	15	2	Tag 1
8	Keyword 17	600	1	0	21	3	Tag 1
9	Keyword 18	600	17	-1	14	0	Tag 1
10	Keyword 20	500	16	-1	17	2	Tag 1
11	Keyword 21	500	8	0	3	0	Tag 1
12	Keyword 22	500	9	-3	1	1	Tag 1
13	Keyword 25	500	7	0	66	-1	Tag 1
14	Keyword 28	400	7	-3	11	-2	Tag 1
15	Keyword 29	400	5	1	44	-3	Tag 1
16	Keyword 31	400	2	0	5	0	Tag 1
17	Keyword 33	400	10	0	53	-3	Tag 1
18	Keyword 34	400	10	-1	73	-2	Tag 1
19	Keyword 35	400	8	-1	0	0	Tag 1
Tag 1

Cell Formulas
Range	Formula
A3:G19	A3	=FILTER(Sheet1!A3:G37,Sheet1!G3:G37="Tag 1")
Dynamic array formulas.

 

[Sineadd]

I would probably use the filter formula. Basically type

=FILTER(Select entire range, tag column="tag X") For each tab you can change the X to whatever tag # you want. Hope this helps.

Book1
	A	B	C	D	E	F	G
1		Store 1	Store 2
2	Keyword	Search Volume	CURRENT	DIFFERENCE	CURRENT	DIFFERENCE	Tags
3	Keyword 1	1000	16	-4	63	-14	Tag 1
4	Keyword 5	700	5	0	11	-1	Tag 1
5	Keyword 8	700	3	1	69	-6	Tag 1
6	Keyword 11	600	4	0	15	4	Tag 1
7	Keyword 14	600	1	0	15	2	Tag 1
8	Keyword 17	600	1	0	21	3	Tag 1
9	Keyword 18	600	17	-1	14	0	Tag 1
10	Keyword 20	500	16	-1	17	2	Tag 1
11	Keyword 21	500	8	0	3	0	Tag 1
12	Keyword 22	500	9	-3	1	1	Tag 1
13	Keyword 25	500	7	0	66	-1	Tag 1
14	Keyword 28	400	7	-3	11	-2	Tag 1
15	Keyword 29	400	5	1	44	-3	Tag 1
16	Keyword 31	400	2	0	5	0	Tag 1
17	Keyword 33	400	10	0	53	-3	Tag 1
18	Keyword 34	400	10	-1	73	-2	Tag 1
19	Keyword 35	400	8	-1	0	0	Tag 1
Tag 1

Cell Formulas
Range	Formula
A3:G19	A3	=FILTER(Sheet1!A3:G37,Sheet1!G3:G37="Tag 1")
Dynamic array formulas.

Hi Thanks


That worked fine on my PC - I am using Microsoft 365 but I have to send to someone who only uses Microsoft Excel 2013 and it will not work for them. The Date just appears with #Name

You wouldnt know how to get it to work no matter what version excel is being used?

Thanks
Sinead

 

[keef2]

you can try this: @Sineadd

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1			Store 1	Store 2
2	Tags	Keyword	Search Volume	CURRENT	DIFFERENCE	CURRENT	DIFFERENCE			Tag 1	Look up value
3	Tag 1	Keyword 1	1000	16	-4	63	-14			Tags	Keyword	Search Volume	CURRENT	DIFFERENCE	CURRENT	DIFFERENCE
4	Tag 2	Keyword 2	900	14	0	0	0			Tag 1	Keyword 1	1000	16	-4	63	-14
5	Tag 4	Keyword 3	900	4	0	17	0			Tag 1	Keyword 5	700	5	0	11	-1
6	Tag 3	Keyword 4	800	16	1	48	-4			Tag 1	Keyword 8	700	3	1	69	-6
7	Tag 1	Keyword 5	700	5	0	11	-1			Tag 1	Keyword 11	600	4	0	15	4
8	Tag 2	Keyword 6	700	29	-7	0	0			Tag 1	Keyword 14	600	1	0	15	2
9	Tag 6	Keyword 7	700	4	0	37	-5			Tag 1	Keyword 17	600	1	0	21	3
10	Tag 1	Keyword 8	700	3	1	69	-6			Tag 1	Keyword 18	600	17	-1	14	0
11	Tag 7	Keyword 9	600	27	-2	11	3			Tag 1	Keyword 20	500	16	-1	17	2
12	Tag 8	Keyword 10	600	2	1	19	35			Tag 1	Keyword 21	500	8	0	3	0
13	Tag 1	Keyword 11	600	4	0	15	4			Tag 1	Keyword 22	500	9	-3	1	1
14	Tag 3	Keyword 12	600	29	-5	0	0			Tag 1	Keyword 25	500	7	0	66	-1
15	Tag 4	Keyword 13	600	1	3	60	2			Tag 1	Keyword 28	400	7	-3	11	-2
16	Tag 1	Keyword 14	600	1	0	15	2			Tag 1	Keyword 29	400	5	1	44	-3
17	Tag 5	Keyword 15	600	41	59	0	0			Tag 1	Keyword 31	400	2	0	5	0
18	Tag 9	Keyword 16	600	3	-1	26	-1			Tag 1	Keyword 33	400	10	0	53	-3
19	Tag 1	Keyword 17	600	1	0	21	3			Tag 1	Keyword 34	400	10	-1	73	-2
20	Tag 1	Keyword 18	600	17	-1	14	0			Tag 1	Keyword 35	400	8	-1	0	0
Sheet1

Cell Formulas
Range	Formula
J4:P20	J4	=IFERROR(INDEX(A$3:A$37,AGGREGATE(15,6,(ROW($A$3:$A$37)-ROW($A$3)+1)/(ISNUMBER(MATCH($A$3:$A$37,$J$2,0))),ROWS(J$4:J4))),"")

 

[Sineadd]

=IFERROR(INDEX(A$3:A$37,AGGREGATE(15,6,(ROW($A$3:$A$37)-ROW($A$3)+1)/(ISNUMBER(MATCH($A$3:$A$37,$J$2,0))),ROWS(J$4:J4))),"")

Hi Thanks for this

Would you mind explaining what the 15,6, part of the formaula does - the data I ham using is much larger and I want to make sure I enter the formula correctly


Thanks

 

[keef2]

It is part of the Aggregate function. Basically allows user flexibility on how it functions along with options for parameters. Small allows the formula to continue to look for the next k-th smallest condition. And option 6 ignoring error values just makes the data a cleaner set and allows it to continue to work so you don't error out.

Here is a list of those parameters and options:

Parameters or Arguments​

function
The function that you wish to use and can be any of the following values:

Value	Explanation
1	AVERAGE
2	COUNT
3	COUNTA
4	MAX
5	MIN
6	PRODUCT
7	STDEV.S
8	STDEV.P
9	SUM
10	VAR.S
11	VAR.P
12	MEDIAN
13	MODE.SNGL
14	LARGE
15	SMALL
16	PERCENTILE.INC
17	QUARTILE.INC
18	PERCENTILE.EXC
19	QUARTILE.EXC

options
Specifies which values to ignore when applying the function to the range. If the options parameter is omitted, it assumes that options is set to 0. options can be any of the following values:

Value	Explanation
0	Ignore nested SUBTOTAL and AGGREGATE functions
1	Ignore nested SUBTOTAL, AGGREGATE functions, and hidden rows
2	Ignore nested SUBTOTAL, AGGREGATE functions, and error values
3	Ignore nested SUBTOTAL, AGGREGATE functions, hidden rows, and error values
4	Ignore nothing
5	Ignore hidden rows
6	Ignore error values
7	Ignore hidden rows and error values

 

[Sineadd]

you can try this: @Sineadd

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1			Store 1	Store 2
2	Tags	Keyword	Search Volume	CURRENT	DIFFERENCE	CURRENT	DIFFERENCE			Tag 1	Look up value
3	Tag 1	Keyword 1	1000	16	-4	63	-14			Tags	Keyword	Search Volume	CURRENT	DIFFERENCE	CURRENT	DIFFERENCE
4	Tag 2	Keyword 2	900	14	0	0	0			Tag 1	Keyword 1	1000	16	-4	63	-14
5	Tag 4	Keyword 3	900	4	0	17	0			Tag 1	Keyword 5	700	5	0	11	-1
6	Tag 3	Keyword 4	800	16	1	48	-4			Tag 1	Keyword 8	700	3	1	69	-6
7	Tag 1	Keyword 5	700	5	0	11	-1			Tag 1	Keyword 11	600	4	0	15	4
8	Tag 2	Keyword 6	700	29	-7	0	0			Tag 1	Keyword 14	600	1	0	15	2
9	Tag 6	Keyword 7	700	4	0	37	-5			Tag 1	Keyword 17	600	1	0	21	3
10	Tag 1	Keyword 8	700	3	1	69	-6			Tag 1	Keyword 18	600	17	-1	14	0
11	Tag 7	Keyword 9	600	27	-2	11	3			Tag 1	Keyword 20	500	16	-1	17	2
12	Tag 8	Keyword 10	600	2	1	19	35			Tag 1	Keyword 21	500	8	0	3	0
13	Tag 1	Keyword 11	600	4	0	15	4			Tag 1	Keyword 22	500	9	-3	1	1
14	Tag 3	Keyword 12	600	29	-5	0	0			Tag 1	Keyword 25	500	7	0	66	-1
15	Tag 4	Keyword 13	600	1	3	60	2			Tag 1	Keyword 28	400	7	-3	11	-2
16	Tag 1	Keyword 14	600	1	0	15	2			Tag 1	Keyword 29	400	5	1	44	-3
17	Tag 5	Keyword 15	600	41	59	0	0			Tag 1	Keyword 31	400	2	0	5	0
18	Tag 9	Keyword 16	600	3	-1	26	-1			Tag 1	Keyword 33	400	10	0	53	-3
19	Tag 1	Keyword 17	600	1	0	21	3			Tag 1	Keyword 34	400	10	-1	73	-2
20	Tag 1	Keyword 18	600	17	-1	14	0			Tag 1	Keyword 35	400	8	-1	0	0
Sheet1

Cell Formulas
Range	Formula
J4:P20	J4	=IFERROR(INDEX(A$3:A$37,AGGREGATE(15,6,(ROW($A$3:$A$37)-ROW($A$3)+1)/(ISNUMBER(MATCH($A$3:$A$37,$J$2,0))),ROWS(J$4:J4))),"")

Thank you - this worked perfectly

 

[keef2]

Thank you - this worked perfectly

Good deal thanks for closing the loop.