0

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.

Yagami Light
  • 1,756
  • 4
  • 19
  • 39
mr_crocodilian
  • 1
  • 4
  • not familiar with access database, but in sql server, you could use `FOR XML PATH` together with `STUFF` function, the link may give you some ideas: https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server – LONG Jul 12 '17 at 12:28
  • Possible duplicate of [Concatenate Rows in Microsoft Access](https://stackoverflow.com/questions/17350055/concatenate-rows-in-microsoft-access) – Gustav Jul 12 '17 at 20:24
  • Hi @LONG thanks for the link - I've continued to look at what is available there but nothing has worked so far. – mr_crocodilian Jul 13 '17 at 08:04
  • @Gustav This isn't a duplicate of the question you've posted, as I want to have the concatenate happen in Excel only, but be completed by the SQL that allows the data to come from Access to Excel. I also can't use VBA as it takes too long to process unfortunately. – mr_crocodilian Jul 13 '17 at 08:04

2 Answers2

0

In normal sql something like: SELECT PaymentID , GROUP_CONCAT(ClaimID) GroupedName FROM table_name GROUP BY PaymentID

Should Work, In Postgres SQl something Like:

SELECT PaymentID , array_agg(ClaimID) FROM table_name GROUP BY PaymentID

Should Work

Jon.G
  • 127
  • 2
  • 11
  • Hi, thanks for the help but neither option works I get the error message: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'array_agg' in expression. – mr_crocodilian Jul 13 '17 at 07:50
0

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.

mr_crocodilian
  • 1
  • 4