Code To Look At Number In AD And If Any Descriptions Are Different In AE Then List Them On Sheet 2

[Dazzawm]

Hi. I need a code that will tell me if there are any descriptions that are different to a particular number. In the sample below you can see there is some data in AD. When the a number is found on sheet 1 and the description is different in AE anywhere else on the sheet then I need these copied to sheet 2 like the second example. Thanks.

Excel 2010

	AD	AE
1	Part	Description
2	TX1234	Example
3	TX1234	Example
4	TX1234	Example 1
5	TX1237	Example 2
6	TX1237	Example 2
7	TX1237	Example 2
8	TX1240	Example 3
9	TX1240	Example 4

<tbody>
</tbody>

Sheet1​

As you can see rows 5, 6 & 7 all match so they don't need copying, the others do because there are different descriptions to the same part number.
Excel 2010

	A	B
1	Part	Description
2	TX1234	Example
3	TX1234	Example
4	TX1234	Example 1
5	TX1240	Example 3
6	TX1240	Example 4

<tbody>
</tbody>

Sheet2​

I must add they will not be grouped together as above, the first number may be row 2 then not appear again until row 100,002 for example.

 

[Dazzawm]

Didn't say a line the error just appeared on screen after I started the code. I couldn't debug.

 

[Fluff]

In that case I have no idea what's happening. this is what I get using your test data from post#1

Excel 2013 32 bit
	A	B	C
2	TX1234	Example	$A$2, $A$3
3	TX1234	Example 1	$A$4
4	TX1240	Example 3	$A$8
5	TX1240	Example 4	$A$9
Sheet2

 

[Dazzawm]

Tried it on another file this time it came up with a visual basic '400' error. Not to worry I'll make do with the first one. thanks.

 

[Dazzawm]

Don't suppose you could take a look at this thread could you please Fluff, its tricky to explain.

https://www.mrexcel.com/forum/excel...a-range-before-previous-date.html#post5060551

 

[Dazzawm]

Hi Fluff this works great in #2 . At the moment it has an offset of 1 so it looks in AE for mismatches. Is it possible to have an input box so I can enter the column it needs to look at please?

 

[Fluff]

How about

Sub CheckParts()

   Dim Dic As Object
   Dim Cl As Range
   Dim Ky As Variant
   Dim V1 As String, V2 As String
   Dim Offst As Variant
   
   Offst = InputBox("Please enter the offset")
   If Offst = "" Then Exit Sub
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Sheet1")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         V1 = Cl.Value: V2 = Cl.Offset(, Offst).Value
         If Not Dic.Exists(V1) Then
            Dic.Add V1, CreateObject("scripting.dictionary")
            Dic(V1).Add V2, Nothing
         ElseIf Not Dic(V1).Exists(V2) Then
            Dic(V1).Add V2, Nothing
         End If
      Next Cl
   End With
   For Each Ky In Dic.Keys
      If Dic(Ky).Count > 1 Then
         With Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
            .Resize(Dic(Ky).Count).Value = Ky
            .Offset(, 1).Resize(Dic(Ky).Count).Value = Application.Transpose(Dic(Ky).Keys)
         End With
      End If
   Next Ky
      
End Sub

 

[Dazzawm]

Sorry for late reply. It didn't seem to work. When I run it and entered column AJ in the input box for example it listed every number that was in AD on sheet 2 where it should only list the ones if there is a mismatch in AJ? (I did change from column A to AD in the code).

 

[Fluff]

You need need to enter the offset, not the column letter. So to use AJ you would need to enter 6

 

[Dazzawm]

That worked, no chance it can be column letters?

 

[Fluff]

Will the start column ever change?