2 criterias to organize data by VLookup

Hi douglascaixeta,

To work with 2 criteria, you should use Index and Match functions together, instead of using vlookup.

Upload a sample workbook, and I'll write a sample formula for you.

Cheers,

Thank you very much Mayda89.

Here is the file: Data.xlsx

I would like to learn to make a graph:

- Showing how prices of each company change per month.
- Showing how prices of each item change per month.

Hi douglascaixeta,

I just looked at the workbook you uploaded, and noticed that in this workbook, a date occurs only once.
From what you have explained, however, I understand that a date should occur more than once.

Please upload a sample workbook that better reflects your original workbook, this and other aspects you consider important.
Otherwise, the formulas I'll write won't work in your original workbook.

Cheers,

Yes, it does occur several times the same date. Data.xlsx

I did like this because I wanted to test with several different months.

Sorry, you're right, I missed them

Anyway, since there are too many variables, and you have all of your data organized this way(every company and every car is on the same table), the solution is a little bit complicated. I had to follow several steps to do it, and these steps maybe may scare you, however, with the data organized this way, I don't see any other solution. (p.s: if following these steps is too hard for you, you could also prefer to write a vba code for these steps.)

See the uploaded workbook.

Hey!

Thank you. I understand what you did. Wise solution.

The problem is that the graph have so many lines that is impossible to see anything. I actually was thinking about 2 graphs: one for company prices evolution, in general and other for item prices evolution. The items will be a lot more than the one you see.

I do not have any data actually. So based on these 2 criteria, you can tell me what is the best way for me to start storing the data:

- Evolution of prices of each company.
- Evolution of prices of each item.

The problem for me is: as I cannot quote all items in all companies (some companies do not have that item) its hard to compare the overall prices of each company. It will be like deep statistics. I dont even know if its possible.

I'm glad that you like it
So looks like I don't need to explain each step.

Anyway, let's go back to your next question:
You said you want two graphs, one tracking the aggregate company prices (one series for each company I suppose) and one tracking the item prices (one series for each item I suppose). How do you want to aggregate these prices? By taking the simple average? Or some sort of weighted average, perhaps using the monthly units of sales etc ?

Cheers,
Gokalp

Originally Posted by douglascaixeta

Hey,

I cannot use average prices for each company. Because I will be comparing prices of different items. For example: I quote very expensive item on one company and cheap items in the other company, this way it will look like one company had higher prices than the other, and its not true.

For the items yes, simple average for each item will do just fine.

For the companies I don't know. Those are insurance prices. So I cannot compare insurance of a truck with insurance of a small car. I cannot compare insurance price of an imported car with a national car.


P.S.: Maybe a dispersion graph will be good. So I have each item a small dot, each company a color of that dot. The X axis the price. So I look at the graph and I can see that if the right part of the graph is populated with more greens I know the green company is more expensive.

P.S.2: Maybe time should go on X and price on Y. Im trying to make that.

Douglas,

I can't make the graph, without knowing how you want it organized.
So I think first you should make up your mind about it.

About the alternative you offered at your first p.s: If you want it organized that way, you can't see how things change across time, is it ok for you? If yes, than it's easy to do the graph like that, just tell me.

About your second p.s: Yes, time can go on x axis, no problem, but again, it doesn't answer the question on how to aggregate company data.


I think you need to decide on what you need to analyze.
Come back when you make up your mind, I'll be around to help!

Cheers,
Gokalp

What I need to analyse is evolution of prices in time. For each item its done. I made the graph.

For the companies, the P.S. 2 is perfect if possible. I need to know time. But I don't know how to make excel understand the company as color, the item as dot, the X as time and Y as price.
The data doesn't need to be aggregated. As you have each dot as a new quote floating in the chat according to time and price.

I made a new one. This one is better because it have more companies for each item, more realistic: Data v3.xlsx

Glad that half of it is done!

About the 2nd graph:
When you say

Originally Posted by douglascaixeta

For the companies, the P.S. 2 is perfect if possible. I need to know time. But I don't know how to make excel understand the company as color, the item as dot, the X as time and Y as price. The data doesn't need to be aggregated. As you have each dot as a new quote floating in the chat according to time and price.

it looks like you're talking about a "scatter plot" type chart, and you want to ignore model types in this graph.

I made the graph you asked, see uploaded workbook.

P.S: If it makes sense to you I'm glad; but I think this graph is even more confusing than the first graph we made. :/

Cheers,
Gokalp

Thanks. That does look good. But I was expecting to be able to put the cursor over a dot and see which item is. I think the best would be a bar graph and the item is X, the price is Y, each bar is the company. But then there is no time. Then I aggregate per month, and make one graph per month.

Do you have any idea on how to put 4 variables in one graph ?

Originally Posted by douglascaixeta

Thanks. That does look good. But I was expecting to be able to put the cursor over a dot and see which item is.

Excel does not have that utility. However, there is free a third party add-in called XY-Chart-Labeler that accomplishes what you want.
The link is below.
http://www.appspro.com/Utilities/ChartLabeler.htm

Originally Posted by douglascaixeta

Do you have any idea on how to put 4 variables in one graph ?

Yes, buy a computer that has a 3-dimensional screen

You're welcome!

Originally Posted by douglascaixeta

P.S. Never mind. I got the XY Chart labels to work.

Does it mean your problem is solved?

See uploaded workbook.

P.S: For some companies, you may get an error, because in your list not every company has every product.
You can use the iferror function to deal with it.

Yes thanks!

Problem now is how to make a graph that gets: Company, Average value I just got and Time.
For me that would be a line graph, where the Y is price, X is time and company is colour of each line. Even that seems too difficult. Excel should be more smart, or Im too dumb?

Done with a table. Very annoying. I will have to learn how to deal with pivot tables, like you did in the first solution.

Originally Posted by douglascaixeta

Done with a table. Very annoying. I will have to learn how to deal with pivot tables, like you did in the first solution.

Yes, pivot-table is an AMAZING tool! I have also discovered it recently, it is really a lifesaver!
Anyway, does it mean that your whole problem with the chart, that you described in the previous post is solved?

Not yet. My only problem now is to create that bubble chart I showed you, but in Excel. I created everything, but I cannot make the bubble have different colour according to the company.

As you see here: Data v4.xlsx

- X: Time
- Y: Value
- Bubble: Item
- Size of bubble: Value
- Color of bubble: company

They do make sense to me, and Im the user of it.

I used your last graph, the one that identifies the company as circle, square, triangle, and just turned into bubble graph, and reduced the bubble size to 10. Bubble size doesn't matter as Y already shows the value.
Excel doesn't choose bubble size according to the serie. Its a number. 100 is too large, my values go up to 5000. Excel is not so smart as Google.
The solution is to make a relative number based on value, but I won't do that, because the bubble size doesn't matter that much.

So this is all I guess. Thank you very much for all the help. I have now a great idea of the graph I need and how I have to organize the registers.

One last question: Is there an easier way to create the bubble graph ? The way I'm doing it, I need to select serie by serie. The data of each company separated. With 500 registers that will be a huge problem.

Thanks. I see what you meant. Don't know what will be the choice with less work yet.

I'm having now the following problem: http://www.excelforum.com/excel-form...07#post3179207
I don't want to continue being so off-topic, so I created this new topic.

Thank you again. Problem solved here.