Extract and return a those items which does not have empty cells and rows.

**Mohammad Munawar** · 11-30-2020, 07:12 AM

Hi,

Required a formula help to return and extract those items which does not have empty cells and rows as per data contains column B to D.

Sample sheet with desired results attached.

Thanks,

**Pete_UK** · 11-30-2020, 07:26 AM

Put this formula in E6:

=IF(COUNTIF(B6:D6,""),"",MAX(E$5:E5)+1)

Copy down to the bottom of your data. Then you can use this formula in F6:

=IFERROR(INDEX(B:B,MATCH(ROWS($1:1),$E:$E,0)),"")

Copy across and down, as required.

Hope this helps.

Pete

**Mohammad Munawar** · 11-30-2020, 09:08 AM

Thanks pete UK for your formula solution.

**Pete_UK** · 11-30-2020, 12:41 PM

You're welcome - glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Thanks for the rep, as well.

Pete

**Bo_Ry** · 11-30-2020, 01:58 PM

For MS365 Please try

=FILTER(B6:D17,NOT(MMULT(--(B6:D17=""),SEQUENCE(COLUMNS(B6:D17),,,0))))

**Mohammad Munawar** · 11-30-2020, 02:14 PM

Thanks Bo ry for another option solution.

Extract and return a those items which does not have empty cells and rows.

LinkBack

Thread Tools

Rate This Thread

Display

Extract and return a those items which does not have empty cells and rows.

Re: Extract and return a those items which does not have empty cells and rows.

Re: Extract and return a those items which does not have empty cells and rows.

Re: Extract and return a those items which does not have empty cells and rows.

Re: Extract and return a those items which does not have empty cells and rows.

Re: Extract and return a those items which does not have empty cells and rows.

Thread Information

Users Browsing this Thread

Similar Threads

Formula to Return Results without any Empty Rows

[SOLVED] Formula to return N for cells with 0 and empty cells, and Y for cells with data

Return all rows with at least one empty cell

[SOLVED] Extract Unique Items (No VBA/No Helper Cells)

[SOLVED] extract only certain values from a list which contains empty rows

[SOLVED] Extract values from a list containing empty cells

Listing Items Without Empty Cells

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions