• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Extraction using sector wise?

I have some data in Sheet 1. Using this data i need to extract the data according to sectorwise. My idea is if i create a drop down in K1 (yellow) so once i click on the sector say "PHARMA" from the dropdown list in K1 all the company names which are related to pharma with their respective PE, EY, ROCE, ROE has to come in the new heading. Please help me out how this can be done.

Working file attached.

Regards
Sonjoe
 

Attachments

  • Data Extract.xlsx
    9 KB · Views: 7
With Power Query, I have created a parameter query which you can see in the attached. Follow the instruction in the worksheet.
 

Attachments

  • Data Extract.xlsx
    27.2 KB · Views: 3
If you have Office 365 and FILTER function then following approach can be used:

To find out companies matching criteria:
=FILTER(B3:B10,C3:C10=K1)

And then a typical formula to produce rest of the data:
=VLOOKUP($J3#,$B$2:$G$10,MATCH(K$2,$B$2:$G$2,0))

Please see attached file for demonstration.
 

Attachments

  • Data Extract.xlsx
    11.5 KB · Views: 3
If you have Office 365 and FILTER function then following approach can be used:

To find out companies matching criteria:
=FILTER(B3:B10,C3:C10=K1)

And then a typical formula to produce rest of the data:
=VLOOKUP($J3#,$B$2:$G$10,MATCH(K$2,$B$2:$G$2,0))

Please see attached file for demonstration.
I had gone for a second filter rather than the lookups. Building on your idea of a lookup, I returned the sequence number and then used it to return data, with columns in any order, as a single spilt range. I wonder how many users would even recognise the formula as being Excel?
75316
 

Attachments

  • Data Extract.xlsx
    14.3 KB · Views: 6
I had gone for a second filter rather than the lookups. Building on your idea of a lookup, I returned the sequence number and then used it to return data, with columns in any order, as a single spilt range. I wonder how many users would even recognise the formula as being Excel?
View attachment 75316
Since OP had mentioned VLOOKUP and MATCH, I demonstrated with it limiting the newer function to its core requirements. Unfortunately, we have not received update with LET so I get the typical _xlfn.LET on my computer with your file. New functions added by Microsoft are powerful and appear more like SQL / Code like sometimes to me. I would not be surprised someday if we get a SELECT function which probably will look like below
=SELECT(field,with,table,DB)
and be able to fetch values from DB specified by user. Sorry for digressing from the subject.
 
Sorry about that; I didn't realise that LET has not completed its rollout. It is strange that, while LET provides no new functionality, it is such a useful aid to developing formulas which are, to an extent, self-documenting. For a while the data-management tools of Tables and PowerQuery outstripped the world of spreadsheet formulas but I think dynamic arrays have redressed the balance and FILTER even allows array techniques to compete when applied to data manipulation problems, at least to the point of 10,000 or so records.

I had rather assumed that PowerQuery would remain the preferred route to accessing external data but the ability to read from, and write to, database tables directly from the worksheet would assist collaboration.
 
Back
Top