Help with repeating formula until condition is met

[somedudenamedmichael]

Hey everyone, new guy here, so sorry in advance if the question isn't posed in the clearest of manners.

Anyway, I'm trying to get Table 2 (as shown below) from Table 1, while trying to avoid macros, since I don't comprehend them yet. For the sake of this example let's say the "name" Column is A and "e-mail" Column is B, and the rows follow the same logic.

So basically, I want Table 2's A2 cell, and all following rows, to be filled with the text from Table 1's A column, so long as there is a corresponding e-mail in Table 1's B column, ignoring all Presidents without an e-mail. This repeated testing for that condition should continue through a certain range of Table 1's A column. It should also be noted that all of Table 1's B column cells without an e-mail, are filled with "-".

Then, a similar formula will have to be created for Table 2's "e-mail" (B) column, but I feel like I can get that done when I understand the logic behind what has to be done in the A column.

Once again, sorry for not using XL2BB to make your lives easier, but I ran into an error when trying to use it, and I was in a hurry to post this.

Thanks in advance for the help!

Table 1

name	e-mail
President of Campo de Ourique	thisemailisfictitious@gmail.com
President of Somewhere Else	-
President of Mordor	-
President of Flatland	thisemailisfictitiousaswell@gmail.com

Table 2

name	e-mail
President of Campo de Ourique	thisemailisfictitious@gmail.com
President of Flatland	thisemailisfictitiousaswell@gmail.com

 

[ExceLoki]

this should work for what you're needing
-----------

Book1
	A	B	C	D	E	F
1	name	e-mail			name	e-mail
2	President of Campo de Ourique	thisemailisfictitious@gmail.com			President of Flatland	thisemailisfictitiousaswell@gmail.com
3	President of Somewhere Else				President of Campo de Ourique	thisemailisfictitious@gmail.com
4	President of Mordor
5	President of Flatland	thisemailisfictitiousaswell@gmail.com
6
Sheet1

Cell Formulas
Range	Formula
E2:F6	E2	=IFERROR(INDEX(SORT(FILTER(A2:B5,(B2:B5>"")*(A2:A5>"")),2,-1),SEQUENCE(5),{1,2}),"")
Dynamic array formulas.

 

[Fluff]

@ExceLoki the OP's profile shows 2016 so cannot use some of those functions.

 

[ExceLoki]

this should work better for 2016 version
----------------

Help with repeating formula until condition is met.xlsx
	A	B	C	D	E
1	name	e-mail		name	e-mail
2	President of Campo de Ourique	thisemailisfictitious@gmail.com		President of Campo de Ourique	thisemailisfictitious@gmail.com
3	President of Somewhere Else			President of Flatland	thisemailisfictitiousaswell@gmail.com
4	President of Mordor
5	President of Flatland	thisemailisfictitiousaswell@gmail.com
Sheet2

Cell Formulas
Range	Formula
E2:E3	E2	=VLOOKUP(D2,A$2:B$5,2,FALSE)
D2:D5	D2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A:A)/(B:B>0),ROW(A2))),"")

 

[somedudenamedmichael]

@ExceLoki Thanks for the answer. Unfortunately it isn't quite working for me yet and I'm struggling to understand why. Basically, the problem is that it's not ignoring Presidents without an e-mail.

I feel like the easiest way to get some better insight from you, would be for me to pass the file that I'm working on to you, and have you take a look at it, if that's something you'd be open to. So with this I ask you, is this something you can help me with, and if so, how'd you prefer to go about it (aka how should I send you the file)?

I should note that Excel is in Portuguese for me, and it unfortunately is the 2016 edition, so I'm not sure if everything would work the same for you.

Once again, Thanks for the help so far

 

[Fluff]

If you want to share a workbook, then you need to upload to a share site such as OneDrive, DropBox, GoogleDrive. Then mark for sharing & post the link you are given to the thread.

 

[somedudenamedmichael]

BaseDadosMun.xlsx

Base_Dados_Municipios Presidente,Chefe Gabinete,Secretária,Adjunto 1 Entidades Intermunicipais,Municípios,Força Política,Maioria Partidária?,Presidente Comunidade Intermunicipal,Nome,Pelouros,Anos Mandato Consecutivos,E-mail,E-mail 2,E-mail GAP,Telemóvel,Nome,E-mail,Telemóvel,Nome,E-mail,Telemó...

docs.google.com

Alright so here goes the link. It has quite a few tabs, but if you do look into it, you only need to mind the second one "Presidentes".

 

[Fluff]

Ok, in D2 filled down try

=IFERROR(INDEX($A$2:$A$1000,AGGREGATE(15,6,(ROW($A$2:$A$1000)-ROW($A$2)+1)/($B$2:$B$1000<>"-"),ROWS(D$2:D2))),"")

 

[ExceLoki]

BaseDadosMun.xlsx

Base_Dados_Municipios Presidente,Chefe Gabinete,Secretária,Adjunto 1 Entidades Intermunicipais,Municípios,Força Política,Maioria Partidária?,Presidente Comunidade Intermunicipal,Nome,Pelouros,Anos Mandato Consecutivos,E-mail,E-mail 2,E-mail GAP,Telemóvel,Nome,E-mail,Telemóvel,Nome,E-mail,Telemó...

docs.google.com

Alright so here goes the link. It has quite a few tabs, but if you do look into it, you only need to mind the second one "Presidentes".

are you using google sheets or excel 2016? or was the google sheets only to share the workbook?

if you're using excel 2016 either mine or @Fluff solutions should work
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A:A)/(B:B<>"-"),ROW(A2))),"")
or
=IFERROR(INDEX($A$2:$A$1000,AGGREGATE(15,6,(ROW($A$2:$A$1000)-ROW($A$2)+1)/($B$2:$B$1000<>"-"),ROWS(D$2:D2))),"")

if you're using google sheets, it will not recognize the AGGREGATE function in the formulas.

 

[somedudenamedmichael]

Alright! It's working!

After checking, both of your formulas work now. @ExceLoki I'm guessing that the problem was the "(B:B>0)" that you had in your original solution, but it's all good now.

Anyways, thanks for taking the time out of your days to help me out! I can now finally tell my boss that I have completed his database, althought I bet I could have done this whole thing in a way more efficient manner.

I'll be leaving one more reply on here quoting @ExceLoki 's original answer and these last 2 corrected formula's so that the solution can all be in one message.

Have a good one!