+ Reply to Thread
Results 1 to 12 of 12

Using vlook up

  1. #1
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    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
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,889

    Re: Using vlook up

    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
    Attached Files Attached Files
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Re: Using vlook up

    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

    Quote Originally Posted by etaf View Post
    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

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,889

    Re: Using vlook up

    would you edit and change the title to something more appropriate and then I can continue to help

  5. #5
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Re: If and sumif formula function

    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

  6. #6
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Re: Using vlook up

    Hi

    I have edited the title. Do you get it?

    Thanks
    Shams

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,889

    Re: Using vlook up

    no it appears to say the same on your first post

  8. #8
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    IF and Sum if function

    Hi

    Sorry please tell me how to change the thread title.

    Thanks
    Shams
    Last edited by mahershams; 07-26-2013 at 07:46 AM. Reason: Wrong title

  9. #9
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Re: IF and Sum if function

    Hi

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

    Thanks
    Shams

  10. #10
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,889

    Re: Using vlook up

    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

  11. #11
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Re: Using vlook up

    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

    Quote Originally Posted by etaf View Post
    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

  12. #12
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,889

    Re: Using vlook up

    i did post an example in post #2 - look at the column AD - shows the sites as netbuy or netsell
    how do you want it to appear in X ?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Vlook ups help please
    By TrainerJ in forum Excel General
    Replies: 9
    Last Post: 04-26-2012, 09:35 AM
  2. Vlook up maybe
    By jascer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2009, 06:51 PM
  3. #N/A with Vlook up
    By Guy Wates in forum Excel General
    Replies: 1
    Last Post: 10-14-2005, 09:02 AM
  4. [SOLVED] If with Vlook up
    By souchie40 in forum Excel General
    Replies: 3
    Last Post: 09-09-2005, 03:05 PM
  5. [SOLVED] vlook up
    By Sean in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1