Create a new list from exiting data with variables

jdhfch

Board Regular
Joined
Jan 25, 2018
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
deleted
 
Last edited:
Upvote 0
post #2 didn't got it quite right, try this instead


Book1
ABCDE
1
2
3A N Other1John13
4A N Other2John6
5John13John7
6A N Other4
7A N Other5
8John6
9John7
10A N Other8
11A N Other9
12A N Other10
2
Cell Formulas
RangeFormula
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
done, file in

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


Book1
JKLMNOPQRST
1
2Account Man 1
3Acc ManAccAcc NoAKAAKA Acc noAcc ManAccAcc NoAKAAKA Acc no
4Account Man 1a1a11Account Man 1a1a11
5Account Man 2b2b22Account Man 1g7g77
6Account Man 3c3c33Account Man 1k11k1111
7Account Man 4d4d44Account Man 1l12l1212
8Account Man 5e5e55Account Man 1m13m1313
9Account Man 6f6f66Account Man 1p16p1616
10Account Man 1g7g77Account Man 1q17q1717
11Account Man 2h8h88Account Man 1r18r1818
12Account Man 3i9i99
13Account Man 4j10j1010
14Account Man 1k11k1111
15Account Man 1l12l1212
16Account Man 1m13m1313
17Account Man 2n14n1414
18Account Man 3o15o1515
19Account Man 1p16p1616
20Account Man 1q17q1717
21Account Man 1r18r1818
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top