Extracting data with multiple matching criteria

[JCarr]

Hello there,

I'm using Excel 2007, and I have been struggling to find the correct conditional formatting for my data. What I have are 4 columns. The first column contains a Job Number(all job numbers are unique), the other three columns contain lot numbers associated with a component of the Job (these may be unique or may have duplicates). What I need to do is to show which job numbers have each and every component of the job match with another job components.


For example,

Job Number Component1 Component2 Component3
Job#1 555 AB 8A
Job#2 899 CD 8F
Job#3 555 AB 8A
Job#4 899 CD 8F
Job#5 555 AB 8Q


To clarify, all components of Job #1 match with Job #3 and all components of Job #2 match with Job #4 and Job #5 doesn't have all components matching with another because of component3. What I would need to extract (i.e. show in another part of the spreadsheet which jobs match with eachother.) I want to be able to continuously add data to this and continuously extract matches.

I've tried using Match and Index formatting, but couldn't seem to get it right.

Suggestions?

 

[Peter_SSs]

Welcome to the MrExcel board!

1. You mentioned not getting Conditional Formatting right but also showing data in another part of the sheet. Conditional Formatting will not move the data. Please clarify.

2. If jobs match, will they only ever be in pairs like these samples? Or might Jobs #1, #5 and #6 all match?

3. Do you specifically need to show which Jobs match with which or just that jobs match somewhere?

Perhaps you could help clarify all of the above, not only with further description, but also posting what you want the results to actually look like.

4. If the easiest way to achieve what you want is by a macro, is that okay?

 

[JCarr]

These Jobs will not necessarily be in pairs, there may be many Jobs which have all components match. I need to specifically show which Jobs match with which and then display the Job Numbers that match. What I want it to show is:

Job Number Component1 Component2 Component3 Matching Jobs
Job#1 555 AB 8A Job #1 Job #3 Job#6
Job#2 899 CD 8F Job #2 Job#4
Job#3 555 AB 8A
Job#4 899 CD 8F
Job#5 555 AB 8Q
Job#6 555 AB 8A


Essentially, showing that Job 1,3,6 match with each other and Job 2,4 match with eachother. I need this to be automatic, such that as I continuously add Job information it will update which Jobs match. If using a Macro is easiest, it will be okay!

 

[JCarr]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Job Number[/TD]
[TD]Component 1[/TD]
[TD]Component 2[/TD]
[TD]Component 3[/TD]
[TD]Matching Jobs[/TD]
[TD]""[/TD]
[TD]""[/TD]
[/TR]
[TR]
[TD]Job #1[/TD]
[TD]555[/TD]
[TD]AB[/TD]
[TD]8A[/TD]
[TD]Job #1[/TD]
[TD]Job#[/TD]
[TD]Job #6[/TD]
[/TR]
[TR]
[TD]Job #2[/TD]
[TD]899[/TD]
[TD]CD[/TD]
[TD]8F[/TD]
[TD]Job #2 [/TD]
[TD]Job #4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job #3[/TD]
[TD]555[/TD]
[TD]AB[/TD]
[TD]8A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job #4[/TD]
[TD]899[/TD]
[TD]CD[/TD]
[TD]8F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job #5[/TD]
[TD]555[/TD]
[TD]AB[/TD]
[TD]8Q[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job #6[/TD]
[TD]555[/TD]
[TD]AB[/TD]
[TD]8A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I hope using this table makes it slightly easier to see. Again, I need it to continuously show me which Job numbers match with which as I add more Jobs. I

 

[Peter_SSs]

Assuming data is in columns A:D & results to go into column E, try this in a copy of your workbook.

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & test.

4. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim d As Object
  Dim a, b, Itm
  Dim i As Long
  Dim s As String
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = Range("A2", Range("D" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a, 1), 1 To 1)
  For i = 1 To UBound(a, 1)
    s = a(i, 2) & "|" & a(i, 3) & "|" & a(i, 4)
    If d.Exists(s) Then
      d.Item(s) = d.Item(s) & ", " & a(i, 1)
    Else
      d.Add s, i & "%" & a(i, 1)
    End If
  Next i
  For Each Itm In d.items
    If InStr(1, Itm, ",") Then
      b(Split(Itm, "%")(0), 1) = Split(Itm, "%")(1)
    End If
  Next Itm
  Application.EnableEvents = False
  Range("E2").Resize(UBound(b, 1)).Value = b
  Columns("E").AutoFit
  Application.EnableEvents = True
End Sub

Results as data entered/edited/removed in columns A:D

Excel Workbook
	A	B	C	D	E
1	Job Number	Component1	Component2	Component3	Matching Jobs
2	Job#1	555	AB	8A	Job#1, Job#3, Job#6, Job#11
3	Job#2	899	CD	8F	Job#2, Job#4
4	Job#3	555	AB	8A
5	Job#4	899	CD	8F
6	Job#5	555	AB	8Q
7	Job#6	555	AB	8A
8	Job#7	1
9	Job#8	555	AB8	A	Job#8, Job#16
10	Job#9	3
11	Job#10	4
12	Job#11	555	AB	8A
13	Job#12	6
14	Job#13	7
15	Job#14	8
16	Job#15	9
17	Job#16	555	AB8	A
18	Job#17	11
19	Job#18	15
20	Job#19	13
21	Job#20	44	ggg	22x	Job#20, Job#21
22	Job#21	44	ggg	22x
Matching Jobs