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
<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>
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
Location</SPAN> | Responsible Party</SPAN> |
New York</SPAN> | Alexander</SPAN> |
New York</SPAN> | Igor</SPAN> |
New York</SPAN> | Alexander</SPAN> |
PittsBurg</SPAN> | Ram</SPAN> |
PittsBurg</SPAN> | Ram</SPAN> |
PittsBurg</SPAN> | Ram</SPAN> |
PittsBurg</SPAN> | Ram</SPAN> |
PittsBurg</SPAN> | Ram</SPAN> |
PittsBurg</SPAN> | Brian</SPAN> |
PittsBurg</SPAN> | Brian</SPAN> |
PittsBurg</SPAN> | Sean</SPAN> |
Shanghai</SPAN> | Park</SPAN> |
Shanghai</SPAN> | Park</SPAN> |
Shanghai</SPAN> | Park</SPAN> |
Shanghai</SPAN> | Candy</SPAN> |
Shanghai</SPAN> | Candy</SPAN> |
<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>
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>