Grouping information into one Row

[rickn]

I have a Query called Jobs, which only shows ACTIVE Jobs.
This is linked to a Query called Job Operations. The Work Centers field from Job Operations table are only shown if the status is not Completed.
Every Job can have many Wokr Centers to be Completed.
I need to see all the jobs listed, then for each job list all the Work Center remaining to be completed, but on just one row and not.have the job repeated for each Work Center that is not completed.Example:
Job # Work Center
123456 CNC/MILL/DRILL/PACK/SHIP

[JBeaucaire]

There's a workbook for us to look at that goes with this question?

Click GO ADVANCED and use the paperclip icon to post up your workbook.

[rickn]

Please see the attached workbook. For example: Job # 1000 has 5 Work Centers, hi-lighted in Red, (SawCut-CNC Lathe-Deburr-Assemble-Inspect). I need to have Job # 1000 only listed once, in a row, and the Open Work Centers of (SawCut-CNC Lathe-Deburr-Assemble-Inspect) be shown. I hope this makes sense, and THANK YOU for any advise.

[JBeaucaire]

My apologies...this is an ACCESS question?

I can offer you an Excel solution, a macro to fix the data. Would that be OK?

[rickn]

No need for an Apology. Are you indicating that Excel is unable to do this, and it is really better suited by Access?

I'm very open to an Excel Solution for the work book, please feel free to send your thoughts and advise.

[JBeaucaire]

No, this is the ACCESS forum. That's what threw me. If this is an Excel question for sure, I can offer you a macro to accomplish your reformatting.

[rickn]

My apologies - I meant to asked the question in the excel forum and entered it in the access instead. Please your macro would be much appreciatted.

[shg]

Thread moved.

[JBeaucaire]

I'll ask a moderator to move this for you. Be sure to use the USERCP link at the top of the forum to see all your open threads, in case your link to this thread stops working.

I can write a quick macro to merge the items in column J for matching column A entries. You want the data reduced to one line per column A value? That's fine for job 1000, all the cells in other columns are the same.

But what about Job 1100? The values in F, G, H and I are all different. What then? Job 1200, too, and Job 13035.

[rickn]

All Jobs need to have the same macro as in Job 1000. So all Jobs need to be listed just once in a ROW, with the Column J for a matching column A. The information in column J for each row needs to list those Work Centers in Sequence Order (with ) being the lowest and being ascending. I have update a new Work Sheet as an example.
I hope this makes sense.

[JBeaucaire]

You don't seem to understand my questions, and you provided more data with no example results.

Oh well, you can try this and see if it's what you're after:

Please Login or Register  to view this content.

[rickn]

Here is a sample with detailed information.

[JBeaucaire]

Another:

Please Login or Register  to view this content.

[rickn]

I inserted the code into a Macro and ran.
Run time error: '4004;
AutoFileter method or Range class failed.
I clicked debug and this line failed......Range("C:C").AutoFilter Field:=1, Criteria1:="="

Any thoughts?

[JBeaucaire]

Here's your sheet back, I've run it on this wb twice...takes about 90 seconds but it works.

[rickn]

Thanks, not sure why it exhibits the ....Runtime error 1004, AutoFilter method of Range class failed.... , on my WB though.

[JBeaucaire]

Is it a different wb than the one you posted? If it's still doing that, then post it up, I'll take a look.

[rickn]

I used the WB that you uploaded too and the same error is shown.

[JBeaucaire]

How very odd. Since it's working for me, and it's a technique I use frequently...not sure what to offer next.

Maybe END the macro at that line where it fails.
Turn on your macro recorder
Let it record YOU filtering column C by "blanks"
Turn off recorder and examine the code created. Is it significantly different than the line I gave you?

Post the resulting code here.