merge/blend fields in records?

[lostipod12]

Hello,

I can think of non-elegant ways to achieve what I'm going after; I'm here to ask if there is an elegant way to meet my goal.

In the excel screenshots below, the first table is what I'm starting with:

and the second table is what I want to achieve:

Explanation: I have some data which came in as the result of two different processes, and I need to combine the results.

In the first table, I have a sample of 3 records which are sent to me as a union of the two processes, called CONDITION 1 and CONDITION 2.

Notice that Record 1 has a value in CONDITION 1, and Record 2 has a value in CONDITION 2. They both have the same values in the first 3 fields.

Notice that Record 1/2 is different than Record 2 in that they have different values in the second field.

What I want to do is to group the two records where the first three fields are the same, then do some sort of merging so that the null values get replaced by whatever non-null value lives in the same field in a different record. The resulting record would look like the first record in the second screenshot.

I tried digging around the crosstab tool and came up with comically bad results.

Thanks,

Nick

[SPetrie · Accepted solution]

Try transposing it first and then doing the crosstab. Using my dummy data I believe I replicated what you are trying to do. I wasnt sure if the two columns before condition 1 were static or not so I guessed at which were our key columns.

[lostipod12]

@SPetrie ,

Wow, this is incredible, I need to make this combination of Transpose + Cross Tab a regular part of my arsenal! That worked a treat, thank you so much.

Oddly, I did get a few records where there were multiple PASS values concatenated in the CONDITION_1 column (which I think should be expected, seeing how the 'Concatenate' tick box was activated):

Do you know if there is a way to summarize/group-by values while they're being concatenated by the Cross Tab tool?

Thanks,

Nick

[SPetrie]

No, it wont really do what you are wanting for that. Your best bet is to switch from 'concatenate' to 'first' and it will grab the first of the two PASS values instead.