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:
[COLOR=#00FF00]IFERROR(INDEX(SOC_Demarc_Report!$C$2:$I$10,MATCH(1,(NTOC_VOICE_ALL!$C$2:$C$10=SOC_Demarc_Report!$C2)*(NTOC_VOICE_ALL!$D$2:$D$10=SOC_Demarc_Report!$D2),0),4),"No Result") [/COLOR]
DISPLAY:
[COLOR=#FF0000]INDEX(Compare!$A$2:$A$10,SMALL(IF(NOT($A$1=Compare!$A$2:$A$10),ROW(Compare!$A$2:$A$10)-1,""),ROW()-2))[/COLOR]
I have tried this to no avail (which Excel accepts):
[COLOR=#00FF00]IFERROR(INDEX(SOC_Demarc_Report!$C$2:$I$10,MATCH(1,(NTOC_VOICE_ALL!$C$2:$C$10=SOC_Demarc_Report!$C2)*(NTOC_VOICE_ALL!$D$2:$D$10=SOC_Demarc_Report!$D2),0),4),"No Result")[/COLOR]*[COLOR=#FF0000](INDEX(Compare!$A$2:$A$10,SMALL(IF(NOT($A$1=Compare!$A$2:$A$10),ROW(Compare!$A$2:$A$10)-1,""),ROW()-2)))[/COLOR]
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:
[COLOR=#00FF00]IFERROR(INDEX(SOC_Demarc_Report!$C$2:$I$10,MATCH(1,(NTOC_VOICE_ALL!$C$2:$C$10=SOC_Demarc_Report!$C2)*(NTOC_VOICE_ALL!$D$2:$D$10=SOC_Demarc_Report!$D2),0),4),"No Result") [/COLOR]
DISPLAY:
[COLOR=#FF0000]INDEX(Compare!$A$2:$A$10,SMALL(IF(NOT($A$1=Compare!$A$2:$A$10),ROW(Compare!$A$2:$A$10)-1,""),ROW()-2))[/COLOR]
I have tried this to no avail (which Excel accepts):
[COLOR=#00FF00]IFERROR(INDEX(SOC_Demarc_Report!$C$2:$I$10,MATCH(1,(NTOC_VOICE_ALL!$C$2:$C$10=SOC_Demarc_Report!$C2)*(NTOC_VOICE_ALL!$D$2:$D$10=SOC_Demarc_Report!$D2),0),4),"No Result")[/COLOR]*[COLOR=#FF0000](INDEX(Compare!$A$2:$A$10,SMALL(IF(NOT($A$1=Compare!$A$2:$A$10),ROW(Compare!$A$2:$A$10)-1,""),ROW()-2)))[/COLOR]
