Combining INDEX formulas

[robhein]

I have a workbook where I am importing data from 2 external reports (CSV to Excel format), and I have to find matches between both reports to determine matching criteria. Once I have this match completed I am pulling the results into another worksheet (all same workbook), for displaying in a line by line order. This is needed since I am working with up to 100000 lines of data. All of this is inside the same workbook and of course is a bit more time consuming for calculations but is acceptable. Both formulas work correctly but I would like to combine the 2 formulas into one and have final results displayed on a single worksheet, rather than have the one worksheet get results of matching data, and then the other worksheet display the results in specific order. I do this since I end up with (for example) 1000 matching results scattered throughout the MATCH worksheet (cells C2:I100000) with the matches, and need to provide the results in an ordered fashion on the RESULTS worksheet.

Rather than try to make this work using my total data and results, I have shortened my amount of data for now, for ease of getting it to work as a combined single formula on a single worksheet.

Below are the 2 formulas for the 2 worksheets performing the match and displaying the results:

MATCH:

Please Login or Register  to view this content.

DISPLAY:

Please Login or Register  to view this content.

I have tried this to no avail (which Excel accepts):

Please Login or Register  to view this content.

[dflak]

If you attach a sample workbook, we might be able to help you better

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[robhein]

If you attach a sample workbook, we might be able to help you better

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I uploaded file to work with. The results for both worksheets formulas work perfect. I only want to combine/merge or whatever I can to streamline the calculations or speed them up. What ever might be best scenario for managing data.

Thanks

[AliGW]

Links to cross-posts now provided - thank you.

[robhein]

Links to cross posted forum question:

http://www.mrexcel.com/forum/excel-q...-formulas.html

http://www.excelguru.ca/forums/showt...INDEX-formulas

[AliGW]

Thank you foe the links. You may now receive help in this thread.

You might also want to refresh your memory by re-reading the forum rules: http://www.excelforum.com/forum-rule...rum-rules.html

[dflak]

I'm sending in my punter - this has an aggregate of an array of aggregates - it's beyond simple recursive substitution until you have everything defined in terms of the original parameters. There is probably an array formula that can do it, but my mind isn't up to that many levels of abstraction now.

Good luck to you.

[robhein]

I'm sending in my punter - this has an aggregate of an array of aggregates - it's beyond simple recursive substitution until you have everything defined in terms of the original parameters. There is probably an array formula that can do it, but my mind isn't up to that many levels of abstraction now.

Good luck to you.

Thank you for your reply, time and honesty. I know this is a little abstract and I can run with it like it is, but I can't help but think there is a better way.