Need EXCEL To go get info for me and return dollars

PFS12

New Member
Joined
Jan 28, 2014
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top