LinkChart3 Download the file from here see Link Chart 2 see Link Chart 1 Thanks Rajan


I am playing with XY these days and come up with this Multi – Axis line graph, have a look here.

This is how this chart accept the data

 

TableMultiGraph

 

Here is the chart screenshot

MultiAxixGraph

 

you can download the file form here

in case you want to learn you can unlock the file with “j33hqiwy8@12345”

Rajan.

Link Chart 2

Posted: February 16, 2015 in VBA

The last chart i posted here was showing item link to a category and vice versa , This one shows each items links with other items.

LinkChart2

 

download the file from here
u
nlock the file with “j33hqiwy8@12345”


here is another graph i have created using XY to show the items under a category or category belongs to multiple items. see the below table that you can fill 1 in, and chart will update automatically.

Table

Here you can select Category and Items in this table to the left and graphs will highlight the links
Chart

 

 

 

 

 

 

 

 

 

 

 

Download the file from here 

 

Tree Chart

Posted: February 11, 2015 in VBA

Hi Guys,

This chart shows the hierarchy, anyone can use this to build organization chart, family tree etc. i have tried to make it simple, user just need to fill the data in first two columns. and the graph will update automatically. feedback and suggestions are welcome.


Capture

Download the file from here


 

Excel provides SMALL and LARGE formula to get sorted numeric values , but sometime we need our string/text data to be sorted by formula only.

Though, there is an inbuilt sorting feature available in excel, but here I am going to demonstrate a formula.

So, Lets say you have your data list in Range “A1:A10”, Better to create a name instead of using direct reference into the formula. so I have create a name range that is “myList”

=INDEX(MyList,MATCH(SMALL(MMULT(N(MyList>TRANSPOSE(MyList)),ROW(MyList)*0+1),ROW(MyList)),MMULT(N(MyList>TRANSPOSE(MyList)),ROW(MyList)*0+1),0))

 

It might be looking a long formula to some of you,but believe me it is not,  because red part is same. Ok,lets evaluate this.

The base inside this formula is  this ,

=N(MyList>TRANSPOSE(MyList))

this formula compares each value with each value in the list and create a two-dimensional array

 T1

 if you look at the 0 in B2 it mean that 10 in A2 is not greater than 10 in B1, and if you look at 1 in B3 , it means 12 in A2 is greater than B1, and so on. the next step is to sum this array by rows. So we will MMULT here with another array. See the  formula down below to get that second array, this should have the same number of row element as our first array but all the element would be 1

=ROW(MyList)*0+1   = {1;1;1;1;1;1;1;1;1}

and now we have two matrix to multiply , so here we use MMULT() function

=MMULT(N(MyList>TRANSPOSE(MyList)),ROW(MyList)*0+1)

After multiply that two matrix it will give a result like this table. 

t2

Now we just need to sort these numbers, and we will use SMALL() function to do that. So here is the next formula

=SMALL(MMULT(N(MyList>TRANSPOSE(MyList)),ROW(MyList)*0+1),ROW(MyList))

this will return these sorted numbers

={0;1;2;3;4;5;6;7;8}

Next , we need to match the correct index for all these elements from the above list.

=MATCH(SMALL(MMULT(N(MyList>TRANSPOSE(MyList)),ROW(MyList)*0+1),ROW(MyList)),MMULT(N(MyList>TRANSPOSE(MyList)),ROW(MyList)*0+1),0)

this will return these index numbers

={3;1;2;6;7;8;4;5;9}

Now we got the index and we just need to get the Text from our original list

=INDEX(MyList,MATCH(SMALL(MMULT(N(MyList>TRANSPOSE(MyList)),ROW(MyList)*0+1),ROW(MyList)),MMULT(N(MyList>TRANSPOSE(MyList)),ROW(MyList)*0+1),0))


and here is your sorted list

t3

 

Thanks for reading

Rajan verma

7838100659


After a long time I came up with an interesting trick, it might be rarely useful for anyone but it is tricky. And the trick is “How to delete all worksheets except one” without any loops by VBA.

I always love to use array everywhere (I mean where possibleJ), so here is the trick

Worksheets() collection can accept Index array , and we need an expression that will return that index array. We all knows that  “=Row(1:5)” will return {1;2;3;4;5} , so we just need to create a dynamic index array to supply in worksheets() collection.

How to do that?

I use Evaluate very often. Evaluate() can evaluate any string that is an identical expression. So here is my Expression

“=Row(1:” & Worksheets.count-1 &”)”

If we evaluate this expression by Evaluate(“=Row(1:” & Worksheets.count-1 &”)”) it will return a 2D array, but in this case worksheets() accepts  1D array, we to make it 1D we would need to transpose this array, so the next expression is “Application.Transpose(Evaluate(“=Row(1:” & Worksheets.count-1 &”)”))

And finally, put this array in Worksheets().

Worksheets(Application.Transpose(Evaluate(“=Row(1:” & Worksheets.count-1 &”)”))).Delete

So this is a single line code to delete all worksheets except one. It will ask for confirmation to delete the worksheets , you can use Application.DisplayAlert=False  before and make it true after this line.

Thats all.

Thanks For reading.

Rajan verma