Return Cells in Column that Match Criteria

Hello,

I would like to return cells in 2 columns that match certain criteria (exclude non matches and blanks). In one of my examples, I'm trying to get all cells that are an URL with the following (can't save due to error):

Formula:

Please Login or Register  to view this content.

Any suggestions?

.
Perhaps I've misunderstood your goal ... but try this :

In C5 paste : =IF(OR(A4="Impression",A4="Click"),A4,"")

In D5 paste : =IF(OR(C5="Impression", C5="Click"),B3,"")

Then drag down both columns.


Keep in mind you are using numerous MERGED CELLS. It would be best to NOT USE MERGED CELLS. They are the bane of Excel
and create more problems and issues than they are worth.

Apologies if I'm not explaining correctly, my goal to to get every cell with either "Clicks or Impressions" and every column that is a URL - as separate entities removing blanks.

GOALS (from the spreadsheet).
1st Column: Only want cells that are "Click" or "Impression"
2nd Column: Only want cells that are URLs

@Christopher - your solution attempts to find every URL where H exists, I just want every instance of "Click" or "Impression" from column A (exclude everything else) and in a separate column, every URL in B (excluding everything else).
@Logit - I would have blank rows in the new columns with that approach, I want every instance excluding the resulting non matching values (blanks)

.
Ok ... better understood now.

My approach would use VBA Macro.

You still need to rid the worksheet of merged cells.

Since you are on O365, you can use the new dynamic array formulas for this. Use this formula for the Type:

=FILTER(A:A,(A:A="Impression")+(A:A="Click"))

I'm not sure how you want to determine if the value is a URL, but maybe this:

=FILTER(B:B,LEFT(B:B,4)="http")

The attached shows these formulas giving the same result as in your example.

Thanks Gregb11, unfortunately my company is on the Semi-Annual release schedule so we aren't scheduled to get Dynamic Array support until the July Microsoft release. In my version of Excel, it converted your Dynamic Array (FILTER) into the old style CSE behind the scenes but without changing the cell refernce. Is there an equivalent current array formula I can use?

Formula:

Please Login or Register  to view this content.

The Moderator believes this is the same question, closed my other thread and asked me to post this ere. I have a column that has a mix of content, empty and blank strings from formulas and I simply want a list of all of the cells with values in them. This spreadsheet will live n SharePoint so I cannot use Macros, VBA or Power Query.

TYPE Cell Display
Text Test1
Formula of =""
Number 12345
Empty cell
Text Test3
Empty cell


What I want back
Test1
12345
Test3

I've tried several variations of the following, but one way or another I get incorrect values or "0" instead of the cell's value.

Formula:

Please Login or Register  to view this content.

Thanks

Try this in J2. Use Ctrl+Shift+Enter (instead of just Enter) and then drag down

Please Login or Register  to view this content.

Thanks Crowater - that worked!