fari1
Active Member
- Joined
- May 29, 2011
- Messages
- 362
hi,
i'm struggling to combine two arrays, one finds certain text values and other finds unique values out of the list, i can use these two arrays in two separate columns, but want to combine them to put less burden on my sheet as arrays take a lot of excel resources.
the array to search text strings consists of two parts
one finds the address of the rows
2nd find the cell values for those address
the array to find unique values is
the names such as search_strings,list and UV are named ranges******** type=text/javascript src="http://images.experts-exchange.com/00225/scripts/attachments_91215bb71fe6ac3f8039d78caee71371.js">*********>
i'm struggling to combine two arrays, one finds certain text values and other finds unique values out of the list, i can use these two arrays in two separate columns, but want to combine them to put less burden on my sheet as arrays take a lot of excel resources.
the array to search text strings consists of two parts
one finds the address of the rows
Code:
{=SMALL(IF(ISERROR(SEARCH(<WBR>Search_str<WBR>ings,TRANS<WBR>POSE(list)<WBR>)),"",TRAN<WBR>SPOSE(ROW(<WBR>list)-MIN(<WBR>ROW(list))<WBR>+1)),ROW(l<WBR>ist)-MIN(R<WBR>OW(list))+<WBR>1)}
Code:
{=INDEX(list,SMALL(IF(ISER<WBR>ROR(SEARCH<WBR>(Search_st<WBR>rings,TRAN<WBR>SPOSE(list<WBR>))),"",TRA<WBR>NSPOSE(ROW<WBR>(list)-MIN<WBR>(ROW(list)<WBR>)+1)),ROW(<WBR>list)-MIN(<WBR>ROW(list))<WBR>+1))}
the array to find unique values is
Code:
{=INDEX(UV,MATCH(0,IF(MAX(<WBR>NOT(COUNTI<WBR>F($B$1:B1,<WBR>UV))*(COUN<WBR>TIF(UV,">"<WBR>&UV)+1))=(<WBR>COUNTIF(UV<WBR>,">"&UV)+1<WBR>),0,1),0))<WBR>}