Need EXCEL To go get info for me and return dollars

PFS12

New Member
Joined
Jan 28, 2014
Messages
20
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
Joined
Feb 24, 2013
Messages
946
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
Joined
Jan 28, 2014
Messages
20
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

Well-known Member
Joined
Feb 24, 2013
Messages
946
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:

PFS12

New Member
Joined
Jan 28, 2014
Messages
20
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:

sergioMabres

Well-known Member
Joined
Feb 24, 2013
Messages
946
Always glad to help people around the world with Excel issues
From Argentina cheers
Sergio
 

Forum statistics

Threads
1,085,319
Messages
5,382,940
Members
401,811
Latest member
ngoctinh87

Some videos you may like

This Week's Hot Topics

Top