Sorting and match cellinfo

[nalle077]

I want to sort every company matched side by side, so that I can see the difference in money they bought products for.
I am sure this is an easy task that does not require VBA, but I cant seem to find the perfect sollution.
Some companies is not in both columns, so they cold be last in the list.
The original list is very long and some companies exist and some dont
Please help

2023-01-01​	Value		2022-01-01​	Value
total	120000​		total	258650​
1 Company	1000​		3 Company	5131​
2 Company	2000​		4 Company	84​
3 Company	3000​		10 Company	231321​
4 Company	4000​		11 Company	21​
5 Company	5000​		13 Company	21​
6 Company	6000​		2 Company	2312​
7 Company	7000​		5 Company	1212​
444 Company	8000​		14 Company	18548​
9 Company	9000​
10 Company	10000​
11 Company	11000​
555 Company	12000​
13 Company	13000​
14 Company	14000​
15 Company	15000​

 

[Joe4]

I think you should be able to use a VLOOKUP formula to pull the value from one list and place it in a column next to the other list for each company.
See: MS Excel: How to use the VLOOKUP Function (WS)

 

[nalle077]

hink you should be able to use a VLOOKUP formula to pull the value from one list and place it in a column next to the other list for each company.

How should I do that?
I don´t understand how the sollution in that example could do what I want.

 

[Joe4]

Look up company code from List 1 in List 2, and return associated Value back to that row in a new column.

If you want specific help setting up that formula, we need to know the specifics of exactly where all these data is located (sheet names, range addresses, etc).
Otherwise, we can only give you a formula based on our our assumptions/example, and it will be up to you to adjust it.
So the more detail you provide, the more we can tailor the formula to your exact situation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[nalle077]

Look up company code from List 1 in List 2, and return associated Value back to that row in a new column.

If you want specific help setting up that formula, we need to know the specifics of exactly where all these data is located (sheet names, range addresses, etc).
Otherwise, we can only give you a formula based on our our assumptions/example, and it will be up to you to adjust it.
So the more detail you provide, the more we can tailor the formula to your exact situation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks for the quick reply.
I dont think it is necassary to use XL2BB for this.
All the data is exactly like in my example in the first post.
I just want to sort the companyname and value from column A and B next to the same companyname and value from D and E

 

[Joe4]

You won't use Sorting (if you wanted something like that, you may need VBA).
You can do it with a VLOOKUP formula or SUMIF formula, it will just look a little different.

Just insert a column at column C, and have it return the 2022 values with a SUMIF formula, so it will look like this:

Here is the formula to put in cell C2 and copy all the way down to cell C17:

=SUMIF(E$2:E$10,A2,F$2:F$10)

 

[nalle077]

Here is the formula to put in cell C2 and copy all the way down to cell C17:

=SUMIF(E$2:E$10,A2,F$2:F$10)

Now we´re talking
Could also the companyname be placed in a column left to kolumn C if we add another column.
I´m helping someone and that is what he wants.

 

[Joe4]

Now we´re talking
Could also the companyname be placed in a column left to kolumn C if we add another column.
I´m helping someone and that is what he wants.

Seems a bit redundant/unnecessary, but if that is what they want, let's go back to the orignal VLOOKUP proposal.
So we insert two blank columns, and then have this:

Formula in cell C2 (and copy down all the way to cell C17):

=IFERROR(VLOOKUP(A2,F$2:G$10,1,0),"")

Formula in cell D2 (and copy down all the way to cell D17):

=IFERROR(VLOOKUP(A2,F$2:G$10,2,0),"")

 

[nalle077]

Formula in cell C2 (and copy down all the way to cell C17):

=IFERROR(VLOOKUP(A2,F$2:G$10,1,0),"")

Formula in cell D2 (and copy down all the way to cell D17):

=IFERROR(VLOOKUP(A2,F$2:G$10,2,0),"")

Worked like a charm. Thank you very much

 

[Joe4]

You are welcome.
Glad I was able to help!