Need EXCEL To go get info for me and return dollars

PFS12

New Member
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 Combine2855
420782- AutoGuide ready w/ GPS mount4210
425137- NavII4170
425510- 372 waas2502
425504- 372 Omni4430
712635- desktop software639
722611- yield and moisture logging3177
415149- AG manual0

<colgroup><col><col></colgroup><tbody>
</tbody>

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

VERSIONHVYDUTYHEAVY-DUTY
AUTOGUIDANCE420782FACTORY GUIDANCE READY
BACK-UP ALARMXT5201XSTANDARD LESS BACKUP ALARM
BALLAST PACKAGE462716FRONT WEIGHT 462716
COMPRESSION ENGINEXT1933XLESS COMPRESSION BRAKE

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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
 

sergioMabres

Well-known Member
Hi PSF12,
Here is an idea in Sheet1 you have
CODE DESCRIPTION DOLLARSCode
722988- Large Color Display Combine 2,855722988
420782- AutoGuide ready w/ GPS mount 4,210420782
425137- NavII 4,170425137
425510- 372 waas 2,502425510
425504- 372 Omni 4,430425504
712635- desktop software 639712635
722611- yield and moisture logging 3,177722611
415149- AG manual -415149

<tbody>
</tbody>

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

And in Sheet2 the result page

VERSIONHVYDUTYHEAVY-DUTYSUM
AUTOGUIDANCE420782FACTORY GUIDANCE READY4210
BACK-UP ALARMXT5201XSTANDARD LESS BACKUP ALARM0
BALLAST PACKAGE462716FRONT WEIGHT 4627160
COMPRESSION ENGINEXT1933XLESS COMPRESSION BRAKE0

<tbody>
</tbody>

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

CodeSUM
4207824210
4251374170

<tbody>
</tbody>

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:

PFS12

New Member
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 [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] 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 [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] , I am confused! Any thoughts?

Thank you
 

PFS12

New Member
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:

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top