New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

please help with plot data

Macros, VBA, Excel Automation, etc.

please help with plot data

Postby bmark0225 » Fri Nov 01, 2013 2:10 pm

Hi all,
Please help me out this problem
Anyway we can creat Macro to plot data with color in range we want to set,
Attached is sample,
I have 3 collum X, Y, Z,
I want to map with color example:
Whatever number in Z less than 0 then color is red
Whatever number from 1 to 5 then color is blue,
the rest are black,
Thanks so much if three is any help
  • 0

You do not have the required permissions to view the files attached to this post.
bmark0225
Rookie
 
Posts: 6
Joined: Nov 1, 2013
Reputation: 0
Excel Version: 2010

Re: please help with plot data

Postby bmark0225 » Sun Nov 03, 2013 9:02 pm

please help,
i appreciate it
mark
  • 0

bmark0225
Rookie
 
Posts: 6
Joined: Nov 1, 2013
Reputation: 0
Excel Version: 2010

Re: please help with plot data

Postby Sisyphus » Tue Nov 05, 2013 3:28 am

Use conditional formatting.
Select F2 -> Conditional Formatting -> Manage Rules -> Use a formula to determine which cells to format.
Enter this formula, =AND(F2>=1,F2<=5) and set the format to Blue
Now add another rule to the same cell.
This time enter this formula =F2<0 and choose the color to be red.
Now, in the dialog box where you see both rules you see in the Applies To column =$F$2.
Change this reference to =$F:$F for both items so as to apply the format to all values in column F.
I hope this is what you wanted.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: please help with plot data

Postby bmark0225 » Thu Nov 07, 2013 11:35 am

Thank you so much for your help
Actually, i need the color on scatter map not on F collumn,
Again, I appreciate your help,
Mark
  • 0

bmark0225
Rookie
 
Posts: 6
Joined: Nov 1, 2013
Reputation: 0
Excel Version: 2010

Re: please help with plot data

Postby Sisyphus » Thu Nov 07, 2013 10:49 pm

That's precisely what I was afraid you would say. I haven't the slightest idea. But now that you have roped me in give me a boot-up. Please describe, step by step, how you create the plot map in your file. Then tell me what you wish to color differently. You see, I can't identify the Z values in your map. So, if you teach me what you know I might be able to apply my knowledge to do the modifications you wish for.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: please help with plot data

Postby bmark0225 » Fri Nov 08, 2013 4:59 pm

Hi Sisyphus,
Every time I do it by manual:
Go to insert chart -> Scatter ->series and on F column I have to sort from smallest to highest , then count how many row that less than 0 on F column,
all is only manual and easy mess up everything,
You can see on my attached file,
Thanks so much for your help,
Mark
  • 0

You do not have the required permissions to view the files attached to this post.
bmark0225
Rookie
 
Posts: 6
Joined: Nov 1, 2013
Reputation: 0
Excel Version: 2010

Re: please help with plot data

Postby bmark0225 » Sat Nov 09, 2013 11:11 am

Hi Sisyphus
I appreciate your help, but seem like my previous post was not clear
i try to post it again, please help,
As you see in the sheet4 ( original data)
Column A is ID 1,2,3...
Column B is X axis
Column C is Y axis
Column D is Z axis
What i try to do is making a map with X and Y, then in the map whatever X,Y has Z value in the different range value has different color
What i am doing now ( on sheet1) is i have to sort column Z, then manually going to insert chart -> scatter->series ,and manually typing each series on, lot of work on that and easily make mistake ,
Anyway such as macro,any code that make it better,
Again, thank you so much for your help,
Mark
  • 0

You do not have the required permissions to view the files attached to this post.
bmark0225
Rookie
 
Posts: 6
Joined: Nov 1, 2013
Reputation: 0
Excel Version: 2010

Re: please help with plot data

Postby Sisyphus » Mon Nov 11, 2013 5:31 pm

Hi,
I'm afraid I will need much more help.
  1. Data
    You say that Sheet4 has original data. These are in columns A:D.
    I have renamed this sheet as "Original"
  2. I have renamed your Sheet1 as "Desired Result"
    Is that correct?
    Why are the data in columns C:F?
  3. There are no headers in both sheets.
    In your original post you had column captions (headers).
    Please note that for any coded solution it is essential to know where the data are. The columns should be the real columns, the rows should be the real rows, and the captions should be the real captions.
  4. Starting from the original data you say that you sort the Z column (D or F).
    It seems to me that at least column A (or D) is also sorted. What about the other columns? Do they all need to be sorted?
  5. In your example you have a Plot Area within the Chart Area
    This result also matches your description where you say that you create a map using X and Y and then insert Z into it.I need the steps to do this.
  6. You do not mention, so far, how you use column A (or C), but I see the data from that column in your chart. How do they get there?
You see, in a coded solution the first step is to create the "object". Then, in the next step, parts of that object will be manipulated. I realize that sorting the columns (and perhaps moving them) and creating the basic chart isn't your big problem. Well, once I know how to do it manually it will also not be a big problem to encode the process. Visualize a sheet with your actual data, as they come in. Then run a macro and your chart is ready. By following this sequence we will end up with a chart object that can be manipulated using code for every step you now do by hand.
I know you are actually supplying the chart object to me, physically. However, since I am not familiar with the topic I don't know what kind of object, or nested object, it is in VBA parlance. The best way to ensure clarity is to create the object by my own code. So, that is where we have to start, and that is why I ask all the above questions.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: please help with plot data

Postby bmark0225 » Mon Nov 11, 2013 8:45 pm

Hi Sisyphus,
On original sheet, that is the data i have:
On the column A is just ID # , I don't use it at all
Let assume that on column B and C data do not follow any sort and I base on column B and C to plot scatter area map,and points on the map has different color depends on value on column D,
With that mean, i am looking for code that when i import the data on original sheet then the map with color on sheet 1 will automatic show up ( It is ok to show the map on same sheet or different sheet)
The attached file that i added on Saturday reply is just want to show you that step by step ( manually) i am doing right now ,
Thank you so much for your time and best regards,
Mark
  • 0

bmark0225
Rookie
 
Posts: 6
Joined: Nov 1, 2013
Reputation: 0
Excel Version: 2010

Re: please help with plot data

Postby Sisyphus » Mon Nov 11, 2013 9:04 pm

Hi,
I regret to inform you that I have failed to generate the map using the steps you have listed. As previously explained, coloring of the scatters can only be discussed after the scatters exist. Since I can't generate the map I can't discuss anything about it.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010


Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google [Bot] and 47 guests