Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


mdf



2035 posts

Uber Geek
+1 received by user: 604

Trusted
Subscriber

Topic # 201609 26-Aug-2016 12:48
Send private message

My google-fu has failed me.

 

I am trying to turn this:

 

 

Into something that looks a bit like this:

 

 

i.e. an item is assigned to "matrix" box and displayed visually depending on it's categorisation in two separate categories (which are in this case literally 1 through 4 and A through D, though in practice there will be a lot more than 8 items to be matrixized.

 

Google has been my friend many times in the past for Excel queries, but in this case I'm not even sure the name or description of what I'm looking for. I can't imagine I will get what I want out of a simple function/chart, fully expecting to have to VB this.


Create new topic
1397 posts

Uber Geek
+1 received by user: 588


  Reply # 1617842 26-Aug-2016 13:04
Send private message

Looks a bit like a Pivot table. I'd start there.


661 posts

Ultimate Geek
+1 received by user: 177


  Reply # 1617852 26-Aug-2016 13:22
Send private message

Also looks like a could be a scatter plot with the points being unique date if you ignore the colors.

 

Maybe try xyz scatterplot or 3d Scatter plot?

 

Good luck


 
 
 
 


5316 posts

Uber Geek
+1 received by user: 2156


  Reply # 1617873 26-Aug-2016 13:53
Send private message

Would an area plot work for you?





Mike

mdf



2035 posts

Uber Geek
+1 received by user: 604

Trusted
Subscriber

  Reply # 1618014 26-Aug-2016 18:07
Send private message

Thanks everyone. All the suggestions got close but not quite what I was looking for.

 

In the end sorted it out by setting up the matrix chart how I wanted it, then using this script to create a ConcatenateIfs function. I then used this function to "pull" in the data that matched the X/Y criteria, so got all the items listed nicely in the relevant cells.


1275 posts

Uber Geek
+1 received by user: 138


  Reply # 1618029 26-Aug-2016 18:40
Send private message

You could probably do it in power query (get and transform) in excel. You write a m script to select your source data and for each row, stick a category on it, the take the result and create your output data table formatted the way you want.




Software Engineer

 


Create new topic



Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.


Geekzone Live »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.