Finding correlations between two columns of data where there are duplicates in both columns.

nassman

New Member
Joined
Jun 4, 2015
Messages
3
Hi,

Have just started a new role and am looking for some help with what you wizards will probably consider very basic level stuff..

I basically need to be able to correlate data from two columns that are set up like this:

Work Order ReferenceSchedule Rate Item
1001car11
1001rec12
1001rec15
2001star11
2001rec12
3001car11
3001rec12
3001rec15
3001rec20

<tbody>
</tbody>


And for example, I need to find all Work order reference numbers which include both schedule rate items rec12 and rec15.

Also, will be dealing with huge volumes of data so unsure if a pivot table will be more efficient or if I should be using a function.

Thanks in advance, forgive my ignorance!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Hi, wouldn't a pivot do the trick?

Excel 2013
ABCDEF
1Work Order ReferenceSchedule Rate Item
21001car11Row LabelsCount of Work Order Reference
31001rec1210012
41001rec15rec121
52001star11rec151
62001rec1230012
73001car11rec121
83001rec12rec151
93001rec15
103001rec20
nassman

Here the pivot is set with a Value filter greater than or equal to 2.
 
Upvote 0
Or you can change layout to see only the SRI...

Excel 2013
EFG
2Count of Work Order ReferenceColumn Labels
3Row Labelsrec12rec15
4100111
5300111
nassman
 
Upvote 0
I should have been more specific.

What i'm hoping to achieve is a compiled list of all the individual work order references where two (or more) specified schedule rate items appear.

I'll be dealing with 10's of thousands of work orders so what I'm hoping for is a column or new spreadsheet where only the work orders which do contain those specified schedule rate items are displayed.

They're basically contractor codes for individual jobs, and some job codes (aka Schedule rate items) cannot be used in conjunction with others. For auditing purposes i'm trying to find the quickest way to identify and report on those specific work orders that do contain conflicting schedule rate items.
 
Upvote 0
I would have still used a pivot table, and places the schedule rate item in the filter field thus creating a one (1)column list...
 
Upvote 0
My suggestion would be to create some helper columns to cope with the large amount of records.
Assume the SRIs you are looking for are in H2:H4 and data is in row 2:50000.

Helper column C (header e.g. “WO#SRI”) with WO# and SRI concatenated. Enter in C2 and copy down.
Code:
=A2&B2

Helper column D (header e.g. “Found1”). Enter in D2 and copy down.
Code:
=$B2=$H$2

Helper column E (header e.g. “Found1-2”). Enter in E2 and copy down.
Code:
=IF(D2,IF($H$3<>"",ISNUMBER(MATCH($A2&$H$3,$C$2:$C$50000,0)),TRUE))

Helper column F (header e.g. “Found1-3”). Copy E2 to the right to F2, change $H$3 in $H$4 (twice) and copy down.
Code:
=IF(E2,IF([B]$H$4[/B]<>"",ISNUMBER(MATCH($A2&[B]$H$4[/B],$C$2:$C$50000,0)),TRUE))

This will give you TRUEs in column F for WOs with search item1 that also include the other 2 search items. If you filter on this column you will only get the records with item1, but those WOs also include the other 2 items.

This will also work for only 2 search items (in H2:H3).

If you would have more than 3 search items, insert additional columns G, H etcetera, copy F2 to G2, change $H$4 in $H$5 twice, etcetera.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top