Is Data Merge the tool I'm looking for?

[RJNMS]

Hello Adobe Community,

I've fairly recently been introduced to data merges and it has improved my workflow drastically for some processes but I'm not afraid to admit that I only have basic knowledge of InDesign and it's tools.

I have a design to deal with that I feel data merge is appropriate for, but I cannot fathom to get the correct result.

To summarise, I am creating various pages to be printed however I need to keep some sort of order according to my original data, in which, i've tidied up to create my own data merge.

C1-R = Category 1, Red

C1-B = Category 2, Blue - etc in reference to my template design.

The above is my current point, to avoid mixing up the 'categorys' so to say, I am only using my C1 columns on each variation. The table does however have empty spaces, as not all colours have the same numbers or amount. Running the data merge producing these empty pages even with ignore blanks ticked. 

This isn't too much of an issue, but I have tried creating 'alternate layouts' but the merge does not work the way I'd hoped, and instead generates all copies into the Yellow section.

Obviously, I was hoping the reds, in my data as C1-R would go into the template for C1-R etc but this is not the case.

Is there a way around this? I'm unsure what I'm getting wrong, and whether a data merge is actually the tool I need to be using to achieve what I'm aiming to achieve efficiently...

[Joel Cherney]

I'm going to confess that I have no idea what you're trying to do with your merge. I've read your post a few times now and can't figure out your goal. But before I ask you what you're trying to make, I'd like to know why you're using Alternate Layouts. I don't really work with Alternate Layouts myself, but I read in the HelpX documentation that the main purpose of alternate layouts is to make it easy to work with "multiple page sizes, orientations, or aspect ratios." Is that what you're trying to do? If so, this other bit I chopped out of the documentation that says (emphasis mine):  

STEP 1: Choose a target device and its corresponding dimension and orientation. Then create your primary layout for all the pages.

inclines me to suggest that you should probably run your merge before creating Alternate Layouts.

If you don't need to publish to multiple page sizes or orientations or aspect ratios, perhaps you can describe to us the final product you're working towards, and we can either help you figure out how to use Data Merge to get there, or perhaps suggest a third-party plugin that would get you whatever it is you're trying to get out of Alternate Layouts. 

[RJNMS]

Hi Joel,

Many thanks for the reply - reading back at my post I will try to clear up what I'm suggesting. It would seem alternative pages likely isn't what I need to be using either so thanks again for that, but my intention was to group my results into colour results. This is what I'm currently getting after removing alt layouts:

Almost as if I want to change the data merge from reading coloumn by coloumn and instead produce a result row by row, so that all the red results are together, all the blue are together and so on. In terms of resultd Pg 1 would be followed by the data from Pg 5.

It's more a time saving question, as there's over at least 3000 individual items and as there are some empty rows in some columns, a few hundred more with blank results thrown into the mix, so I don't think I can change the format of my actual data.

[Colin Flashman]

I'm with Joel here - I am having difficulty understanding the brief.

Perhaps can we look at this challenge another way - instead of showing us what has been done to this point, tell us - in detail - what your brief is and what you would like to achieve?

Data Merge is a powerful - but limited - tool within InDesign. I can get it to do things which shouldn't be possible and have documented my findings over at the CreativePro website, and am interested to see if there are some techniques that you could use in your brief... but if your brief can be explained in the most layman's terms, that would go a long way to seeing if we could assist you.

[RJNMS]

Hi Colin,

Apologies i'm still not making sense, I'm struggling to explain the desired outcome.

It's a bit more complex due to how the client wishes to see the final data, but I have x6 groups of variable data, that fits into 4 designs. 

Above is a sample of the data sent to me, the full project makes 1085 individual prints. The way I've set up the data merge is as into 'cells' (C1 = Cell 1 as per client data). Then the various colours as below:

My final result for Cell 1 (uploaded PDF) works and matches my data, but ideally I need to get my document to have all red designs together, blue together and so on. 

I will certainly take a look at your blog/CreativePro - as I believe that I'm going to end up getting lots of data that may have various designs such as this brief in future.

[Robert at ID-Tasker]

Could you please stop posting "fake" data - and show us REAL example of what you're trying to achieve? 

Because, right now it's one field - then it will be multiple and we will have to start all over again... 

No offence, but right now you're just wasting your time and ours...

[RJNMS]

Hi Robert, a real example is attached in the above and this is unfortunately the data I am working with. The 'samples' are to try and reduce the full range and samples but I appreciate that the post isn't clear.

[Joel Cherney]

I think I almost get it. Your end product is just a card or ticket with a value from the Excel file and a colored border, right? And your client gives you raw data in Excel format; you're adding the colors in Excel, you're adding the header row with C1-R, C2-R, correct? Then you're trying to run a single merge in a single document to generate all four colors of card/ticket/whatever?

A few suggestions that might make this easier:

1) Stop trying to do it all in one InDesign document. You have four colors, so you'd use four INDDs, one per color. There's no harm whatsoever in using the same data source for four separate INDDs. 

2) If you need to keep the colors grouped together, then why in the world do you need the data in separate columns? A bit of VBA that ganged up all red columns into a single column doesn't sound that hard to do. Then your merge would be much simpler. 

3) Client's source data looks a bit WTF to me. Are you looking at all of the various AA###s in that Red cell under "number range" and then... keying it in by hand? Please tell me you're not keying it in by hand. That looks like another case for data massage in Excel; a spot of VBA could parse all the Red cells and find all the values and expand them into a list, which would then give you the Red-only data source.

4) On the offhand chance that you actually don't want to explain 100% of what you're trying to do, I often have success sending people in your shoes over to Em Software to look at InData. It sounds like it's more robust than you'd actually need, but if my above suggestions are not useful for your workflow (say, if you already have a zero-effort way to turn your client's submissions into your group-colored Excel data source, and you don't want to touch that at all), and you like taking a programmatic approach to building documents, and it'd be faster to learn a new scripting language than to fix your Excel workflow (and/or you hate VBA and refuse to touch it) then a tool like InData might be the ticket. 

[RJNMS]

Hi Joel,

Thanks for baring with, almost there. Correct on the data front, I'm using InD to add the border and categorising the colours by using columns in excel. The data screenshot in my above post is from my client is CELL 1 as they call it, which is column A-D in my reorganised data below it. Here's the full data from my clients end:

Your 3rd point seems it could be on to something but data merging isn't really my every day field and neither is Excel, it's more of a one off job that may reoccur, so trying to get a good process in, so I will look into that. I thankfully didn't single handedly type in the ranges.

I actually have 6 cells of data like the above, so I figured creating multiple INDDs would get a bit cumbersome if I was to make a file for each colour. The inconvenience is that although I want to group all the reds together, I still have to provide them to my client in cell groups - this is why in my excel sheet I have so many columns, essentially 1-4 for each 'cell+colour' and then each row is the actual data.

[Robert at ID-Tasker]

And NOW we are getting somewhere... 

So you just need to create single/separate "signs" - with names extracted from column "G" - grouped & styled/formatted by color - column "E"?

[Colin Flashman · Accepted solution]

I had a look at the PDF that was uploaded and have re-read the brief.

To make sure I'm understanding it correctly, here's what I have:

* The client gives you a variable data range (that hopefully you don't have to key in manually after you have the ranges).

* From these ranges, some have to be specific colours.

* You want specific ranges to be grouped in their colours.

* You want to do this in one file.

If I have this right, I'd make the data as follows:

Code,color

AA01,red

AA02,red

AA03,red...

and so on.

Then once that range is done, in the same database you can have the next records as

BA01,blue

BA02,blue

BA03,blue...

and so on.

Now, the catch is how to change colours during the data merge. It can be done though it isn't in the help guide. This post describes how to achieve the technique but it requires knowledge of GREP styles: https://creativepro.com/changing-colors-during-a-data-merge/

Otherwise, you can make a solid patch of colour as a graphic and instead of the second column of the database being the name of the color, it can refer to the graphic that contains the color e.g. red.pdf or red.ai

Let me know if I've understood this properly or not.

[Robert at ID-Tasker]

Or, as mentioned earlier by @Joel Cherney - 4x different INDD files / templates...

[RJNMS]

Many thanks Colin, spot on with this. I haven't explored GREP styles before but, did some searching, read some posts and visited the page above and with some tweaks to my copy of the data I've been able to get to my end goal.

Much appreciated!