Reverse Frequency

[smandula]

Need to list the individual frquencies for each Number
i.e.
Number Frequency
1 10
2 7
3 11
4 5
5 3
6 6
7 8
8 9
9 9
10 9
11 7
12 10
13 5
14 5
To Look like the following.

Frequency
3 5 7 8 9 10
------------------------------------------
5 4 2 7 8 1
13 11 9 12
14 10

A reverse frequency with individual lookup in ascending order.

Any suggestions would be appreciated.

With thanks

 

[Leo Heuser]

Need to list the individual frquencies for each Number
i.e.
Number Frequency
1 10
2 7
3 11
4 5
5 3
6 6
7 8
8 9
9 9
10 9
11 7
12 10
13 5
14 5
To Look like the following.

Frequency
3 5 7 8 9 10
------------------------------------------
5 4 2 7 8 1
13 11 9 12
14 10

A reverse frequency with individual lookup in ascending order.

Any suggestions would be appreciated.

With thanks

Assuming your data in A2:B25, here´s a formula solution.

Array formulae must be concluded with <Shift><Ctrl><Enter>, also
if you edit them later. If done correctly, the formula in the formula
bar will be enclosed in braces { }. Don't enter these braces yourself.

In E2 enter this array formula (D2 must be present and empty (or
at least must not contain data present in B2:B25)):

=MIN(IF((COUNTIF($D$22, $B$2:$B$25)=0)*($B$2:$B$25<>""),$B$2:$B$25))

Copy E2 to the right with the fill handle (the little square in the lower
right corner of the cell) until zeros are displayed (e.g. in L2).


In E3 this formula (not an array formula):

=INDEX($A$2:$A$25,MATCH(E$2,$B$2:$B$25,0))

Copy E3 to F3:L3.


In E4 this array formula:

=IF(ROW()-ROW($E$4)+2>COUNTIF($B$2:$B$25,E$2),"-",
INDEX($A$2:$A$25,MIN(IF(($B$2:$B$25=E$2)*(COUNTIF($E$3:E3,$A$2:$A$25)=0),ROW($B$2:$B$25)-ROW($B$2)+1))))

Copy E4 to F4:L4
While E4:L4 is selected, copy the selection down as far as necessary.

 

[smandula]

Hello Leo

I can't get array formula to work?
error in "=IF(ROW()-ROW($E$4)+"

Could you help?
With Thanks

 

[Leo Heuser]

In E4 this array formula:

Hello Leo

I can't get array formula to work?
error in "=IF(ROW()-ROW($E$4)+"

Could you help?
With Thanks

What do you mean by "I can't get array formula to work"?
What happens?

In which cell did you enter the formula, I entered in E2?
In which cell did you enter the formula, I entered in E3?
In which cell did you enter the formula, I entered in E4?

Leo Heuser

 

[smandula]

I copied this formula in E4

In E4 this array formula:

=IF(ROW()-ROW($E$4)+2>COUNTIF($B$2:$B$25,E$2),"-",
INDEX($A$2:$A$25,MIN(IF(($B$2:$B$25=E$2)*(COUNTIF($E$3:E3,$A$2:$A$25)=0),ROW($B$2:$B$25)-ROW($B$2)+1))))


Problem seems to be in this part of the formula
IF(ROW()-ROW($E$4)+ - - - - - - - - -

With Thanks

 

[Leo Heuser]

I copied this formula in E4

In E4 this array formula:

=IF(ROW()-ROW($E$4)+2>COUNTIF($B$2:$B$25,E$2),"-",
INDEX($A$2:$A$25,MIN(IF(($B$2:$B$25=E$2)*(COUNTIF($E$3:E3,$A$2:$A$25)=0),ROW($B$2:$B$25)-ROW($B$2)+1))))


Problem seems to be in this part of the formula
IF(ROW()-ROW($E$4)+ - - - - - - - - -

With Thanks

The formula has probably been cut in two, when you copied it.

1. Copy the formula from my post.
2. Select E4 and press <Delete>
3. Click in the formula bar (which must be empty)
4. Press <Ctrl>v to copy the formula to the bar.
5. Go to the upper line.
6. Use <End> to get to the end of the line.
7. Press <Delete> to connect first and second line.
8. Go to the end of the second line.
9. Press <Delete> to get rid of the empty third line.
10. Press <Shift><Ctrl><Enter>

It should work now.

Leo Heuser

 

[smandula]

Many Thanks,

This Formula is absolutely brilliant !

Formula editing on my part was the problem.

I sure appreciate your comeback, I would say at first
it was impossible. Great Solution.

With Thanks

 

[Leo Heuser]

Many Thanks,

This Formula is absolutely brilliant !

Formula editing on my part was the problem.

I sure appreciate your comeback, I would say at first
it was impossible. Great Solution.

With Thanks

You're welcome. Thanks for your kind feedback

Leo Heuser