Converting data format

[PuffinPanic]

Hi All,

I've been banging my head against this for the last hour or so, and I just can't figure it out, so I'm hoping that someone can help.

I have a set of data that is in this format:

Job 1 category	Job 1 description	Job 2 category	Job 2 description	Job 3 category	Job 3 description
category 1	Job 1 cat 1 description 1	category 1	Job 2 cat 1 description 1	category 1	Job 3 cat 1 description 1
category 1	Job 1 cat 1 description 2	category 1	Job 2 cat 1 description 2	category 1	Job 3 cat 1 description 2
category 1	Job 1 cat 1 description 3	category 2	Job 2 cat 2 description 1	category 1	Job 3 cat 1 description 3
category 2	Job 1 cat 2 description 1	category 2	Job 2 cat 2 description 2	category 1	Job 3 cat 1 description 4
category 2	Job 1 cat 2 description 2	category 3	Job 2 cat 3 description 1	category 2	Job 3 cat 2 description 1
category 2	Job 1 cat 2 description 3	category 3	Job 2 cat 3 description 2	category 2	Job 3 cat 2 description 2
category 3	Job 1 cat 3 description 1	category 3	Job 2 cat 3 description 3	category 2	Job 3 cat 2 description 3
category 3	Job 1 cat 3 description 2			category 3	Job 3 cat 3 description 1
category 3	Job 1 cat 3 description 3			category 3	Job 3 cat 3 description 2

but I need it to look like this:

Category	Job 1	Job 2	Job 3
category 1	Job 1 cat 1 description 1	Job 2 cat 1 description 1	Job 3 cat 1 description 1
category 1	Job 1 cat 1 description 2	Job 2 cat 1 description 2	Job 3 cat 1 description 2
category 1	Job 1 cat 1 description 3		Job 3 cat 1 description 3
category 1			Job 3 cat 1 description 4
category 2	Job 1 cat 2 description 1	Job 2 cat 2 description 1	Job 3 cat 2 description 1
category 2	Job 1 cat 2 description 2	Job 2 cat 2 description 2	Job 3 cat 2 description 2
category 2	Job 1 cat 2 description 3		Job 3 cat 2 description 3
category 3	Job 1 cat 3 description 1	Job 2 cat 3 description 1
category 3	Job 1 cat 3 description 2	Job 2 cat 3 description 2	Job 3 cat 3 description 1
category 3	Job 1 cat 3 description 3	Job 2 cat 3 description 3	Job 3 cat 3 description 2

It's not a straightforward as just removing the category columns, because some have more descriptions for the same category as others. I have a large number of jobs and their associated descriptions so I didn't really want to do this manually - although it's taken me so long I might have been better off just doing a copy & paste job now, but I don't like to be beaten.

I'm hoping it's something simple and it's just me not spotting the obvious solution.

Any help would be appreciated.

Thanks

PuffinPanic

[ChrisTX]

See if the attached workflow gives you the correct output.  Not sure if the highlighted cells are correct in your example, or in the workflow.

When you mentioned "I have a large number of jobs", I don't know if that means you have more columns besides Job 1, Job 2, Job 3.  If you do have many columns, you could convert the middle section of the workflow to a batch macro.

Chris

[Ben_H · Accepted solution]

Hi @PuffinPanic,

Chris got there before me but here's an alternate method, that uses the transpose tool.

I got the same result as Chris - slightly different order than your target example.

Regards,

Ben

[PuffinPanic]

Hi Chris,

Sorry, that wasn't as clear written down as in my head. 

I meant that this is a subset of the jobs, so job1, job2, etc, could go on to job28 or more.

I haven't designed any macros myself before, although I have used a couple that other people have posted on the forum, so that will be interesting.

Also, the formatting of your answer is correct, it was my example where it wasn't lined up properly - the perils of copy and paste!

Thanks for the response, I will have a go today to see if I can get it working. 

[PuffinPanic]

Thanks Ben,

I'll try it out today.

[PuffinPanic]

Hi,

Ben, that solution worked perfectly. I've tweaked it a bit to make it work with my actual data, but it definitely does the job. 

Thank you both for your helpful suggestions. 

Kind regards

PuffinPanic