how to link worksheet1 column to a dynamic named range list in a table in worksheet2

[rny1ef]

hi folks,

so this was a 2step problem:
- 1st step was to extract a unique list, w/o blanks, from a large dynamic named range in a table; the extract is a new column in the table
- 2nd step is to link to that result column and pull the values into a new worksheet; the list can grow and/or shrink and there will be formulas in the new worksheet for new calcs

I used the following to get the 1st step done:

* =IF(COUNTIF($K$2:K2,sectors)=1,K2,"")
[this puts an intermediate result into a column that shows distinct names but with blanks as they occur in the source data]

* {=INDEX(sectors_extract,SMALL(IF(LEN(sectors_extract)=0,"",ROW(sectors_extract)-MIN(ROW(sectors_extract))+1),ROW(A1)))}
[this removes the blanks and just leaves #NUM in the column for those rows where there were blank entries in 'sectors'; I've seen this link http://www.get-digital-help.com/2009...om-one-column/ but I was not able to make it work exactly and used a variation from another source]

so, now, I want to take the data in the column named sector_list [this is the result set from the 1st step; there are names, and then #NUM for all the blanks; I tried creating a dynamic named range for the values up to the rows with #NUM but that didn't work] and pull it into a different worksheet in the same workbook.
I tried to use a variation of this from the link above as a base:

Step 1 - Create an array with the same size as the list
=INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0))
COUNTIF($B$1:B1,List) returns an array containing either 1 or 0 based on if $B$1:B1 is found somewhere in the array List .
COUNTIF($B$1:B1,List)

but all I got was circular reference errors. is that because I am trying to pull a dynamic named range, in an array formula, from a table in 1 worksheet into another worksheet that is not defined as a table?

maybe you can give some guidance?
as much as I'd like an answer, I'd like to understand interacting with tables a whole lot better.
I admit tthat I am more than confused in my own mental circular references.

my data looks like this:
Capture.JPG

thanks
ron

[windy58]

Can you upload a sample workbook? The picture appears to be missing data, items in sector list column do not appear in smg_desc column

Windy

[rny1ef]

RS SECTOR INDUSTRY 14-08-13g.xlsb

windy58,
ok, here is a slimmer version of the table.
thanks for looking.
consider this newbie effort.

it takes all the tickers on the exchange, with embedded industry and sector info, and tries to pull lists of the unique sectors and unique industries and put them in separate worksheets.
I'm sort of halfway there, learning excel as I go along.

thanks,
ron

[windy58]

Have you considered using advanced filters?

Is this a process repeated or is it a one off?

Windy

[Glenn Kennedy]

Have a look at this and see if it meets your needs...

[Glenn Kennedy]

Ha ha. It would help if I had attached the correct attachment!! This does what you want, but it is probably too slow to be of much practical use...

[rny1ef]

glenn,
excellent - thankyou. I printed the 2 routines and will analyze so I can learn. first off, yes, a tad slow but it works so I need to understand the logic of what you did. I've been reading all this stuff with index/match/offset/countif - I am beginning to see what the strategies are, but I don't quite see the end of the tunnel as yet.
thanks again,
ron

[rny1ef]

wind58,

this is actually a process to repeat each week. names may drop off and/or added each week, so the table was set as an excel 'table' to handle dynamic lists. the website get-digital-help has strategies that work, but somehow I can get them to work for me. my guess is I don't have this array formula concept well understood; that, the dynamic named range concept, and where these arrays 'exist' during execution - maybe that is why I get circular reference messages when I try this stuff.

[Glenn Kennedy]

If you have any further problems - please post again. however, if that's your current problem solved, can you mark the thread as solved and (preferably) say thanks to those who helped by clicking the "Add Reputation" button at the foot of their post(s)?

[rny1ef]

glenn,

ok, still getting educated.
tried your formula, and yes, slow, at least on my latop; but more importantly, I am still understanding it.
so, I tried this, and it makes sense, but it has problems:

[from get-digital-help]
=IFERROR(INDEX(Table7[name],MATCH(0,COUNTIF($A$1:A1,Table7[name]),0)),"")
[and you make it an array formula]

problems:
1. if table7 refreshes[using the excel 'refresh-all' button], and rows are added, the array formula is locked [can't add rows]
2. if table7 refreshes, and rows are deleted, the array formula winds up showing a 0 in the row that was deleted instead of #NA[which it did before the IFERROR function was added], which is fine, but still comes up short

so, I tried to make the 3rd worksheet a table, to see if I could get the first table, once refreshed, to force the second table to be 'refreshed'.
but that was a total disaster.

however Microsoft implemented 'refresh-all', to wipe out a original table, read in all new values from the source, expanding or contracting the size of the resulting table, well that I can't figure out how to do. and i don't know how to link/cascade/[whatever] an additional table to the first table.


maybe you can take a look while I go back and try to understand your solution?
clearly your solution works; and the second formula works as well but not as clean as yours just seems to be faster.

i attached a workbook with far fewer rows. the first worksheet is the source, the second worksheet shows the result set.
[in the first tab is Table7; i added row 23 after i implemented the second tab -- as you can see, even though Table7 got bigger, the second tab didn't expand]

this is starting to make sense, but just barely, but very worthwhile.

thanks,
ron
glenn - rny1ef(2).xlsb

[Glenn Kennedy]

"My" formula was pasted down a few more rows, to allow for additional data. "Your" formula sopped at the last value. Paste it on down a bit further and voila... it'll update, too.

"My" formula puts the values in ascending order. "your" one puts them in order of appearance in the source sheet. That sorting takes time... Lots of time!

Neither of them like gaps in the data. "Your" formula does handle them better than "mine". A zero is better than a total breakdown.

Take your pick!!

Another alternative is the attached. Try it, too..

[rny1ef]

glenn,

I will mark original as solved - thankyou for excellent help and insight.

++

can I ask a question, and you can tell me if I need to launch another request?

1. so I know the solution is 'slow'; ok, no problem, it works and I learned a lot
2. since I built it as a table, I clicked one of the dropdowns to select 1 ticker, and the response to the simple filter request was 'slow'
- now I realize what it means that index/match/etc are volatile - they re-run on any xls operation
3. so I tried something else to set a benchmark:
- I loaded the original data to access2013
- I executed a sql request:

SELECT [RS SECTOR INDUSTRY].smg_desc, srs_04wk, srs_13wk, srs_26wk, srs_52wk INTO Table2
FROM [RS SECTOR INDUSTRY]
GROUP BY [RS SECTOR INDUSTRY].smg_desc
ORDER BY [RS SECTOR INDUSTRY].smg_desc;

- result was instantaneous
- so, yes, the excel functions are superb, but complex in their solution

now the real question:
4. can I write sql from within excel without having to use access? OR should I try to do all this inside say power pivot?
[if I am going to learn, I might as well go for the whole enchilada; doing it in steps is providing a great learning experience]

thanks,
ron