Results 1 to 6 of 6

Thread: Need EXCEL To go get info for me and return dollars
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2014
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Need EXCEL To go get info for me and return dollars

    This is going to be long and tedious, or I am just simple minded. I would like to have a database of information (Control) on one sheet, and import data on another sheet, and have a formula compare that to the control and if it finds a similarity I need it to tell me how much money is invovled.

    This would be me control sheet with prices (Two Columns)

    722988- Large Color Display Combine 2855
    420782- AutoGuide ready w/ GPS mount 4210
    425137- NavII 4170
    425510- 372 waas 2502
    425504- 372 Omni 4430
    712635- desktop software 639
    722611- yield and moisture logging 3177
    415149- AG manual 0

    and I would like to import information like this (3 columns)

    VERSION HVYDUTY HEAVY-DUTY
    AUTOGUIDANCE 420782 FACTORY GUIDANCE READY
    BACK-UP ALARM XT5201X STANDARD LESS BACKUP ALARM
    BALLAST PACKAGE 462716 FRONT WEIGHT 462716
    COMPRESSION ENGINE XT1933X LESS COMPRESSION BRAKE

    I would like a cell to scan for certain "Codes" like 420782, and 425137, and sum the total dollars that it returns. I don't know if that is one formula or if it is going to have to be a singular formula for each "code". There are going to be multiple codes for each import, and I would like to have it bounce off my control sheet. Thus I could ammend my control sheet from time to time when the codes change (which they do from year to year) My imports are going to be on a new sheet, but i could have all of them bounce off the "Codes" sheet, but it will all be in one book. I hope I am explaining this ok

    Any thoughts on how to do this?

    Thanks in advanced,
    Seth

  2. #2
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    946
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need EXCEL To go get info for me and return dollars

    Hi PSF12,
    Here is an idea in Sheet1 you have
    CODE DESCRIPTION DOLLARS Code
    722988- Large Color Display Combine 2,855 722988
    420782- AutoGuide ready w/ GPS mount 4,210 420782
    425137- NavII 4,170 425137
    425510- 372 waas 2,502 425510
    425504- 372 Omni 4,430 425504
    712635- desktop software 639 712635
    722611- yield and moisture logging 3,177 722611
    415149- AG manual - 415149

    Where code is =VALUE(LEFT(A2,6))

    And in Sheet2 the result page

    VERSION HVYDUTY HEAVY-DUTY SUM
    AUTOGUIDANCE 420782 FACTORY GUIDANCE READY 4210
    BACK-UP ALARM XT5201X STANDARD LESS BACKUP ALARM 0
    BALLAST PACKAGE 462716 FRONT WEIGHT 462716 0
    COMPRESSION ENGINE XT1933X LESS COMPRESSION BRAKE 0

    Where SUM is

    =SUMPRODUCT(--(Sheet1!$C$2:$C$9=Sheet2!B2),Sheet1!$B$2:$B$9)

    Or if I have only the codes you want to scan

    Code SUM
    420782 4210
    425137 4170

    Where SUM is

    =SUMPRODUCT(--(Sheet1!$C$2:$C$9=Sheet2!A9),Sheet1!$B$2:$B$9)

    Here is a test file for you to see how is the ideas set out
    https://1drv.ms/x/s!AovCE1fDrrdSnEkF41T06MiSBzqr

    Cheers
    Sergio
    Last edited by sergioMabres; Jun 18th, 2019 at 10:00 AM.

  3. #3
    New Member
    Join Date
    Jan 2014
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need EXCEL To go get info for me and return dollars

    Sergio,

    I appreciate the effort here, there are more codes for me to add and I will need to expand on it from time to time, if I one more cell to either formula I get #value as the answer. Did you create a range or table or am I missing something stupid? I really need the codes to act like a data base and I am going to be importing build sheets of machines (which contain the codes) and need to compare them to the chart to give me a dollar value of each machine. This is basically pulling information out of an invoice where the values aren't always given, so if it contains the code I need to apply the correct dollar amount to it and them total it up after. So in your above =SUMPRODUCT(--(Sheet1!$C$2:$C$9=Sheet2!B2),Sheet1!$B$2:$B$9) if I change Sheet1!$C2:$C10.....I get the #value , I am confused! Any thoughts?

    Thank you

  4. #4
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    946
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need EXCEL To go get info for me and return dollars

    It is very easy to miss write a SUMPRODUCT formula for instance matrix size have to be coherent, if you change size in one matrix you need to change in the same way the second matrix
    To see how it works please download the test file at
    https://1drv.ms/x/s!AovCE1fDrrdSnEkF41T06MiSBzqr
    Cheers
    Sergio
    Last edited by sergioMabres; Jun 19th, 2019 at 03:50 PM.

  5. #5
    New Member
    Join Date
    Jan 2014
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need EXCEL To go get info for me and return dollars

    That is exactly what my problem was, Thank you! Maybe you can shed some light on to one more thing for me, and I apologize for being a noob here. When I download my sheet with the information on it to run into the formulas that you helped me with, It downloads as a .CSV. I copy the information and paste it into my columns and rows waiting to tell me my values, and it gives me the green tab in the top left hand corner and stores the numbers as text. The formulas don't run unless I individually check them off one by one and tell it to convert it to a number. Any thoughts as to why it would do that and any way to just grab all of them and convert? I tried the format tab and that didn't work for me. Once again, Shocker, I am confused by this.

    Again thank you for the help...

    Nevermind I just figured it out!
    Last edited by PFS12; Jun 19th, 2019 at 06:15 PM.

  6. #6
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    946
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need EXCEL To go get info for me and return dollars

    Always glad to help people around the world with Excel issues
    From Argentina cheers
    Sergio

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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