# Help with repeating formula until condition is met

#### somedudenamedmichael

##### New Member
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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
this should work for what you're needing
-----------
Book1
ABCDEF
1namee-mailnamee-mail
2President of Campo de Ouriquethisemailisfictitious@gmail.comPresident of Flatlandthisemailisfictitiousaswell@gmail.com
3President of Somewhere ElsePresident of Campo de Ouriquethisemailisfictitious@gmail.com
4President of Mordor
5President of Flatlandthisemailisfictitiousaswell@gmail.com
6
Sheet1
Cell Formulas
RangeFormula
E2:F6E2=IFERROR(INDEX(SORT(FILTER(A2:B5,(B2:B5>"")*(A2:A5>"")),2,-1),SEQUENCE(5),{1,2}),"")
Dynamic array formulas.

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

this should work better for 2016 version
----------------
Help with repeating formula until condition is met.xlsx
ABCDE
1namee-mailnamee-mail
2President of Campo de Ouriquethisemailisfictitious@gmail.comPresident of Campo de Ouriquethisemailisfictitious@gmail.com
3President of Somewhere ElsePresident of Flatlandthisemailisfictitiousaswell@gmail.com
4President of Mordor
5President of Flatlandthisemailisfictitiousaswell@gmail.com
Sheet2
Cell Formulas
RangeFormula
E2:E3E2=VLOOKUP(D2,A\$2:B\$5,2,FALSE)
D2:D5D2=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A:A)/(B:B>0),ROW(A2))),"")

@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

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.

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".

Ok, in D2 filled down try
Excel Formula:
``=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))),"")``

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.

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!

Replies
6
Views
167
Replies
7
Views
177
Replies
2
Views
82
Replies
13
Views
275
Replies
2
Views
196

1,203,625
Messages
6,056,387
Members
444,862
Latest member
more_resource23

### 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.

### Which adblocker are you using?

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

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