• 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.

Search Data of a Column and show all the rows of related search

A

anuwers

Member
Dear Sir,

I have attached a sample with three sheets Master, Input Search , Samples

Samples - Will have the sample i am expecting as an output to display in Input Search Sheet.
Master - Where i have all the datas in that sheet. Expected number of rows will be more than 200000 and column will be around 2O

INput Search - This sheet has my one of the sample where i write the details in yellow box, i need to search the full column as specified to display in the input search.

Basically, it will search only one column and the search can be any combination with in the data in column.

Kindly support as and when i write the yellow box all relevant search should get displayed.
 

Attachments

  • Column Search with available Data.xlsx
    13 KB · Views: 11
I have created a parameter query in Power Query. View the attached workbook. Enter your variable in M2 then on the Data Tab, click on Refresh All and your data will be updated.
 

Attachments

  • Column Search with available Data.xlsx
    25.3 KB · Views: 3
Thank you Mr. Alan.

This will not help us as my data will cross more than 200000 rows.

When I write in M2 and enter, all the related M2 value rows should be displayed down.
Moreover alphabet case sensitive to be removed.
(or) give the VBA code for every M2 entered value to get the required data on the column specified in initial request.

Thanks
 
With Power Query, up to and over 1.6 million rows is not an issue. You will find that with large data sets, PQ is faster than VBA.
Case sensitivity is an issue for PQ. If your data is mixed, then it will require a step in the Mcode to make all data to be searched to be either Capitalized or not, but it will need to be consistent.

I believe that the solution provided with some tweaks as explained above will still serve your needs. However, this is your workbook and you need to be happy. So if VBA is your way, then you will need to wait for a VBA Guru to come along. Meanwhile these links may help you to understand the future of Excel. Good Luck with your project.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.
 
Dear Alan,

Can i get the output as like requested in my InputSearch Sheet.
Currently your test result shows the result in one of the column, which will be difficult for us view, instead it should appear in different sheet as like showed in INPUT search sheet.

Moreover how to implement this query into my big file at my work.
 
Have made the change. Pay attention to the note on my solution page which discusses Case Sensitivity. instructions for aligning with your actual file are in my post nr. 4. Read the last line.
 

Attachments

  • Column Search with available Data.xlsx
    25.1 KB · Views: 4
Dear Alan,

Thanks. But data comes only 9B8 and not for any other combination.

I search with Naveed - Not displayed
I search with 74 - Not displayed
I search with 87 - Not displayed
I search with 486 - Not displayed
I search with Care - Not displayed
 
Not sure what happened, but one of the lines had erroneous info. See the attached which I have moderated.
 

Attachments

  • Column Search with available Data.xlsx
    25.2 KB · Views: 5
Thanks. It works only for lookup with Alphabets, alpha numeric.
But when i write to look for numeric. not showing the result.

Also guide me how to apply the power query in my data sheet. From where i can copy the query and paste it in my data sheet of excel.
 
It can be made case insensitive by changing the Added Custom step to:
= Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains(Text.Upper([#"Buyer Account number & Name"]),Text.Upper(Table3))then [#"Buyer Account number & Name"] else null)
 
Last edited:
1)Guide me how to change the last step in Table 3
2)also how to implement this query in my data sheet used in my office
 
You must log in or register to reply here.
Back
Top