Hi All,
Had to know if the frequency array function can be applied for a text which we can by using the following formula "=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))" which gives the unique occurrences of text in column A.
Actually i need to know how to get the unique occurrences of text in a particular column for ex - col B for a value in column A. For ex please see below.
Column A Column B
[TABLE="width: 268"]
<TBODY>[TR]
[TD]Location</SPAN>[/TD]
[TD]Responsible Party</SPAN>[/TD]
[/TR]
[TR]
[TD]New York</SPAN>[/TD]
[TD]Alexander</SPAN>[/TD]
[/TR]
[TR]
[TD]New York</SPAN>[/TD]
[TD]Igor</SPAN>[/TD]
[/TR]
[TR]
[TD]New York</SPAN>[/TD]
[TD]Alexander</SPAN>[/TD]
[/TR]
[TR]
[TD]PittsBurg</SPAN>[/TD]
[TD]Ram</SPAN>[/TD]
[/TR]
[TR]
[TD]PittsBurg</SPAN>[/TD]
[TD]Ram</SPAN>[/TD]
[/TR]
[TR]
[TD]PittsBurg</SPAN>[/TD]
[TD]Ram</SPAN>[/TD]
[/TR]
[TR]
[TD]PittsBurg</SPAN>[/TD]
[TD]Ram</SPAN>[/TD]
[/TR]
[TR]
[TD]PittsBurg</SPAN>[/TD]
[TD]Ram</SPAN>[/TD]
[/TR]
[TR]
[TD]PittsBurg</SPAN>[/TD]
[TD]Brian</SPAN>[/TD]
[/TR]
[TR]
[TD]PittsBurg</SPAN>[/TD]
[TD]Brian</SPAN>[/TD]
[/TR]
[TR]
[TD]PittsBurg</SPAN>[/TD]
[TD]Sean</SPAN>[/TD]
[/TR]
[TR]
[TD]Shanghai</SPAN>[/TD]
[TD]Park</SPAN>[/TD]
[/TR]
[TR]
[TD]Shanghai</SPAN>[/TD]
[TD]Park</SPAN>[/TD]
[/TR]
[TR]
[TD]Shanghai</SPAN>[/TD]
[TD]Park</SPAN>[/TD]
[/TR]
[TR]
[TD]Shanghai</SPAN>[/TD]
[TD]Candy</SPAN>[/TD]
[/TR]
[TR]
[TD]Shanghai</SPAN>[/TD]
[TD]Candy</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]
Here I want to get the Unique text occurrences in column B with out repetitions against text values in column A
Like
New York - 2
PittsBurg - 3
Shanghai - 2
Need the number of unique occurrences of text on col B for the particular value in col A as above.
Can someone help in modifying the formula i have given or a new set of array function where we can give the referrence to the needed value in column A and then get the unique text occurrences in column B for that referenced value in Col A .
Your help is appreciated.
Thanks
Divakar</SPAN></SPAN>
Had to know if the frequency array function can be applied for a text which we can by using the following formula "=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))" which gives the unique occurrences of text in column A.
Actually i need to know how to get the unique occurrences of text in a particular column for ex - col B for a value in column A. For ex please see below.
Column A Column B
[TABLE="width: 268"]
<TBODY>[TR]
[TD]Location</SPAN>[/TD]
[TD]Responsible Party</SPAN>[/TD]
[/TR]
[TR]
[TD]New York</SPAN>[/TD]
[TD]Alexander</SPAN>[/TD]
[/TR]
[TR]
[TD]New York</SPAN>[/TD]
[TD]Igor</SPAN>[/TD]
[/TR]
[TR]
[TD]New York</SPAN>[/TD]
[TD]Alexander</SPAN>[/TD]
[/TR]
[TR]
[TD]PittsBurg</SPAN>[/TD]
[TD]Ram</SPAN>[/TD]
[/TR]
[TR]
[TD]PittsBurg</SPAN>[/TD]
[TD]Ram</SPAN>[/TD]
[/TR]
[TR]
[TD]PittsBurg</SPAN>[/TD]
[TD]Ram</SPAN>[/TD]
[/TR]
[TR]
[TD]PittsBurg</SPAN>[/TD]
[TD]Ram</SPAN>[/TD]
[/TR]
[TR]
[TD]PittsBurg</SPAN>[/TD]
[TD]Ram</SPAN>[/TD]
[/TR]
[TR]
[TD]PittsBurg</SPAN>[/TD]
[TD]Brian</SPAN>[/TD]
[/TR]
[TR]
[TD]PittsBurg</SPAN>[/TD]
[TD]Brian</SPAN>[/TD]
[/TR]
[TR]
[TD]PittsBurg</SPAN>[/TD]
[TD]Sean</SPAN>[/TD]
[/TR]
[TR]
[TD]Shanghai</SPAN>[/TD]
[TD]Park</SPAN>[/TD]
[/TR]
[TR]
[TD]Shanghai</SPAN>[/TD]
[TD]Park</SPAN>[/TD]
[/TR]
[TR]
[TD]Shanghai</SPAN>[/TD]
[TD]Park</SPAN>[/TD]
[/TR]
[TR]
[TD]Shanghai</SPAN>[/TD]
[TD]Candy</SPAN>[/TD]
[/TR]
[TR]
[TD]Shanghai</SPAN>[/TD]
[TD]Candy</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]
Here I want to get the Unique text occurrences in column B with out repetitions against text values in column A
Like
New York - 2
PittsBurg - 3
Shanghai - 2
Need the number of unique occurrences of text on col B for the particular value in col A as above.
Can someone help in modifying the formula i have given or a new set of array function where we can give the referrence to the needed value in column A and then get the unique text occurrences in column B for that referenced value in Col A .
Your help is appreciated.
Thanks
Divakar</SPAN></SPAN>