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

"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.

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.

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

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

"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