Need EXCEL To go get info for me and return dollars

[PFS12]

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)

[TABLE="width: 401"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]722988- Large Color Display Combine[/TD]
[TD="align: right"]2855[/TD]
[/TR]
[TR]
[TD]420782- AutoGuide ready w/ GPS mount[/TD]
[TD="align: right"]4210[/TD]
[/TR]
[TR]
[TD]425137- NavII[/TD]
[TD="align: right"]4170[/TD]
[/TR]
[TR]
[TD]425510- 372 waas[/TD]
[TD="align: right"]2502[/TD]
[/TR]
[TR]
[TD]425504- 372 Omni[/TD]
[TD="align: right"]4430[/TD]
[/TR]
[TR]
[TD]712635- desktop software[/TD]
[TD="align: right"]639[/TD]
[/TR]
[TR]
[TD]722611- yield and moisture logging[/TD]
[TD="align: right"]3177[/TD]
[/TR]
[TR]
[TD]415149- AG manual[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 573"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]VERSION[/TD]
[TD]HVYDUTY[/TD]
[TD]HEAVY-DUTY[/TD]
[/TR]
[TR]
[TD]AUTOGUIDANCE[/TD]
[TD]420782[/TD]
[TD]FACTORY GUIDANCE READY[/TD]
[/TR]
[TR]
[TD]BACK-UP ALARM[/TD]
[TD]XT5201X[/TD]
[TD]STANDARD LESS BACKUP ALARM[/TD]
[/TR]
[TR]
[TD]BALLAST PACKAGE[/TD]
[TD]462716[/TD]
[TD]FRONT WEIGHT 462716[/TD]
[/TR]
[TR]
[TD]COMPRESSION ENGINE[/TD]
[TD]XT1933X[/TD]
[TD]LESS COMPRESSION BRAKE
[/TD]
[/TR]
</tbody>[/TABLE]

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]

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

<tbody>
</tbody>

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

<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

Code	SUM
420782	4210
425137	4170

<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

 

[PFS12]

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

 

[sergioMabres]

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

 

[PFS12]

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!

 

[sergioMabres]

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