I hope I'm understanding what you're asking.
Could you just create two new calculated fields Rpt_Amt1 and Rpt_Amt2 and define them as:
That should give you something like:
I'm not sure if that's what you were getting at. Could you show what you'd like the data to wind up looking like?
This is how the data comes in:
this is the expected results:
Hope this helps, thanks for your help.
There are a couple of approaches to this that work using an easy 2 stage process. What you need is an approach that makes each SSN a single record after complete extraction to the table. From there a couple of calculated fields would be all that is required in the second stage of the process.
So you could either set up a first simple model that reads the incoming data and simply outputs the columns to a new file but skipping duplicate entries for the SSN column. That makes it easy to make each SSN a single multi-row record form which a suitable table can be created.
The new output would look like this;
Alternatively use a Summary for the same effect making the "Source" field an 'across' field to turn it into columns by creating fields that specifically pick just the B or D or E data. Then export the Summary (which should now have one row per SSN) and use that exported file with a simple second model to provide the source data for the final output required.
If this is to be a regular task a quick batch script (or program if you cut code) can automate it for you.