• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using the Frequency function to find unique items between ranges

M

mike szczesny

New Member
How can the frequency function be used for two dimensional ranges and non-contiguous (separated) ranges. I am in interested in non VBA answers.

Thanks,
Mike Szczesny
 
Is this of any help


When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter when completing the formula. This creates {} brackets around your formula. This is very important to remember. If you do not have {} brackets around your formula, it is NOT being interpretted as an array formula by Excel.

frequency002.gif


Based on the spreadsheet above, the following Excel formula {=FREQUENCY(B2:B12,D2:D5)} into cells E2:E6 using Ctrl+Shift+Enter to complete the formula. This would return a vertical array with 5 values as follows:

The first value in the array would display in cell E2. The result would be 2 (because there are 2 Test Scores <= 59).

The second value in the array would display in cell E3. The result would be 1 (because there is 1 Test Score between 60 and 69).

The third value in the array would display in cell E4. The result would be 2 (because there are 2 Test Scores between 70 and 79).

The fourth value in the array would display in cell E5. The result would be 3 (because there are 3 Test Scores between 80 and 89).

The fifth value in the array would display in cell E6. The result would be 3 (because there are 3 Test Scores > 89). This catches all values greater than the last value in the interval.



.
 
Hi bobhc,

Thanks for your response. I am sorry that I was not able to more clearly state my problem. I am not really interested with intervals with the frequency function. I would be more concerned with finding the unique item in a multi-demensional range such as A1: J5.

I know how to find the sum and each unique items in a single column range. For capturing unique items, I have been using =index(range,small(if(frequency(if(range<>"",match(range,range,0)),row(range)-row(range 1st cell)+1),row(range)-(row(range 1st cell),rows($r$c:rc))).
For the sum I would use =sum(if(frequen(..........,1)

How would go about doing the same with range a1:J5? This is thrust of my question. I hope this to illustrate what I am asking about.

Mike S.




bobhc said:
Is this of any help


When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter when completing the formula. This creates {} brackets around your formula. This is very important to remember. If you do not have {} brackets around your formula, it is NOT being interpretted as an array formula by Excel.

frequency002.gif


Based on the spreadsheet above, the following Excel formula {=FREQUENCY(B2:B12,D2:D5)} into cells E2:E6 using Ctrl+Shift+Enter to complete the formula. This would return a vertical array with 5 values as follows:

The first value in the array would display in cell E2. The result would be 2 (because there are 2 Test Scores <= 59).

The second value in the array would display in cell E3. The result would be 1 (because there is 1 Test Score between 60 and 69).

The third value in the array would display in cell E4. The result would be 2 (because there are 2 Test Scores between 70 and 79).

The fourth value in the array would display in cell E5. The result would be 3 (because there are 3 Test Scores between 80 and 89).

The fifth value in the array would display in cell E6. The result would be 3 (because there are 3 Test Scores > 89). This catches all values greater than the last value in the interval.



.
Click to expand...
 
NARAYANK991 said:
Hi Mike ,

You might find this helpful :

http://excelxor.com/2014/11/08/unique-alphabetical-list-from-several-columns/

Narayan
Click to expand...
Hi Narayan,

Yes. This would be very helpful for me. Perhaps you can help me further. I have a formula that list items from a non-single row (column) range. However, how would I use it to find only unique items? In other words, I am not sure how I would incorporate the frequency( and transpose parts into this formula. Assume the range has duplicate items. This formula lists the items column, then next column, etc. Here is the formula

=IFERROR(INDEX($A$1:$E$5,INT(SMALL(IF($A$1:$E$5<>"",(ROW($A$1:$E$5)-ROW($A$1)+1)*10^9+COLUMN($A$1:$E$5)+COLUMN($A$1)+1),ROWS($A$8:A8))/10^9),MOD(SMALL(IF($A$1:$E$5<>"",(ROW($A$1:$E$5)-ROW($A$1)+1)*10^9+COLUMN($A$1:$E$5)-COLUMN($A$1)+1),ROWS($A$8:A8)),10^9)),"")

I am having a difficult time in understanding how I would need to insert the frequency and transpose portions. Would this be possible?

Thanks,
Mike S.
 
Hi Mike ,

I doubt that I will be able to help you out ; if you can upload a sample workbook with input data , and the output that you are looking for , it might be better.

Narayan
 
@mike szczesny

Perhaps you might consider informing members of this forum that you have already posted this question on at least one other forum?

I understand that, in doing so, you improve your chances of receiving an answer to your query. However, I'm sure you can also appreciate that "cross-posting" can be a little frustrating to some: not everyone takes well to the idea of dedicating (sometimes considerable) time and effort towards a solution, only to find that one has already been provided elsewhere.

Regards
 
NARAYANK991 said:
Hi Mike ,

I doubt that I will be able to help you out ; if you can upload a sample workbook with input data , and the output that you are looking for , it might be better.

Narayan
Click to expand...
Hi Narayan,

Here is my example. I am having some difficulting adapting this to select only for unique items
XOR LX said:
@mike szczesny

Perhaps you might consider informing members of this forum that you have already posted this question on at least one other forum?

I understand that, in doing so, you improve your chances of receiving an answer to your query. However, I'm sure you can also appreciate that "cross-posting" can be a little frustrating to some: not everyone takes well to the idea of dedicating (sometimes considerable) time and effort towards a solution, only to find that one has already been provided elsewhere.

Regards
Click to expand...
To XOR LX:

To be honest, I wouldn't have know that this would have been an issue. Thanks for letting me know about this in the future.
 
NARAYANK991 said:
Hi Mike ,

I doubt that I will be able to help you out ; if you can upload a sample workbook with input data , and the output that you are looking for , it might be better.

Narayan
Click to expand...

Hi Naryan,

I am difficulty adapting this formula to select unique items. I cannot figure out how to the frequency and transpose functions in this formula. I am not even sure if it is even feasible for such a formula. Here is that sample data. Please refer to the attached.

Thanks,
Mike S.
 

Attachments

  • example.xlsx
    8.9 KB · Views: 2
You must log in or register to reply here.
Back
Top