• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Vlook-up C&P multiple values

N

NewToVBA

New Member
Hi all,

Hoping you can help, I am trying to learn VBA and I have spent 2/3 hours googling this but I either don't understand the code or it doesn't answer what I am trying to do! ARGGHHH

I work in insurance at going forward I have to run 2 reports
Report one: Transaction - this details all the business transaction for the primary introducer
Introducer report: This details all the introducer that are getting paid on the policy
Our system can only issue a report based on primary introducer, it doesn't combine several in a single report - hence the 2 reports.

I need to code a macro that with merge this 2 reports into a single sheet. The main info that shows on both reports is the invoice reference which I want to you to look up all the introducers connected to that record.

Attached is a draft excel sheet.

Sheet 1: Transaction: This pulls the primary introducer details/client details and any agents connected
Sheet 2: Introducer: This is where it lists any introducer connected to that invoice ref is shown
Sheet 3: Results: This is where I want the date pasted to, above each heading is the cell there the data is stored. Ie Colum B is client name and Above is "T-K" meaning Transaction sheet "Sheet 1" column K.

I have abbreviated the data and deleted others for DPA reasons.

Please can someone help!

PS - if anyone has any good sites to learn basic VBA etc, please do send links so I can try and learn more, if you can help with the code, please do let me know what the code is asking so I can at least understand what it is doing to help with future changes.
 

Attachments

  • Master Calc - Macro Enabled.xlsm
    100.3 KB · Views: 6
It's very difficult to work out how you want the output. There are loads of ways.
Can you put together a manually created report with data in specifically for ref 503311618 ?
If you could also do it for ref. 503301676 which has 9 rows in the Introducer sheet.

That will give me a good idea how to put it together.
 
p45cal said:
It's very difficult to work out how you want the output. There are loads of ways.
Can you put together a manually created report with data in specifically for ref 503311618 ?
If you could also do it for ref. 503301676 which has 9 rows in the Introducer sheet.

That will give me a good idea how to put it together.
Click to expand...


Thank you very much for you reply.

I have entered the two refs how I want them to show.

Basically, the transaction report will only show the main introducer and agent, we then have to run an introducer report to details all the pay always on that policy, the results page is intended to merge the two together. Each line on the results page gives us the policy info, premium info and then who are noted as introducers and how much each is getting paid.

Regards the second ref - there are so many entries because we have invoiced all the polices at the same time. Going forward we will invoice each one separately so it gives a break down for each policy.

Therefore each invoice reference would only have the maximum of 4 introducers columns on the results page(3 intro's pulled from intro sheet ie RS, GS IH and the agent pulled from the trans sheet ie SP) per invoice ref on future reports. (Thanks for pointing out, I hadn't thought about that)

Hope that makes sense but please do let me know if I can help clarify it any further.

Thanks for your help.
 

Attachments

  • Master Calc - Macro Enabled.xlsm
    101 KB · Views: 5
In this picture, showing 2 sheets filtered for 503301676, what decides the values shown in the Results sheet columns P,V & X? Is it the maximum of the 3 values in the Introducer sheet?
72132

Sleep time here…
 
p45cal said:
In this picture, showing 2 sheets filtered for 503301676, what decides the values shown in the Results sheet columns P,V & X? Is it the maximum of the 3 values in the Introducer sheet?
View attachment 72132

Sleep time here…
Click to expand...

Hi,

Thanks for the above, I can see a slight issue with the way we process this internally from this. The reason there are 9 rows connected to that invoice reference is because we have invoiced all 3 policies in one invoice.

Going forward, we will invoice each policy separately so there would be a maximum of 3 rows per invoice (Instead of the 9 shown) The 9 in your picture are for 3 different policies.

So on the future report for example, the invoice ref would only show one figures for eavh of the introducers.
For example the below pictureExample.JPG
 
Last edited by a moderator:
OK, understoood; I'll doctor the Introducer sheet by removing 6 rows.
Onto the next question, picture below showing data filtered for 503311618. There's nothing in the Results sheet which takes into account row 17 of the Transaction sheet, What should happen here?
72136
 
p45cal said:
OK, understoood; I'll doctor the Introducer sheet by removing 6 rows.
Onto the next question, picture below showing data filtered for 503311618. There's nothing in the Results sheet which takes into account row 17 of the Transaction sheet, What should happen here?
View attachment 72136
Click to expand...

The admin fee in Colum R (17) would be copied into the merged row on the results page, but all the other columns on that admin fee row can be ignored ie don;t need to merge with data in column W (22).

Thanks you again, your help is much appreciated.
 
OK, I'll sum the values of column R in Transaction sheet, which still leaves the question of which of the two values in column W of the Transaction sheet should I use? While as a human I can look at those two rows and see lots of zero values (and nothing in column F btw), computers are a lot less discerning and need some hard and fast rules about which to choose; can you give me some pointers?

edit post posting:
For me to continue trying to help, please include all your cross posts links (if there are anymore) and take a peep at this:

A message to forum cross posters

Do you know why you're here? If you have been pointed to this page, it is not because someone is mad at you, but rather because we want to curb your posting habits before people start ignoring your posts. Online … Continue reading →
www.excelguru.ca www.excelguru.ca
Nothing wrong with cross posting btw, just how you do it (I hope I'm not treading on the toes of moderators saying this).
 
Last edited:
p45cal said:
OK, I'll sum the values of column R in Transaction sheet, which still leaves the question of which of the two values in column W of the Transaction sheet should I use? While as a human I can look at those two rows and see lots of zero values (and nothing in column F btw), computers are a lot less discerning and need some hard and fast rules about which to choose; can you give me some pointers?
Click to expand...

Okay that all makes sense.

If it's an admin fee, the column F will always be blank because it's our admin fee and therefore there is no insurance company.

So for W, is there a sort of IF statement that says, only copy W where F contains text? That way it will only copy the insurer premium (ie 1,312.50), but will ignore the admin fee (ie 30)?
 
p45cal said:
Can do.
Could you review msg#9 re crossposting please (updated since you posted last)?
Click to expand...

Sorry - new to the forum so didn't realise. Do I just put the link in my original question or is there a box you tick?

I only posted on the one other forum Excelforum, not anywhere else. I choose 2 forums in case one question was left unanswered. You both came back last night.

I have looked at the option from the other forum but it doesn't work, the results page has 154 rows, when it should only really have 30+. I believe this is because he is using the data from the introducer sheet, rather than the transaction sheet. It needs to be from the transaction sheet as that is all the clients by a certain individual, the intro sheet is a list of client for everyone in that time period. That way we can run a trans report of any one individual, and merge with all introducers to find all the pay aways for each policy for that one individual

I will reply on that to say thank you anyway though.

I would rather continue working with you as you have a better understanding of the end solution following the back and forth today.
 
NewToVBA
Do You have an idea which solution would You use
this Forum's or that other Forum's - which has given yesterday evening?
... and after You have get a solution, there will be tasks for You too.
 
vletm said:
NewToVBA
Do You have an idea which solution would You use
this Forum's or that other Forum's - which has given yesterday evening?
... and after You have get a solution, there will be tasks for You too.
Click to expand...

Hi - sorry if my previous message wasn't clear, the solution on the other forum didn't work, I want to stay on this forum to finish the solution as p45cal had taken the time to try and understand what I am looking for and we have has several back and forth to try and make it just what i needed.

No problem with me doing tasks!
 
NewToVBA
Hint:
If that other Forum's solution didn't work then would it at least polite to comment something to there.
Questions:
What would You think Yourself if You would have used time for someones thread and none feedback?
Would You be so active to use time next time for same users thread?
 
vletm said:
NewToVBA
Hint:
If that other Forum's solution didn't work then would it at least polite to comment something to there.
Questions:
What would You think Yourself if You would have used time for someones thread and none feedback?
Would You be so active to use time next time for same users thread?
Click to expand...

Hi,

I did mention further up that I would log in to that forum and thank the person later on today for their help.
 
The attached has a table at cell A16 of the Results sheet. You can clear some of the cells in that table and then update the table by right-clicking anywhere within that table and choosing Refresh.
Check the values are all correct. A few differ from your expected values but I think (?) yours are incorrect. If not I'll have to rethink.
On the Introducer sheet, I've moved the six rows I talked of doctoring to below that table (they're marked with an x. I've left instructions down at the bottom right of that table).
The table on the Results sheet has columns much in the order you wanted them except for the rightmost columns where I've put all the introducers and their amounts. The 1° Introducer is always your Primary Introducer but the rest aren't in any particular order. The calculated column is also there.

This is very much work in progress and it needs a lot of tidying up behind the scenes.
There is no macro.

You say your 'system' can only produce certain kinds of report; what is that system? Does it produce reports which you've imported yourself into Excel or does it produce Excel workbooks/worksheets? I ask all this because while I've produced a result from 2 tables on Excel sheets, it's very likely to be faster, more robust* and much easier to update with new data in the future, if the system produces other kinds of files which Excel will be able to interrogate (you'd just have to arrange for those files to be put in the same place each time they were produced and all you'd need to do is refresh the table). If the reports are produced by a well-known database or some such, it's very likely Excel will be able to interrogate that data(base) directly, without the system needing to produce reports at all. Again, all you'd need to do is refresh that table.

An example of where things can go wrong is Excel's handling of dates - it tries to be helpful by trying to convert things which are brought into cells that look like dates, into proper dates, but often gets it wrong especially as dates can be misinterpreted because of the American/UK month/day tranposition. Your file has mostly dates which have remained as strings, but one or two are real dates (cell A16 of the Transaction sheet for example) which could cause things to go awry. Reports produced without Excel don't usually suffer from the 'helpful' Excel treatment.

You're in Surrey? Snap.
 

Attachments

  • Chandoo45321Master Calc v01.xlsx
    126 KB · Views: 11
Wow! It looks amazing!
I think you have completely got what I needed, it looks perfect to me.

The system I use is called "Acturis" it's an insurance specific system, all reports are delivered in an excel format. I will then copy the data over and refresh the table.

Can I ask how you created this? Where is the code? I ask for 2 reasons
1: So I can amend if needed in the future
2: I want to understand the code so I know how you did this so i can learn how to do future reports

Thank you for taking the time to do this - it's much appreciated/ I will test it with future reports on Monday when the next lot get produced but it looks like exactly what i need!
 
NewToVBA said:
Can I ask how you created this? Where is the code? I ask for 2 reasons
1: So I can amend if needed in the future
2: I want to understand the code so I know how you did this so i can learn how to do future reports
Click to expand...
It's Power Query (Get & Transform Data on the Data tab). As I said, it's work in progress so there's a whole mess of queries, mostly experimental stuff. It needs major refining - I'm still getting my head around Power Query; hopefully, now you've confirmed the sort of thing you want, it makes it clear to other potential responders just what you want and (hopefully) someone much better than me at Power Query etc. might look in and provide a much slicker solution. You could start a new thread in the https://chandoo.org/forum/forums/power-bi-power-query-and-power-pivot.20/ section of these forums (provide a link to this thread for context) and/or (I hope I don't get censured for suggesting another website) you could start a thread at https://www.excelguru.ca/forums/forumdisplay.php?19-Power-Query-(Get-amp-Transform)-amp-M-code (not forgetting of course to provide cross posting links at both!). Ken Puls is the owner of ExcelGuru and has written a book or two on Power Query and is a recognised authority on the Power BI suite.
The code (called M Code) can be reached in several ways, but the fastest way to get to it is to right-click the result table, choose Table |Edit Query… The steps are on the right and you can click through them. You can see the mess of queries I've made by expanding the Queries panel on the left. The whole code for a single query can be seen by clicking on the Advanced Editor icon in the Query section of the Home tab.

NewToVBA said:
I will test it with future reports on Monday when the next lot get produced but it looks like exactly what i need!
Click to expand...
OK, just be aware that the two source tables have to be named the same as they are now (perhaps just paste the data into the existing tables) and the Headers used should be exactly the same, including case.

For future updates, if Acturis provides Excel files then all that would probably be needed is for the Excel file(s) produced by it to be put in a specific folder, then all you'd have to do is refresh the results table. If Acturis can provide other kinds of file it might be better as the dates have shown themselves to be a bit random in your Excel file.

I see that Acturis has an 'Integrated MI Suite' so you could ask them if you can connect the MS Power Suite directly to it although I suspect they might say 'no' and want to use their 'over 300 prebuilt reports which can be tailored to your specific requirements' and charge you for the tailoring!
 
Last edited:
Hi mate,

Sorry - - yes Surrey based for its pro's and cons!

I am just outside of Guildford.

I have encountered a small problem! To be honest, every time we find solutions I encounter a new problem so this is normal.

Acturis unfortunately is not designed for our company set up so isn't able to produce the MI reports which is why I am trying to create one. It has hundreds of pre-built reports but none that can do this.

My problem - By using the primary introducer, this will miss off any cases where RS gets a pay away as a secondary introducer, for example GS is primary and RS secondary. The scenario is where two "Partners" work together on a case and share the income depending on the business line.

I have tried to include all the data to then look at filtering the results where he shows, but when I copy and paste all the data into your sheet it comes up saying "[Expression.Error] We cannon convert the value null to type table "

Do you know how I fix this?

Thanks in advance,
Shaun

 
Oh and Acturis will not allow any 3rd party systems into theirs, I think there are 3 external systems from memory that can plug in but none are useful, it's great from a security point but shit from a data manipulation point.
 
NewToVBA said:
By using the primary introducer, this will miss off any cases where RS gets a pay away as a secondary introducer
Click to expand...
OK, save me a bit of time; Does this happen in the file that I've seen? If so can you point me to it? If it doesn't, can you attach a file where such cases exist, and point out what's missed off, so that I can test?
 
In the file I have, I've identified 3 Invoice Refs involving RS on the Introducer sheet in column A (Introducer Name) where no such Invoice ref is present on the Transaction sheet:
503295694, 503295756 & 503295835.
If I include these data from the Introducer sheet, those rows will, by definintion, have no data from the Transaction sheet, viz. nothing in the following columns:
Policy Contact Name, Policy Status, Insurer, Effective Date (Transaction), Discounted Premium, IPT Amount, VAT Amount, Discounted Premium + IPT, Policy Fee, Admin Fee, Client Amount, Commission Rate, Total Commision, VIBL Commission, Transaction Agent & Agent Commission.
Unless I'm looking at a reduced dataset here.
I haven't investigated again where the Inroducer Amounts on the result table come from, but that data won't come from the Transaction sheet.

Am I following you correctly?
 
p45cal said:
In the file I have, I've identified 3 Invoice Refs involving RS on the Introducer sheet in column A (Introducer Name) where no such Invoice ref is present on the Transaction sheet:
503295694, 503295756 & 503295835.
If I include these data from the Introducer sheet, those rows will, by definintion, have no data from the Transaction sheet, viz. nothing in the following columns:
Policy Contact Name, Policy Status, Insurer, Effective Date (Transaction), Discounted Premium, IPT Amount, VAT Amount, Discounted Premium + IPT, Policy Fee, Admin Fee, Client Amount, Commission Rate, Total Commision, VIBL Commission, Transaction Agent & Agent Commission.
Unless I'm looking at a reduced dataset here.
I haven't investigated again where the Inroducer Amounts on the result table come from, but that data won't come from the Transaction sheet.

Am I following you correctly?
Click to expand...

I have uploaded a new one which I have populated with this weeks data, looking through the PQ steps, I think this is the same prop as the one you sent.

Are the errors purely because the inv ref on the trans sheet isn't showing on the into sheet?

I will need to look into why they are not showing on both sheets as that doesn't make sense in my head.
 

Attachments

  • ERROR VERSION - POPULATED.xlsx
    137.6 KB · Views: 5
You must log in or register to reply here.
Back
Top