Sort bars in a graph

  • Thread starter Thread starter mahadevan.swamy
  • Start date Start date
M

mahadevan.swamy

Hi,

I have a bar graph in which the numbers are not sorted and are looking
very random. What i want is to sort the bars in descending order IN
the graph. But I don't want to sort the linked data as there are many
and I only want to pick the top 10 issues with the highest numbers.
Even though i copy and paste the data separately (pasted linked data
only), the data is not getting sorted. Does anyone have a solution to
sort the bars in the graph without touching the data. Thanks

Mahadevan
 
Try the Large function

make a new column that looks like this

=large(a1:A100,1)
=large(a1:A100,2)
=large(a1:A100,3)
=large(a1:A100,4)
=large(a1:A100,5)
=large(a1:A100,6)
=large(a1:A100,7)
=large(a1:A100,8)
=large(a1:A100,9)
=large(a1:A100,10)

This will give you the top ten numbers in an array. Then plot the results
of the large functions.
 
Hi,

Thank you for your help. I have two columns: Defect Code and the
number of defect codes. I would like to pick the top 10 issues based
on the highest number of defect codes in correspondence to Defect
Code. I have the following in my excel sheet: -

DefCODE DEF

43OGFH 1
51GLKG 1
51PKGD 1
51TQBB 1
16EXBR -
16EXSP -
16KEBR -
16MSBK -
16SMSP -
16WBID -
16WRBT -
16XSPR -
37APLN -
37BHLN -

I tried the large function but it is giving me a number which doesn't
make sense. The possibility is that there can be numbers which can be
repeated and I want this to be sorted. I would appreciate if you have
any solution for this. Thanks

Swamy
 
The large() function doesn't seem to work right. I wrote a function largea()
which works properly.

Function LargeA(MyCells As Range, MyRank As Integer) As Integer

Dim MyData() As Variant

ReDim MyData(MyCells.Count)

'get data
DataCount = 1
For Each MyCell In MyCells

MyData(DataCount) = MyCell
DataCount = DataCount + 1

Next MyCell


For i = 1 To MyRank
For j = (i + 1) To (MyCells.Count)

If (MyData(j) > MyData(i)) Then

Temp = MyData(j)
MyData(j) = MyData(i)
MyData(i) = Temp
End If

Next j
Next i

LargeA = MyData(MyRank)

End Function
 
Make a pivot table based on your data. Put DefCODE in the rows area and DEF
in the data area. Right-click on the DefCODE field button, click Advanced,
Autosort descending by Sum of DEF field, then on the same dialog, turn Top
10 Autoshow ON, and keep the default Top 10, using Sum of DEF.

Plot this data in a pivot chart or regular chart. For a regular chart, start
by selecting a blank cell a few columns or rows removed from the pivot table
and run the chart wizard, then in step 2, go to the Series tab, and select
the individual ranges for the series. If you start with a cell in the pivot
table, or if you use the Data Range tab, the chart becomes a pivot chart and
you have to cancel the wizard and start over.

- Jon
 
Back
Top