Long list, 2 criteria, find unique, paste
 

Is it possible to have a long list over many worksheets as follows;

Worksheet1 -Monday
Job Code Data1 Data2 Data3
Bob 101 8 5 12
Bob 940 12 1 3
Dale 101 2 4 7
Bob 101 1 3 2

Worksheet2 -Tuesday
Job Code Data1 Data2 Data3
Tim 101 8 3 4
Bob 145 4 9 2
Tim 101 4 1 3
Bob 101 5 2 8

Etc...
This data changes day to day by the way.

I would like this data to simplify to a unique list including Job & Code as
criteria. The above would simplify to:

Worksheet3 -print week
Job Code
Bob 101
Bob 940
Dale 101
Tim 101
Bob 145

This would then be put onto another worksheet (print week) in the form above.

Is this possible to do this so that if you change the data in the worksheets
-Monday and Tuesday the Worksheet3 -print week automatically & dynamically
changes also?

Note that the list in Worksheet -print week would get smaller or longer
depending on the data in Worksheets -monday and tuesday. Also note that
there are 40+ names and 10+ codes.

Thanks.

Howard Leung

Long list, 2 criteria, find unique, paste
 

What you need, my friend, is a Pivot Table. Pivot Tables can consolidate
multiple ranges into one summary table for you, and you can use entire
columns as ranges. To make it update whenever data is changed, use this
little bit of VBA code (copy it, right-click your Pivot Table worksheet and
select View Code, then paste this in there):

Private Sub Worksheet_Activate()
Me.PivotTables(1).PivotCache.Refresh
End Sub

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Howard Leung" wrote:

Is it possible to have a long list over many worksheets as follows;

Worksheet1 -Monday
Job Code Data1 Data2 Data3
Bob 101 8 5 12
Bob 940 12 1 3
Dale 101 2 4 7
Bob 101 1 3 2

Worksheet2 -Tuesday
Job Code Data1 Data2 Data3
Tim 101 8 3 4
Bob 145 4 9 2
Tim 101 4 1 3
Bob 101 5 2 8

Etc...
This data changes day to day by the way.

I would like this data to simplify to a unique list including Job & Code as
criteria. The above would simplify to:

Worksheet3 -print week
Job Code
Bob 101
Bob 940
Dale 101
Tim 101
Bob 145

This would then be put onto another worksheet (print week) in the form above.

Is this possible to do this so that if you change the data in the worksheets
-Monday and Tuesday the Worksheet3 -print week automatically & dynamically
changes also?

Note that the list in Worksheet -print week would get smaller or longer
depending on the data in Worksheets -monday and tuesday. Also note that
there are 40+ names and 10+ codes.

Thanks.

Howard Leung

Long list, 2 criteria, find unique, paste
 

That worked wonderfully. And such a simple solution too. Thanks kindly. Let
me know if I can help you with anything.

Howard Leung
TQ Construction
Construction Manager
604 430 9900

"KC Rippstein" wrote:

What you need, my friend, is a Pivot Table. Pivot Tables can consolidate
multiple ranges into one summary table for you, and you can use entire
columns as ranges. To make it update whenever data is changed, use this
little bit of VBA code (copy it, right-click your Pivot Table worksheet and
select View Code, then paste this in there):

Private Sub Worksheet_Activate()
Me.PivotTables(1).PivotCache.Refresh
End Sub

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Howard Leung" wrote:

Is it possible to have a long list over many worksheets as follows;

Worksheet1 -Monday
Job Code Data1 Data2 Data3
Bob 101 8 5 12
Bob 940 12 1 3
Dale 101 2 4 7
Bob 101 1 3 2

Worksheet2 -Tuesday
Job Code Data1 Data2 Data3
Tim 101 8 3 4
Bob 145 4 9 2
Tim 101 4 1 3
Bob 101 5 2 8

Etc...
This data changes day to day by the way.

I would like this data to simplify to a unique list including Job & Code as
criteria. The above would simplify to:

Worksheet3 -print week
Job Code
Bob 101
Bob 940
Dale 101
Tim 101
Bob 145

This would then be put onto another worksheet (print week) in the form above.

Is this possible to do this so that if you change the data in the worksheets
-Monday and Tuesday the Worksheet3 -print week automatically & dynamically
changes also?

Note that the list in Worksheet -print week would get smaller or longer
depending on the data in Worksheets -monday and tuesday. Also note that
there are 40+ names and 10+ codes.

Thanks.

Howard Leung

Long list, 2 criteria, find unique, paste
 

If you can take this estimated $300k budget home plan and do it for half
that, well that would be just dandy. :)
Thanks for the feedback. Glad I could help!
-- KC


"Howard Leung" wrote:

That worked wonderfully. And such a simple solution too. Thanks kindly. Let
me know if I can help you with anything.

Howard Leung
TQ Construction
Construction Manager
604 430 9900

"KC Rippstein" wrote:

What you need, my friend, is a Pivot Table. Pivot Tables can consolidate
multiple ranges into one summary table for you, and you can use entire
columns as ranges. To make it update whenever data is changed, use this
little bit of VBA code (copy it, right-click your Pivot Table worksheet and
select View Code, then paste this in there):

Private Sub Worksheet_Activate()
Me.PivotTables(1).PivotCache.Refresh
End Sub

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Howard Leung" wrote:

Is it possible to have a long list over many worksheets as follows;

Worksheet1 -Monday
Job Code Data1 Data2 Data3
Bob 101 8 5 12
Bob 940 12 1 3
Dale 101 2 4 7
Bob 101 1 3 2

Worksheet2 -Tuesday
Job Code Data1 Data2 Data3
Tim 101 8 3 4
Bob 145 4 9 2
Tim 101 4 1 3
Bob 101 5 2 8

Etc...
This data changes day to day by the way.

I would like this data to simplify to a unique list including Job & Code as
criteria. The above would simplify to:

Worksheet3 -print week
Job Code
Bob 101
Bob 940
Dale 101
Tim 101
Bob 145

This would then be put onto another worksheet (print week) in the form above.

Is this possible to do this so that if you change the data in the worksheets
-Monday and Tuesday the Worksheet3 -print week automatically & dynamically
changes also?

Note that the list in Worksheet -print week would get smaller or longer
depending on the data in Worksheets -monday and tuesday. Also note that
there are 40+ names and 10+ codes.

Thanks.

Howard Leung