Using vlook up

Dear Concern

I need a help to identify the data list in the attached file as "Net buying" and "Net selling" companies. In X column I have created a new ID and based on this ID, I have calculated their net buy and net sell quantity in AB column. Now I want to identify each cell in X column whether it is a "net buying" company or "net selling company". There are some companies in the data set where they buy and sell shares.

Definition of Net buying company = If a same company both buy and sell share and if their buy quantity exceed the sale quantity in column AB, then it is known as Net buying company and opposite is net selling company.

I have attached the file.

Please help me.

Regards
Shams

Dear etaf

Thank you very much for quick reply. Sorry for incorrect thread title. Just want to know little extra,

If a company is sell more than buy I want this company as Net sell company, even in rows when this company got buy information. For example, I need both X 22 AND X23 cell to be known as NET selling company marked in AD22 cell (currently it shown as Net sell) and AD23 cell (Net buy) because these companies sell value exceed the buy value.

Thanks in advance for your help.

Thanks
Shams

Originally Posted by etaf

You would need to EDIT and change your title
have a read if rule 1 here
http://www.excelforum.com/forum-rule...rum-rules.html

Your post title should accurately and concisely describe your problem, not your anticipated solution.
I know the moderators do monitor and post for people to change title

so i have done this in two parts

in AC2
=IF(AA2="sell",AB2*-1,AB2)
I wanted to change the value you had in AB to show -ve for sell and + for buy
and then in AD2

=IF(SUMIF(E:E,E2,AC:AC)<0,"Net Sell","NetBuy")
find out if the overall number is less than zero or greater than = to zero
not sure if you want to class 0 as a different type

Dear etaf

Thank you very much for quick reply. Sorry for incorrect thread title. Just want to know little extra,

If a company is sell more than buy I want this company as Net sell company, even in rows when this company got buy information. For example, I need both X 22 AND X23 cell to be known as NET selling company marked in AD22 cell (currently it shown as Net sell) and AD23 cell (Net buy) because these companies sell value exceed the buy value.

Thanks in advance for your help.

Thanks
Shams

Hi

I have edited the title. Do you get it?

Thanks
Shams

Hi

Please have a look now. I have changed the title.

Thanks
Shams

Dear etaf

Thank you very much for help. It seems very complicated to me. I do not understandd what you want to say me. Is it possible to show me in excel file.

Sorry for the inconvenience.

Thanks
Shams

Originally Posted by etaf

doesnt seem to have worked - you can use the report button and ask a mod how to do it

in my AD cell the it will have net sell or net buy for the company and will be the same for all the company with the same name in E

so how do you want it to show in X and Y
you could just append the name in E and the status in AD

=E2&"_"&AD2
to get the company listed as Buy or Sell based on the +ve or -ve result in AD

Brickworks Ltd
come out as a net buy in my calc
79247
-12056

for the two enties
and so
=IF(SUMIF(E:E,E83,AC:AC)<0,"Net Sell","NetBuy")

=SUMIF(E:E,E83,AC:AC)
will =
67191
67191

for both entries and
=IF(SUMIF(E:E,E83,AC:AC)<0,"Net Sell","NetBuy")

makes them a netbuy

filter on that company name in E and see

if that is what you need