Reverse Frequency
 

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

Reverse Frequency
 

"smandula" skrev i en meddelelse
ups.com...
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$2:D2, $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)+2COUNTIF($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.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Reverse Frequency
 

Hello Leo

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

Could you help?
With Thanks



In E4 this array formula:

2COUNTIF($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.

Reverse Frequency
 

In E4 this array formula:

2COUNTIF($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" skrev i en meddelelse
ups.com...
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

Reverse Frequency
 

I copied this formula in E4

In E4 this array formula:

=IF(ROW()-ROW($E$4)+2COUNTIF($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

Reverse Frequency
 

"smandula" skrev i en meddelelse
ups.com...

I copied this formula in E4

In E4 this array formula:

=IF(ROW()-ROW($E$4)+2COUNTIF($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 <Ctrlv 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

Reverse Frequency
 

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

Reverse Frequency
 

"smandula" skrev i en meddelelse
oups.com...

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