Excel -- automated "find" formula
Experts:
Per attached XLS, I have a cell range (A1:B300) which has 9 paired values as part of the 300 records. Thus, there are 291 rows which currently don't have values.
I would like to create a formula that "screens" through the 300 rows and then (in column D and E) adds the 9 paired values into cell range D1:E9. In the attachment, this process was done manually; however, as additional values may be added/removed from cell range A1:B300, I would like to automate the screening/finding process in cell range D:E.
A picture is worth a thousand words... so, please see the attached XLS for additional details.
Thank you,
EEH
Automated-Find.xlsx
Per attached XLS, I have a cell range (A1:B300) which has 9 paired values as part of the 300 records. Thus, there are 291 rows which currently don't have values.
I would like to create a formula that "screens" through the 300 rows and then (in column D and E) adds the 9 paired values into cell range D1:E9. In the attachment, this process was done manually; however, as additional values may be added/removed from cell range A1:B300, I would like to automate the screening/finding process in cell range D:E.
A picture is worth a thousand words... so, please see the attached XLS for additional details.
Thank you,
EEH
Automated-Find.xlsx
ASKER
Saurabh:
Thank you... a pivot table is NOT going to work for me. As indicated, I would like to get a formula/function that allows me to achieve the results.
Thank you,
EEH
Thank you... a pivot table is NOT going to work for me. As indicated, I would like to get a formula/function that allows me to achieve the results.
Thank you,
EEH
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
please find attached.
it is with formula and it is dynamic, automatically.
if you want eliminate duplicate then my formula works becuase it ignores duplicates and extracts non blank unique IDs. if you want duplicates then Rgonzo's formula works for you to include duplicates.
Automated-Find.xlsx
it is with formula and it is dynamic, automatically.
if you want eliminate duplicate then my formula works becuase it ignores duplicates and extracts non blank unique IDs. if you want duplicates then Rgonzo's formula works for you to include duplicates.
Automated-Find.xlsx
Try this Array Formula which requires confirmation with Ctrl + Shift + Enter instead of Enter alone.
In D1
I have applied the formula in the column G and H, please refer to the attached.
**************************
Remember that an Array Formula is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Automated-Find.xlsx
In D1
=IFERROR(INDEX(A$1:A$300,SMALL(IF($A$1:$A$300<>"",ROW($A$1:$A$300)-ROW($A$1)+1),ROWS(D$1:D1))),"")I have applied the formula in the column G and H, please refer to the attached.
**************************
Remember that an Array Formula is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Automated-Find.xlsx
:-) Sktneer
that is what i like about EE. friendly competitions that results on different ways of getting things done.
that is what i like about EE. friendly competitions that results on different ways of getting things done.
ASKER
Rgonzo1971 -- your solution is most BRILLIANT!!! (It even fixed where I made a sorting error in column E for 3.3.4). Again, this works absolutely fantastic.
Thank you!
EEH
Thank you!
EEH
ASKER
All:
Thank you for your great contributions... all of them work good... some of them were better.
Based on the first-come, first serve basis, points were awarded to expert Rgonzo1971.
Again, I thank all of your for contributing to this post.
EEH
Thank you for your great contributions... all of them work good... some of them were better.
Based on the first-come, first serve basis, points were awarded to expert Rgonzo1971.
Again, I thank all of your for contributing to this post.
EEH
Enclosed is the sample file for your reference...
Saurabh...
Automated-Find.xlsx