Hi everyone,
I have a question about listing all the rows depending on their specific data or text in a cell.
For example on the attached screenshot, i would like to search column C, search for the first number which is ''4'', and bring the whole row when found out. Im trying to list all the rows containing the same value in this specific column.
And also is it possible to make it more advanced as, first two digits or first 3 letters?
Thank you very much
filter row advanced query.png
When you set filters to on (Data ribbon), you get Text Filter options on the drop-down - these include the Begins with ... option.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks for the reply, i'm aware of this solution, i'm just checking for the formula based solution for singular or multiple digits/texts.
There are instructions at the top of the page explaining how to attach your sample workbook.
Hello
I just created a reference file from a bigger excel sheet.
My aim is basically list all rows from C to AW that starts with specific number/numbers or letter/letters (lets say finding the rows start with number 4 in K column on this example).
I'm using a similar function with Filter isnumber and search when there is specific data in a specific cell in whole row. I couldn't figure out how to filter by the first digits or letters so far.
Thanks for all the help
Last edited by frostinheart; 07-02-2020 at 10:06 AM.
anyone has a solution for this?
As for: "... finding the rows start with number 4 in K column..."
A helper column (B) on Sheet1 is populated using: =LEFT(K2,1)="4"
On sheet2 column C is populated using:Formula:
Please Login or Register to view this content.
Columns D:AV are populated using:Formula:Please Login or Register to view this content.
Note that the helper column may be moved and/or hidden for aesthetic purposes.
Note that you could also filter Sheet1 by choosing TRUE when filtering column B (Sheet1).
Note that the formula that populates column C (Sheet2) may be used to populate columns C:AV however I used the second formula as it will be more efficient, which may be evident depending on the size (number of rows) of the data.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thank you very much. That helped alot!
Last edited by AliGW; 07-06-2020 at 06:19 AM. Reason: Please don't quote unnecessarily!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
You're Welcome and thank you for the feedback. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks