Author: Oscar Cronquist Article last updated on August 11, 2022

### Question:

I found a question here about tracking a stock portfolio. He would like to automatically create an overview table with a unique stock symbol per row. Â He also wants the range extended down to include new rows as they become valid.

Example,

```Sheet1
Symbol Â  Â  Type Â  Â Shares Â  Â Price
F Â  Â  Â  Â  Â Buy Â  Â  100 Â  Â  Â  12
MSFT Â  Â  Â  Buy Â  Â  100 Â  Â  Â  25
MSFT Â  Â  Â  Buy Â  Â  50 Â  Â  Â  Â 28
F Â  Â  Â  Â  Â Buy Â  Â  100 Â  Â  Â  16
GOOG Â  Â  Â  Buy Â  Â  25 Â  Â  Â  Â 550

Sheet2
Symbol               Quantity
F                    200
MSFT                 150
GOOG                 25```

Sheet1

Sheet2

### Create named ranges

1. Select tab "Formulas" on the ribbon
2. Press with left mouse button on "New..." button
3. Type
Symbol

in Name: bar

4. Type formula:
=Sheet1!\$A\$2:INDEX(Sheet1!\$A:\$A, COUNTA(Sheet1!\$A:\$A))

in "Refers to:" bar

5. Press with left mouse button on Close button

Repeat above instructions with following names and formulas:

Type - =Sheet1!\$B\$2:INDEX(Sheet1!\$B:\$B, COUNTA(Sheet1!\$B:\$B))
Shares - =Sheet1!\$C\$2:INDEX(Sheet1!\$C:\$C, COUNTA(Sheet1!\$C:\$C))
Price - =Sheet1!\$D\$2:INDEX(Sheet1!\$D:\$D, COUNTA(Sheet1!\$D:\$D))

### Array formulas

Array formula in cell A2, sheet 2:

=IFERROR(INDEX(Symbol, MATCH(0, COUNTIF(\$A\$1:A1, Symbol), 0)), "") + CTRL + SHIFT + ENTER

Copy cell A2 and paste down as far as needed. This formula creates uniue distinct symbols. Read this post: How to extract a unique distinct list from a column for a formula explanation.

Array formula in cell B2, sheet 2: