Nested If Statement with Index and Match

[CrazyDave]

Good day good friends,
I have a list of names which I would like to automatically fill into another tab on my spreadsheet. That bit is easy.
My problem is that there are three entries in the first list, that I don't want in the second list. They are important in the first, but not needed in the second.

In A6:A30 of a tab called WeeklyTimings, I have
Billy
Jessica
Sarah
Simon
Brisbane 1
Terry
Abraham
Albert
Brisbane 2
Peter
Barry
John
Ingrid
Brisbane 3

I need this list to appear in A7 downwards of a tab called Mornings, minus the three "Brisbane" entries. The position of the Brisbane entries is completely random. The number of total names is completely random.
I could use a formula in Column B on WeeklyTimings to sort which names to carry over, but I don't have the room for that...
I'm hopeful someone can show me a formula to put into the Mornings tab which will do the trick. I have been tussling with this for a few weeks with no luck.

Many thanks, in anticipation...

 

[sandy666]

with Power Query
you can load result wherever you want

Column1		Column1
Billy		Billy
Jessica		Jessica
Sarah		Sarah
Simon		Simon
Brisbane 1		Terry
Terry		Abraham
Abraham		Albert
Albert		Peter
Brisbane 2		Barry
Peter		John
Barry		Ingrid
John
Ingrid
Brisbane 3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Filter = Table.SelectRows(Source, each not Text.StartsWith([Column1], "Brisbane"))
in
    Filter

 

[CrazyDave]

Sorry Sandy666, what is Power Query?

Thought I was getting to know Excel...

ps... thanks for your quick response!

 

[sandy666]

more about PowerQuery aka Get&Transform

• What is PowerQuery (Get&Transform)
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference (erratum required)
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

and update your profile about Excel version

 

[Fluff]

What version of Excel are you using?
Please update you account details to show this, as it affects which functions you can use.

 

[Fluff]

Two options depending on your version

+Fluff New.xlsm
	A	B	C	D
1	Names		Filter	Aggregate
2	Billy		Billy	Billy
3	Jessica		Jessica	Jessica
4	Sarah		Sarah	Sarah
5	Simon		Simon	Simon
6	Brisbane 1		Terry	Terry
7	Terry		Abraham	Abraham
8	Abraham		Albert	Albert
9	Albert		Peter	Peter
10	Brisbane 2		Barry	Barry
11	Peter		John	John
12	Barry		Ingrid	Ingrid
13	John
14	Ingrid
15	Brisbane 3
16
Main

Cell Formulas
Range	Formula
C2:C12	C2	=FILTER(A2:A50,(LEFT(A2:A50,8)<>"Brisbane")*(A2:A50<>""))
D2:D15	D2	=IFERROR(INDEX($A$2:$A$50,AGGREGATE(15,6,(ROW($A$2:$A$50)-ROW($A$2)+1)/(LEFT($A$2:$A$50,8)<>"Brisbane")/($A$2:$A$50<>""),ROWS(D$2:D2))),"")
Dynamic array formulas.

 

[CrazyDave]

Many thanks Sandy666 and Fluff for your responses. I haven't yet got into Dynamic Arrays, maybe one day...
I've just downloaded Power Query for my 2013 version of Excel, and will give that a go.

Profile has been updated too.

 

[sandy666]

You are welcome & thanks for the feedback
Let us know if it will work for you

 

[Peter_SSs]

Hmm, perhaps it is the 'Brisbane' issue but when reading this I began to think the unwanted items might be the ones marked with a number, no matter what the text before. In case of that

20 07 26.xlsm
	A	B	C
1
2
3
4
5	Names		New List
6	Billy		Billy
7	Jessica		Jessica
8	Sarah		Sarah
9	Simon		Simon
10	Brisbane 1		Terry
11	Terry		Abraham
12	Abraham		Albert
13	Albert		Peter
14	Brisbane 2		Barry
15	Peter		John
16	Barry		Ingrid
17	John
18	Ingrid
19	Brisbane 3
20
21
Omit Names

Cell Formulas
Range	Formula
C6:C21	C6	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$6:A$30)/ISERROR(RIGHT(A$6:A$30,1)+0),ROWS(C$6:C6)))&"","")

 

[CrazyDave]

Thankyou Peter!!!
You are quite correct. I submitted a false list of names, and while I could have easily substituted Brisbane for the correct name, this new formula is much easier to use.

The formula provided by Fluff did work, but required more work to edit across the entire week.

Still trying to find the time to get my head around PowerQuery... I've had a quick look, and realised it needed more time than I had...