liamlarmour
New Member
- Joined
- May 4, 2017
- Messages
- 4
Context:
I regularly use Vlookups in my work as part of a project, but as you may be aware, they can only return one value from the lookup column. For month-end reporting I need to determine if a particular account as a record in the relevant system inboxes, if it appears in more than one workstream (we have CorVu reports run that listing all unique IDs with a lot of relevant details and then the inbox the record has been created in. We have approx 50k unique IDs (2100 of these belong to 2 or 3 workstreams and so should have a record in each relevant inbox), with approx 7000 open queries in one report, and 9000 closed queries in another. As part of my reporting, I must constantly track these every month, with one part of this matching our unique IDs from a master list to each open and closed report, to return the relevant inboxes if the unique ID appears in either report.
Raw data (yellow column is a helper col inserted to run other vlookups from) - this report is cols A-AU, I insert a helper col in which becomes the new A, so the lookup col is AV. I have hidden columns for customer privacy.
a b c av
<colgroup><col width="159" style="width:119pt"> <col width="131" style="width:98pt"> <col width="159" style="width:119pt"> <col width="218" style="width:164pt"> </colgroup><tbody>
</tbody>
I usually create a blank tab, copy out the Property Refs (unique IDs), remove duplicates and then try to obtain the details I need.
a d e f g
<colgroup><col width="92" style="width:69pt"> <col width="102" style="width:77pt"> <col width="291" style="width:218pt"> <col width="240" style="width:180pt"> <col width="347" style="width:260pt"> <col width="154" style="width:116pt"> <col width="166" style="width:125pt"> </colgroup><tbody>
</tbody>
Possible Solution:
I came across another thread from 2009 for hiker95's "VLookUpMulti" function (http://www.mrexcel.com/forum/excel-questions/439622-returning-multiple-values-through-vlookup.html) which is exactly what I have been looking for. I have tried to tweak this and I can't work out how to do it.
Reasons for tweaking:
It is a user-defined function (UDF). The code works well if it is searching within a table of only two columns, eg unique ID and lookup column. I assume this is to do with the sample data used in the thread. So in an attempt to see if it works for my means, I tried it with a smaller sample of two columns and it worked. However when I tried it looking up the values from a third column (specifying this in the formula) it still only returned values from the second column.
1) I need to redefine the VBA code so that I can return values from any lookup column up to and maybe even beyond the final column (if i was to add extra data onto a report for other reasons).
2) The code seems to return numerical data in one order and text data in another. Generally our reports are arranged in chronological order for the created records - so it would be useful to return them in the order created - then I could always retrieve data from other columns go alongside it - this may not work if the returned results are sorted in different orders depending on their numeric or text format.
3) I'm aware that UDFs may only apply to one workbook that it is created in. However I would need to use this in multiple workbooks. Is there a way around this? I've read a couple of different pages around creating it in a separate workbook on its own and saving it as an add-in, activating it and then using it that way. Is that the only, and easiest, way?
Apologies for all the questions, I'm not great at macros at the best of times, and am really trying to get into VBA, but it isn't easy when most sites sometimes presume you already know the basics around most things.
I really appreciate any help offered.
Thanks
Liam
I regularly use Vlookups in my work as part of a project, but as you may be aware, they can only return one value from the lookup column. For month-end reporting I need to determine if a particular account as a record in the relevant system inboxes, if it appears in more than one workstream (we have CorVu reports run that listing all unique IDs with a lot of relevant details and then the inbox the record has been created in. We have approx 50k unique IDs (2100 of these belong to 2 or 3 workstreams and so should have a record in each relevant inbox), with approx 7000 open queries in one report, and 9000 closed queries in another. As part of my reporting, I must constantly track these every month, with one part of this matching our unique IDs from a master list to each open and closed report, to return the relevant inboxes if the unique ID appears in either report.
Raw data (yellow column is a helper col inserted to run other vlookups from) - this report is cols A-AU, I insert a helper col in which becomes the new A, so the lookup col is AV. I have hidden columns for customer privacy.
a b c av
| Full Property Number | Full Cust Number | Full Property Number | Inbox name |
| 11504 | 0 | 11504 | E M&B Meter Under Review |
| 197706 | 0 | 197706 | E M&B Meter Under Review |
| 197706 | 0 | 197706 | E M&B SiteMeters different tow |
| 255578 | 0 | 255578 | E M&B Meter Under Review |
| 271534 | 6599300 | 271534 | E M&B Combination Meters |
| 396423 | 1968319 | 396423 | E M&B Combination Meters |
| 428535 | 2554256 | 428535 | E M&B Meter Under Review |
| 480070 | 0 | 480070 | E M&B SiteMeters different tow |
| 1024809 | 0 | 1024809 | E M&B LPS Occ Rapid Demolished |
| 1192760 | 1024193 | 1192760 | E M&B Belfast Harbour |
| 1192966 | 9162885 | 1192966 | E M&B Belfast Harbour |
| 1192966 | 9162885 | 1192966 | E M&B LPS Missing properties |
<colgroup><col width="159" style="width:119pt"> <col width="131" style="width:98pt"> <col width="159" style="width:119pt"> <col width="218" style="width:164pt"> </colgroup><tbody>
</tbody>
I usually create a blank tab, copy out the Property Refs (unique IDs), remove duplicates and then try to obtain the details I need.
a d e f g
| Rapid Prop Ref | index | List - 53897 | INBOXES | This Property appears in [No of] Workstreams | TEL CLOSED COUNT (col J) | Closed CMS Inboxes (col K) |
| 11504 | 20170428-1619 | Descoped From M2B Desktop Required | E M2B to M&B On hold | 2 | 1 | |
| 11504 | 20170428-27569 | MUR Ready For B&R | E M&B Meter Under Review | 2 | 1 | |
| 197706 | 20170428-26005 | MUR Desktop Required | E M&B Meter Under Review | 2 | 2 | |
| 197706 | 20170428-36653 | Site Meter Properties in Different Towns | E M&B SiteMeters different tow | 2 | 2 | |
| 255578 | 20170428-26013 | MUR Desktop Required | E M&B Meter Under Review | 2 | 1 | |
| 255578 | 20170428-42127 | Unmatched Properties | E M&B LPS Unmatched properties | 2 | 1 | |
| 271534 | 20170428-36674 | Site Meter Properties in Different Towns | E M&B SiteMeters different tow | 2 | 1 | |
| 271534 | 20170428-41348 | Unknown Combis 010317 | E M&B Combination Meters | 2 | 1 | |
| 396423 | 20170428-23106 | Missing Properties | E M&B Missing Properties | 2 | 1 | |
| 396423 | 20170428-41351 | Unknown Combis 010317 | E M&B Combination Meters | 2 | 1 | |
| 428535 | 20170428-7476 | Dom on Rapid, Dom Agri on LPS | E M&B LPS Dom Agri | 2 | 1 | |
| 428535 | 20170428-27581 | MUR Ready For B&R | E M&B Meter Under Review | 2 | 1 | |
| 480070 | 20170428-28556 | Occupied LPS, Demolished Rapid | E M&B LPS Occ Rapid Demolished | 2 | 1 | |
| 480070 | 20170428-36700 | Site Meter Properties in Different Towns | E M&B SiteMeters different tow | 2 | 1 | |
| 1024809 | 20170428-28843 | Occupied LPS, Demolished Rapid | E M&B LPS Occ Rapid Demolished | 2 | 1 | |
| 1024809 | 20170428-36721 | Site Meter Properties in Different Towns | E M&B SiteMeters different tow | 2 | 1 | |
| 1192760 | 20170428-10 | Belfast Harbour | E M&B Belfast Harbour | 2 | 1 | |
| 1192760 | 20170428-24172 | Missing Properties | E M&B Missing Properties | 2 | 1 | |
| 1192966 | 20170428-12 | Belfast Harbour | E M&B Belfast Harbour | 2 | 2 | |
| 1192966 | 20170428-24176 | Missing Properties | E M&B Missing Properties | 2 | 2 |
<colgroup><col width="92" style="width:69pt"> <col width="102" style="width:77pt"> <col width="291" style="width:218pt"> <col width="240" style="width:180pt"> <col width="347" style="width:260pt"> <col width="154" style="width:116pt"> <col width="166" style="width:125pt"> </colgroup><tbody>
</tbody>
Possible Solution:
I came across another thread from 2009 for hiker95's "VLookUpMulti" function (http://www.mrexcel.com/forum/excel-questions/439622-returning-multiple-values-through-vlookup.html) which is exactly what I have been looking for. I have tried to tweak this and I can't work out how to do it.
Reasons for tweaking:
It is a user-defined function (UDF). The code works well if it is searching within a table of only two columns, eg unique ID and lookup column. I assume this is to do with the sample data used in the thread. So in an attempt to see if it works for my means, I tried it with a smaller sample of two columns and it worked. However when I tried it looking up the values from a third column (specifying this in the formula) it still only returned values from the second column.
1) I need to redefine the VBA code so that I can return values from any lookup column up to and maybe even beyond the final column (if i was to add extra data onto a report for other reasons).
2) The code seems to return numerical data in one order and text data in another. Generally our reports are arranged in chronological order for the created records - so it would be useful to return them in the order created - then I could always retrieve data from other columns go alongside it - this may not work if the returned results are sorted in different orders depending on their numeric or text format.
3) I'm aware that UDFs may only apply to one workbook that it is created in. However I would need to use this in multiple workbooks. Is there a way around this? I've read a couple of different pages around creating it in a separate workbook on its own and saving it as an add-in, activating it and then using it that way. Is that the only, and easiest, way?
Apologies for all the questions, I'm not great at macros at the best of times, and am really trying to get into VBA, but it isn't easy when most sites sometimes presume you already know the basics around most things.
I really appreciate any help offered.
Thanks
Liam
