List items based on criterias in two different col

问题:

I have the following Excel spreadsheet:

         A           B            C        

1     Product      Sales        List      

2    Product A      500       Product A
3    Product B                Product C
4    Product C      400       Product D
5                             Product E  
6    =""                      Product F
7    Product D      600       Product H
8    Product E      550       
9    =""
10   Product F      200
11   Product G      =""
12   Product H      800 

In Column A and Column B different products with their sales are listed. As you can see it can either happen that there are empty cells or cells with ="" in both Column A or Column B.

In Column C I want to achieve now that only the products which do NOT have an empty cells or cells with ="" in Column A or Column B are inlcuded in the list.

I could already make it work for Column A with this formula:

={INDEX($A$2:$A$100,SMALL(IF(LEN($A$2:$A$100)=0,"",ROW($A$2:$A$100)-MIN(ROW($A$2:$A$100))+1),ROW(A1)))}

What do I have to change in this formula to also exclude the products wich have an empty cell or a cell ="" in Column B from my list in Column C?

回答1:

When you have worked it out for Column A, it is very simple to do for B:

Each cell in Column D has the appropriate function: (Example for D2)

=VLOOKUP(D2, $A:$B, 2, 0)

NOTE: This assumes you don't have repeated values in Column A

回答2:

doesn't have to be an array formula. use this formula in C instead.

=IF(AND(A:2<>"",B:2<>""),A:2,"")

Then autofill the formula. Then sort column C to get all product list.

or pivot the range by column C in row box to get the distinct product list in case A has duplicate products.