Formula to check column and return multiple values

Hello everyone,

I want to make an excel formula that checks if a column doesn't contain a blank cell if it doesn't it needs to return a value from that specific row if it doesn't it needs to move on to the next so that every number is neatly in sequence and no blank spaces are present anymore.
This could be done by copying the columns pasting them as values and then use a sort, but I want to have the proces automated for future users of the excel worksheet.

This is my currnet formula:
{=IFERROR(INDEX($E$8:$E$12;SMALL(IF(""<>$F$8:$F$12;MATCH(ROW($F$8:$F$12);ROW($F$8:$F$12));"");ROWS($N$1:N1)));"")}

It is checking the F column for empty cells and if it doesn't encounter them selecting the row numbers returns the value of that row in column E it also needs to do this for F and G.

I have two problems with the formula as of now:
1. It works well when retrieving cells from column E and G but doesn't in F. (SOLVED)
2. It now specifically checks for a certain array length but, I need it to do this for the entire E,F and G column no matter the length since this will deviate in the future (all columns start at row 8 in the current sheet)

I have worked with excel a fair bit but, I am no expert whatsoever. So if you have an answer to my problems it would be very much appreciated if you could explain the answer.

Thank you all for your time!

Welcome to the forum.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Maybe this in M2, copied across and down:

=IFERROR(INDEX(E:E,AGGREGATE(15,6,ROW($F$8:$F$200)/($F$8:$F$200<>""),ROWS(M$7:M7))),"")

It's an ordinary formula, but you may need ; not , as the separator.

Sure, but to avoid ambiguity, can you repost a samples sheet in a new reply to this thread (not an edit of Post 1)? That way, I'll get an email when yo make the change.

No.... I wouldn'd to it like that at all. There's no reason why you can't simply use ranges that are "future proof"

=IFERROR(INDEX('data processing'!E:E,AGGREGATE(15,6,ROW('data processing'!$F$8:$F$100)/('data processing'!$F$8:$F$100<>""),ROWS(A$1:A1))),"")

copied across and down will do exactly what you want. AGGREGATE is perfectly hapy with the range inclusing blank rows, as you're selcting only the non blanks with the bit in red. INDIRECT is not needed at all. It's best avoided , whenever possible, as it's volatile, recalculating every time anything changes on the sheet. That can cause big performance issues.

However, if you do want to use INDIRECT, for some unexplained reason:

=IFERROR(INDEX('data processing'!E:E,AGGREGATE(15,6,ROW(INDIRECT("'data processing'!F8:F"&'data processing'!$H$8))/(INDIRECT("'data processing'!F8:F"&'data processing'!$H$8)<>""),ROWS(E$1:E1))),"")

copied across and down... but I wouldn't...

Originally Posted by Glenn Kennedy

No.... I wouldn'd to it like that at all.

I will take your advise and avoid the volatile function, I was just worried about the difference in rows of data between different experiments.

Thanks for the help and advise! I really do appreciate it.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.