OK CJ, I've got this figured out. And while there may be another, perhaps easier, manner by which to do this, here's mine. And it should take less time to implement than to read my directions.
Right, first step: capture the fields so that the ItemID values appears in each row in the table. Now add a calculated field named Row using RowNo(). Create a project export to send the table to an Excel file. Save the model and project files.
Now create a new Monarch model using the report file. Add an external lookup to the new Excel file, importing Row as MainRecordRow. This will be the row number of the first instance of new ItemIDs. You need to import the result of RowNo() calculations as values because fields using the RowNo() function cannot be used later in a summary. This provides a workaround.
Create a new formula-based calculated field named ColorNumber using:
Now for each ColorNumber field that you need, like Color1, Color2, ColorN you need, create a formula-based calculated field using formulas similar to:
changing =1 to =2 as necessary. I used three fields for your example.
This will give you different color names for the different color numbers, but we need them to be in the same row, not spread across the three rows of your ItemID 1.
Here comes the fun part.
Create a new Master Colors summary. Use ColorNumber as the first key field, and ItemID as the second key field. Add Color1, Color 2, Color3 as item fields. No need for a measure field.
Now create a project export. Export the summary Master Colors. Check the box so that each distinct value of the first key makes a separate file or table. Select Automatic naming by tables. Send the output to a new Excel file. Overwrite when exporting. Run the export.
Now in this same model, go back to the table window and add external lookups, one for each color. Hopefully you don't have more nine, as I think that's the limit for external lookups. Going with the assumption that you're good with less than nine, we'll continue.
Create a FinalColor1 external lookup. Link to the latest export using the _1 sheet. ItemID links to ItemID. Import Color1 as FinalColor1.
Do the same for numbers 2 and three, changing sheets and imported fields as necessary.
Now you'll have the Red, Green, Yellow values all in one row for ItemID 1, along with the other fields.
Whew. Getting there.
Now build a new summary, Final Colors. Set the filter to ColorNumber. ItemID is your key field, and I'd add FinalColor1, FinalColor2 and FinalColor3 as item fields.
Rename the FinalColor fields to display Color1, Color2 and Color3, to better match your requirements, and you're done.
If there's an easier way, I haven't found it yet.
Depending upon how many 'Colours Columns' you may have (a large number may be impractical) and alternative approach would be to take your initial 2 column table and make it a Summary too. Then set the first column field property to 'Suppress Duplicates'. Export to a fixed column width report file defined with the columns to be the max width you are ever likely to see for them.
Now read in the new report to a new session making the populated first column the subject of the detail trap. For the 'colors column' you have can trap all rows in a single multi-line field. From that point you can create calculated fields to separate the multi-line field into individual columns using the TEXTLINE() function or, possibly, the split function(s) if they will deal comfortably with whatever the real data in the 'Colors' column happens to be.
Alternatively it may be that you multi-row field capture gives you the key you need directly once you have removed or substituted and spaces by converting it to another calcualted field.
Does any of this help or have I missed something important as I read the scenario?
If you needed something that provided some form of tag association with the color columns (i.e. each column is named for a color and that column is ALWAYS that color) then a slightly different approach would be required.
I would assume that for long term consistency the original table color column would be best sorted into a know 'color' order prior to export.
Hello MonUserCJ, hello Grant, hello Kruncher
This is quite a common requirement - especially when trying to join two data sets where there isn't an easy set of joining fields. It would be nice if Monarch could let us manipulate data like this in one model on the fly, instead of needing to export a summary and read it back in as a lookup. Perhaps we should add this a Future Features request?