concatenate, sum, and remove duplicates

Hello! I'm hoping that someone with macro experience can help me solve the below listed problem - which can be summarized by concatenation of alpha, summation of numeric, and duplication removal within a single set of records.

I have a job of evaluating health claim denial data from 3 sources - Clearinghouse, Payor front end (Routed from Clearinghouse), and 835 ERA (mined from a practice management software system). From the data that is presented from the clearinghouse (clearinghouse edits, and payer front end), these denials are claim specific - that is, data is "rolled up" into one record specific to the encounter or claim ID. So, if we reported a denial for a claim that had 4 line items, it would display one record, and all numeric values would be summed - alpha datafields would be concatenated into a singular cell, for corresponding line items. Therefore, each record represents one claim. In contrast, the 835 denial data that comes from the practice management system is line item specific, which gives us multiple records for any one claim. In order to correctly report these denials, I need standardized data - which means that these 835 denials must be manipulated in some fashion to match the layout from the other 2 sources.

I have tried to use a pivot to combine, but it will not concatenate the 835 sourced data.

Because I've seen data issues like this be solved in Excel, I assume that a VB Macro could be written to offer this solution. I could employ this each time the data is analyzed (daily) and it would offer a useable workaround. Because writing VB Macros are outside of my expertise, I need for a developer to offer some insight. Please see attached spreadsheet of test data that represents the current layout of the 835 output. Please let me know if I can further clairify.

Many thanks!

Hello jmerry1,

Welcome to the Forum!

I didn't see an example of the record after all the operations had been performed on the record. Can you post an example?

Hello jmerry1,

The attached workbook has the macro below to condense the information. "Sheet2" is formatted the same as "Sheet1" and has a button to run the macro. The condensed results are copied to "Sheet2". The original information on "Sheet1" is not changed.

Please Login or Register  to view this content.