Originally Posted by
belinda200
And here s my solution:
In A2 and down:
=INDEX(Sheet2!$B$1:$B$120,AGGREGATE(15,6,ROW(Sheet2!$B$2:$B$120)/(MATCH(Sheet2!$B$2:$B$120,Sheet2!$B$2:$B$120,0)=ROW(Sheet2!$B$1:$B$120)),ROWS(Sheet1!$K$1:K1)))
Hi belinda200
Thank you for such a quick reply and your formula works perfectly.
Rather than just asking for help I am trying to learn from the help that people kindly give and I have been trying to dissect your formula to see how it works so may I ask what the purpose is for
? I only ask as I am curious because in my example there is no data in that column but in the final worksheet there will be.
The final workbook Sheet 2 will have anywhere between 4000 – 7000 rows of data which will be updated by a Macro (importing raw data from a delimited text file) and I have been able to change your cell references from
to
which appear to work fine.
During the text file import/update the VBA macro also inserts all of the formulas and to stop the whole workbook growing to a silly size (MB wise) I always wrap formulas in IF statements like
to only insert the formula where associated data exists. I have also wrapped an IFERROR statement around your formula as it was returning #NUM! on cells with no data in Sheet 2 which worked fine until I wrapped the if statement around the whole code and that caused the #NUM! error to return.
Is there a better way (or should I say the correct way) for me to use the IF data exists and IFERROR with your formula?
Your formula with the IFERROR statement
And with the IF (data exists) statement
Bookmarks