Create a new list from exiting data with variables

[jdhfch]

Hi,

Please can someone help?

I am trying to create a list from a master list based on a criteria. Below is an example

https://www.dropbox.com/s/ui61g35ycc...nshot.jpg?dl=0

Basically, I want to create a list from the master list in row A & B

I want to use the account manager name in cell D3 (John in this instance) and pull all instances of John from row A, then all instances of Johns customers as well in row B. I want there to be no spaces.

Many thanks

Jason

 

[AlanY]

deleted

 

[AlanY]

post #2 didn't got it quite right, try this instead

Book1
	A	B	C	D	E
1
2
3	A N Other	1		John	13
4	A N Other	2		John	6
5	John	13		John	7
6	A N Other	4
7	A N Other	5
8	John	6
9	John	7
10	A N Other	8
11	A N Other	9
12	A N Other	10
2

Cell Formulas
Range	Formula
E3	{=IFERROR(INDEX($B$3:$B$12,SMALL(IF($A$3:$A$12=D3,ROW($A$3:$A$12)-(ROW($A$3)-ROW($A$1))),COUNTIF(D$3:D3,D3))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jdhfch]

Sorry, I can't get this to work?

 

[AlanY]

have you followed this?

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[jdhfch]

Hi AlanY

I haver done this.

 

[AlanY]

this is the one i'm working on, have a look to see if you can spot something difference

https://drive.google.com/file/d/1XfyRnyhOOYuHTdOBMduqo0tGBplUH8OJ/view?usp=sharing

 

[jdhfch]

this is the one i'm working on, have a look to see if you can spot something difference

https://drive.google.com/file/d/1XfyRnyhOOYuHTdOBMduqo0tGBplUH8OJ/view?usp=sharing

Hi AlanY

I am clearly being thick.. I can't get it to work at all.. Could I be cheeky and ask if you could look at the linked sheet and see if you can figure out what I need to do.. I am wanting to copy the data from the master list to the green cells?

https://drive.google.com/open?id=1HbCySZiQQL2JgkLwjmn2gBhqI3uOr9Vd3o6Oy3W13mQ

rgds

jdhfch

 

[AlanY]

done, file in

https://drive.google.com/file/d/1Peb0hsuhLEIxWdHIqrevg2v1-0xfSe94/view?usp=sharing

Book1
	J	K	L	M	N	O	P	Q	R	S	T
1
2							Account Man 1
3	Acc Man	Acc	Acc No	AKA	AKA Acc no		Acc Man	Acc	Acc No	AKA	AKA Acc no
4	Account Man 1	a	1	a1	1		Account Man 1	a	1	a1	1
5	Account Man 2	b	2	b2	2		Account Man 1	g	7	g7	7
6	Account Man 3	c	3	c3	3		Account Man 1	k	11	k11	11
7	Account Man 4	d	4	d4	4		Account Man 1	l	12	l12	12
8	Account Man 5	e	5	e5	5		Account Man 1	m	13	m13	13
9	Account Man 6	f	6	f6	6		Account Man 1	p	16	p16	16
10	Account Man 1	g	7	g7	7		Account Man 1	q	17	q17	17
11	Account Man 2	h	8	h8	8		Account Man 1	r	18	r18	18
12	Account Man 3	i	9	i9	9
13	Account Man 4	j	10	j10	10
14	Account Man 1	k	11	k11	11
15	Account Man 1	l	12	l12	12
16	Account Man 1	m	13	m13	13
17	Account Man 2	n	14	n14	14
18	Account Man 3	o	15	o15	15
19	Account Man 1	p	16	p16	16
20	Account Man 1	q	17	q17	17
21	Account Man 1	r	18	r18	18
Sheet1

Cell Formulas
Range	Formula
Q4	{=IFERROR(INDEX($J$4:$N$21,SMALL(IF($J$4:$J$21=$P4,ROW($J$4:$J$21)-(ROW($J$4)-ROW($J$1))),COUNTIF($P$4:$P4,$P4)),MATCH(Q$3,$J$3:$N$3,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jdhfch]

You're a star.. thank you AlanY