I have an Excel 2010 spreadsheet which gathers data from an Access database, one of the tables I need to get data from is set up like so:
|UniqueID | PaymentID | ClaimID |
1 | 1234 | 5556 |
2 | 1234 | 5557 |
3 | 1235 | 5558 |
4 | 1236 | 5559 |
5 | 1236 | 5560 |
What I need though is for the ClaimID's to be concatenated onto the same row based on the PaymentID so it should look something like this:
|UniqueID | PaymentID | ClaimID |
| 1 | 1234 | 5556, 5557 |
| 2 | 1235 | 5558
| 3 | 1236 | 5559, 5560 |
I've tried this using VBA code which worked, however it took too long to process. I have tried PowerQuery which also worked but the rest of the business don't have it installed, so won't work in the long run. My final thought was to use the SQL in connection options to manipulate the data, but I'm not sure how. I've searched and tried the following functions: ConcatADO, Concatlist, Stuff and Group_Concat but have not had any luck (maybe i'm using them wrong?).
Other information that might be helpful:
There is about 40,000 rows of data coming from the database on this particular table. While I use Excel 2010 some users will be using Excel 2007, 2010, 2013 and 2016.
Is there a way to achieve the concatenation of ClaimsID rows based on the PaymentID using SQL or a different method that I may not have thought about?
Thanks in Advance.
For anyone else who may have this issue, I couldn't get the SQL way to work in the end so I had to go for something far less elegant.
I pulled the data through using SQL and added two helper columns onto the Excel data table. Before applying formulas - make sure the data is arranged in (for my instance) PaymentID order lowest to highest - in the first helper (named "ClaimIDConCat") column I placed this formula:
=IF([@PaymentID]=OFFSET([@PaymentID],-1,0),OFFSET([@ClaimIDConCat],-1,0)&", "&[@ClaimID],[@ClaimID])
This provides me with an ever growing list of ID's related to the payment. In the second helper column (named "FinalRow") i used this formula:
=IF([@PaymentID]<>OFFSET([@PaymentID],1,0),"FinalRow","NotFinal")
This will simply look to see if the row underneath is the same as the current row - if it is the same "NotFinal" is placed in the cell if it is "FinalRow" is used.
Finally my final piece was to pull the data into another table using a simple INDEX() MATCH() array formula as such:
{=IFERROR(INDEX(Tablename[ClaimIDConCat],MATCH(1,([@RelatedID]=ClaimIDData[PaymentID])*("FinalRow"=Tablename[FinalRow]),0)),"N/A")}
This provided me with the overall required output.