Forum Discussion
Extract information
See the attached sample workbook. The formula is based on How To Extract A Dynamic List From A Data Range Based On A Criteria Without Filters In Excel
See the attached sample workbook. The formula is based on How To Extract A Dynamic List From A Data Range Based On A Criteria Without Filters In Excel
HansVogelaar Amazing, this works. Thank you.
I also tried using the aggregate function to receive something similar. They both work. My only concern is, is there a way I can extend this formula to the end of the sheet so it applies to all cells in column (till the last row 1048576). I'm asking because the data I have exceeds 50,000 rows in a lot of cases so I'd like to make sure the manual click and drag to as many as possible can be simplified.
Thanks for your guidance, it is much appreciated!
- I'm sorry but why don't you make it a table and use the filters or create a pivot table and/or use a slicer?
My file is extremely huge ( sometimes takes the whole 100,000 + rows) and the pivot table does not seem to help me when my data is dynamic and I could have search criteria that is not on the data itself. I'm going to give it another shot though.
Based on the sample workbook:
1) Change the ranges in the formula in cell D1 ($A$2:$A$6 and $B$2:$B$6) as needed. It doesn't matter if they are larger than the data, but I wouldn't extend them to row 1048576.
2) Press F5 to activate the Go To dialog. Enter - for example - D1:D60000 and click OK.
3) Press Ctrl+D to fill the formula down.
- I think my data is too large, this is something I tried and it worked for a few but my excel seems to crash after I enter more than 70000 rows.
Try Matt Tarler's suggestions.
