Programming

Programming questions? Ask a programmer for answers ASAP

Ask a Computer Expert,
Get an Answer ASAP!

I am having an excel/vba issue. Below I have data Security…

Customer Question
I am having an excel/vba...
I am having an excel/vba issue. Below I have data
Security Total owned Customer
35ybgws 15,000,000 Kipper
45876ghgh 45,000,000 Dan
fgw34t 45,000,000 Alex
fgw34t 36,000,000 Jackson
re54htgewt3 16,000,000 Tanner
rger5445 45,000,000 Ben
sfew45tyhm 36,000,000 Layla
vbedgrwe 25,000,000 Tanner
vbedgrwe 170,000,000 Kara
w2t3ryhtrrgw 19,000,000 Kyle
w2t3ryhtrrgw 16,000,000 Tyson
This is data describing tot holdings of the securities. Each month int is paid to the sec and as you see some securities are held by multiple custs. Below is the data for interest for the month
Security Interest
35ybgws 54,456.00
45876ghgh 456,554.00
fgw34t 3,233.00
rger5445 211,115.00
vbedgrwe 432,523.00
w2t3ryhtrrgw 555.00
I need a VBA code or something that will return the sec,customer,position and interest to calc how much int goes to each cust. So if $100 in interest is paid and 3 custs own it 10%, 10% and 80% I need to break it down to $10 for 2 and $80 for 1. I need a macro.
Submitted: 8 years ago.Category: Programming
Show More
Show Less
Ask Your Own Programming Question
Answered in 2 hours by:
6/15/2010
Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago
John D
John D, Bachelor of Science - Engineering Consultant
Category: Programming
Satisfied Customers: 9,666
Experience: Vast experience in Excel programming and business solutions
Verified

HiCustomer

 

Could you send me your file, that would help me better understand the question.

 

 

 

Ask Your Own Programming Question
Customer reply replied 8 years ago
Yeah so it didn't stay the way I types so it makes no sense! How can I send you the file?
Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago

Ok, to send the file go to www.wikisend.com and upload it there. You will then get a page that has the download link. Copy the download link and come back here and paste it in your reply.


If the file has sensitive information let me know before you upload it.

 

 

Ask Your Own Programming Question
Customer reply replied 8 years ago

This shouldn't be sensitive. Let me know if you need more info.

 

http://wikisend.com/download/497860/Dummy%20Data.xlsx

Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago

Got the file, thanks.

 

However your desorption above does not seem to apply to the data in that file. Could you please explain what you are trying to do, making references to the data in the file.

 

Also it would help if you can give an example or two showing the desired result and where you want the result to be placed.

 

 

Ask Your Own Programming Question
Customer reply replied 8 years ago

So every month, we get interest that has to be paid to our customers who own certain securities. On tab one I run a query which gives me all of our holdings. On tab two, I have imported this month's interest that is due to our customers. If there were not multiple customers that held one security I would just do a V look up to get the owner of to know who to give the interest to on tab two. Because I have certain securities that are owned by multiple customers I need a way to automatically (with a macro) know what percentage of the interest to give to each customer. For instance: The security "GLK2321" is paying out $1,909.375.00 in interest today. I have 4 customers who own this security: Tyson, Edward, Mollie and Yaris. In total we have $117,500,000.00 of this security and as you can see each customer owns a different percentage of the total. Tyson(12,500,000),Edward(70,000,000), Mollie(25,000,000), Yaris(10,000,000).

I need a macro that will automatically give me a list of every customer that has the security and how much of the interest I should be paying them.

Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago

Ok, I think I managed to get it done with formulas only. Of course I can do it with a macro but I think you're going to find it more practical and easier this way (especially since the results will come out automatically without any need to push a button to run the macro)

 

Here you go

 

FILE

 

The formulas are in column H (yellow cells)

 

Hope this is ok. Try it and let me know if it needs any adjustments or if you have any questions, and thank you for clicking Accept if you are satisfied with the work.

 

 

John D
John D, Bachelor of Science - Engineering Consultant
Category: Programming
Satisfied Customers: 9,666
Experience: Vast experience in Excel programming and business solutions
Verified
John D and 87 other Programming Specialists are ready to help you
Ask your own question now
Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago

Is the interest distributed the way you had in mind, or do I need to change the logic of the distribution?

 

Ask Your Own Programming Question
Customer reply replied 8 years ago
This is great, thanks a lot!
Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago

You are welcome :)

 

Ask Your Own Programming Question
Customer reply replied 8 years ago
Hi, so I am trying to use the formula in my actual file and can't seem to get it to work. I'm not sure if it's because the whole file name is XXXXX XXXXX formula or what but I keep getting an error. Is there any way you could just type the formula for me? Thanks if you don't mind. I'm sorry to follow up. You have been so helpful.
Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago

Hi Alyson,

 

No there is no file name in the formula. It should work in any file, provided the source sheet is called exactly "Today's Interest". Also you need to have these lists in the following columns:

Security ID in column B

Balance in column F

 

As for typing the formula for you, here is the formula in H2:

 

=IF(ISERROR(VLOOKUP(B2,'Today''s Interest'!A:B,2,0)*F2/SUMPRODUCT(($B$2:$B$514=B2)*($F$2:$F$514))),"",VLOOKUP(B2,'Today''s Interest'!A:B,2,0)*F2/SUMPRODUCT(($B$2:$B$514=B2)*($F$2:$F$514)))

 

If you still need help send me your actual file and I will be happy to fix it for you

 

 

 

Ask Your Own Programming Question
Customer reply replied 8 years ago
I'm sorry, I am trying to make my range bigger so 514 I need to be like 1000 but I can't just change it in the formula. Why?
Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago

You need to replace all four occurrences of 514 in the formula with 1000. Here is the modified formula in cell H2

 

=IF(ISERROR(VLOOKUP(B2,'Today''s Interest'!A:B,2,0)*F2/SUMPRODUCT(($B$2:$B$1000=B2)*($F$2:$F$1000))),"",VLOOKUP(B2,'Today''s Interest'!A:B,2,0)*F2/SUMPRODUCT(($B$2:$B$1000=B2)*($F$2:$F$1000)))

 

 

Ask Your Own Programming Question
Customer reply replied 8 years ago
So when I change it in my file, it changes the answer but when I changed it in your file, it was right. Do you know why this could be? I really am sorry for all of the follow up.
Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago

Ok please send me your file (or a smaller version of your file) so that I will be able to help you.

 

Ask Your Own Programming Question
Customer reply replied 8 years ago

http://wikisend.com/download/151016/Fina P&I working doc.xlsx

 

Here is the link. TY

Programmer: John D, Bachelor of Science - Engineering Consultant replied 8 years ago

Here you go

 

http://wikisend.com/download/598062/Fina%20P&I%20working%20doc%201.xlsx

 

The modified formulas are in the same column (column I)

 

Hope this is ok. Let me know if you have any questions

 

 

Ask Your Own Programming Question
Was this answer helpful?

How JustAnswer works

step-image
Describe your issueThe assistant will guide you
step-image
Chat 1:1 with a programmerLicensed Experts are available 24/7
step-image
100% satisfaction guaranteeGet all the answers you need
Ask John D Your Own Question
John D
John D
John D, Bachelor of Science - Engineering Consultant
Category: Programming
Satisfied Customers: 9,666
9,666 Satisfied Customers
Experience: Vast experience in Excel programming and business solutions

John D is online now

A new question is answered every 9 seconds

How JustAnswer works:

  • Ask an ExpertExperts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional AnswerVia email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction GuaranteeRate the answer you receive.

JustAnswer in the News:

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

What Customers are Saying:

My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed.

One Happy CustomerNew York

Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help.

Mary C.Freshfield, Liverpool, UK

This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!!

AlexLos Angeles, CA

Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult.

GPHesperia, CA

I couldn't be more satisfied! This is the site I will always come to when I need a second opinion.

JustinKernersville, NC

Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around.

EstherWoodstock, NY

Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know.

RobinElkton, Maryland

< Previous | Next >

Meet the Experts:

ATLPROG

ATLPROG

Computer Software Engineer

7,677 satisfied customers

MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML

LogicPro

LogicPro

Computer Software Engineer

7,175 satisfied customers

Expert in C, C++, Java, DOT NET, Python, HTML, Javascript, Design.

unvadim

unvadim

Computer Software Engineer

1,168 satisfied customers

Good knowledge of OOP principles. 3+ years of programming experience with Java and C++. Sun Certified Java Programmer 5.0.

lifesaver

lifesaver

Computer Software Engineer

950 satisfied customers

Several years of intensive programming and application development experience in various platforms.

ehabtutor

ehabtutor

Computer Software Engineer

936 satisfied customers

Bachelor of computer science, 5+ years experience in software development, software company owner

TheDoctor

TheDoctor

Software Engineer

893 satisfied customers

M.S. in Internet Information Systems

The-PC-Guy

The-PC-Guy

Software Engineer

848 satisfied customers

Extensive Knowledge in PHP, MYSQL, CSS & Javascript

< Previous | Next >

Related Programming Questions
I have a multiple worksheet document. It is a work schedule
I have a multiple worksheet document. It is a work schedule product that has a tab for every month of the year and a leader tab (named "Current") that I want to fetch data based on a value input into … read more
The-PC-Guy
The-PC-Guy
Software Engineer
Bachelor's Degree
848 satisfied customers
I am looking to rearrange excel data based on time period. I
I am looking to rearrange excel data based on time period. I am essentially looking to shift data to match the month it corresponds to (ie January Month 1 data would be January and Month 2 data would … read more
The-PC-Guy
The-PC-Guy
Software Engineer
Bachelor's Degree
848 satisfied customers
Good Evening, (almost morning), I wrote code in Fortran
Good Evening, (almost morning), I wrote code in Fortran IV from about 1965 to 1969. About 1979 I was introduced to business basic (BBX) and hired a contract programmer to write code for a small compan… read more
AAATom
AAATom
Software Engineer
Master\u0027s Degree
42 satisfied customers
I am using some Excel Spreadsheets that use a custom function
I am using some Excel Spreadsheets that use a custom function that I wrote in VBA. When I open the spreadsheet, I used to select "Enable Editing" when it would pop up, and the all functions would reca… read more
Brandon M.
Brandon M.
System Administrator
Vocational, Technical or Trade Scho
2,143 satisfied customers
Hi, I have one workbook with 5 different sheets of information.
Hi, I have one workbook with 5 different sheets of information. All sheets have Application data on them. I need to make sure that if more Applications are added to any of these sheets, they automatic… read more
John D
John D
Bachelor of Science - Engineering Consultant
Bachelor Degree in Engineering
9,666 satisfied customers
I am putting together a patient record database In Access 2010
I am putting together a patient record database In Access 2010 for a clinic. When doing intake for a new patient, each clinician will have a (blank - with no saved records) copy of the database on a f… read more
patrickdengler
patrickdengler
Software Architect
Master's Degree
33 satisfied customers
need an expert who is familiar with Axia college prog.
Attachment: 2011-10-17_005327_it210_appendix_h.doc Attachment: 2011-10-17_005345_it210_appendix_i.doc Attachment: 2011-10-17_005440_it210_appendix_e.doc I need an expert who is familiar with Axia coll… read more
Alex
Alex
Sr. Software Engineer
Bachelor\u0027s Degree
332 satisfied customers
I am stuck on a program that is 90% complete. I have a function
I am stuck on a program that is 90% complete. I have a function that is setup as case statements and I can't use case statements in the program. The section I am having issues with is the function lab… read more
ATLPROG
ATLPROG
Computer Software Engineer
Master's Degree
7,677 satisfied customers
i would like to conditionally format a trendline in an excel chart based &#
i would like to conditionally format a trendline in an excel chart based on a date. so a date greater than six months is green, a date within 6 mos of a certain date is yellow and a date greater is re… read more
Suryanto
Suryanto
Computer Engineer
Bachelor's Degree
535 satisfied customers
I need help on a course project. This is a what I need Project
I need some help to create a java course project program. For lifesaver, arty or raj or any expert who has java experience. Here is a download location which has the starter files and the full program… read more
Hugh
Hugh
Bachelor's Degree
36 satisfied customers
I am having problem fixing my error any ideas for me. Thanks /* Source
I am having problem fixing my errors for a java program I need to run, any ideas for me. Thanks /* Source Name: TykesProj.java Date Written: 7/14/10 wtitten by: XXXXX XXXXX */ import java.io.*; public… read more
Kevin Bui
Kevin Bui
Software Developer
Bachelor\u0027s Degree
108 satisfied customers
need 8 programming questions answered from the textbook AN
need 8 programming questions answered from the textbook "AN introduction to programming using visual basic 2008" by david schneider 7th edition. Do you own the book by chance?… read more
Kevin Bui
Kevin Bui
Software Developer
Bachelor\u0027s Degree
108 satisfied customers
I need your help with this - Complete SR-mf-003, Change Request
I need your help with this - Complete SR-mf-003, Change Request 4. Insert comments in the program to document the program. Attach a design flow chart to the program's source code. Requestor: Ninfa Pen… read more
Ansh P.
Ansh P.
System Analyst
Bachelor\u0027s Degree
6,393 satisfied customers
I need a MySQL query to build a table of standings for NFL
I need a MySQL query to build a table of standings for NFL like this one. I have two tables that are populated with enough data to make this happen. A certain amount of flexibility is allowed. If I ha… read more
toughguy
toughguy
Master\u0027s Degree
210 satisfied customers
Is there a way to have specific text written into the body
Is there a way to have specific text written into the body of an email sent from within Excel by macro?… read more
Russell H.
Russell H.
Computer Technology Consultant
Vocational, Technical or Trade Scho
792 satisfied customers
I have a workbook in Excel 2002. It has several worksheets,
I have a workbook in Excel 2002. It has several worksheets, and I have created at least two named cells in each worksheet. Normally, I save the workbook at the end of the day, and open it the next day… read more
Cherryl Lewis
Cherryl Lewis
Bachelor's Degree
180 satisfied customers
I need to modify my Java program with the below requirements Use
I need to modify my Java program with the below requirements: Use an array for the mortgage data for the different loans. Read the interest rates to fill the array from a sequential file. Display the … read more
Cherryl Lewis
Cherryl Lewis
Bachelor's Degree
180 satisfied customers
Im doing some Perl programming to extract DNA sequence data. I
I'm doing some Perl programming to extract DNA sequence data. I hope to be able to enter on a command line: perl DNA_sequence.pl NC_001666 rps12 (or something like this...may be different depending on… read more
Karl Everest King
Karl Everest King
Web Designer, Programmer
Vocational, Technical or Trade Scho
224 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less

Ask Your Question

x