Aligning 2 Columns of data in individual rows or better way to compare

[tempo896]

So I have a dilemma and I have made it work before but for some reason I can't remember how I did it or where I saved the file that i did it in before.

I would like to align two columns of data side by side. one column has less rows than the other so I need a macro that will move the one columns data to align with its corresponding math in the other. Example below.

The number next to each part number needs to go with that part number as well seeing how thats the number i am comparing anyway. We did a physical inventory and this is me comparing the 2 columns of data. I'm basically looking for any discrepancies between there data and our data. The physical discrepancies are few and I can check those no problem by searching. If someone can show me a better way to compare these two lines of data I am all ears but if not aligning them is the next best thing. FYI, column 1 has over 5000 part numbers (our customer who for some reason doesn't seem to know which warehouse has what parts), column E has only about 1700.

A B C D E

There #s	Qty		Our #s	Qty
02WNSB0400-TH	0		03WHNSB0730-TH	7
02WNSB0680-TH	23		03WHNSB0740-TH	7
02WNSB1030-TH	40		03WHNSB0750-TH	7
02WNSB1050-TH	35		03WHNSB0760-TH	7
02WNSB1060-TH	0		03WHNSB0770-TH	7
02WNSB1080-TH	50		03WHNSB0780-TH	5
02WNSB1150-TH	0		03WHNSB0790-TH	7
02WNSB1180-TH	65		03WHNSB0800-TH	9
02WNSB1230-TH	60		03WHNSB0810-TH	7
02WNSB1250-TH	56		03WHNSB0820-TH	7
03FWHNSB0800-TH	0		03WHNSB0830-TH	6
03FWHNSB1080-TH	0		03WHNSB0840-TH	7
03WHNSB0300-TH	10		03WHNSB0600-TH	10
03WHNSB0310-TH	6		03WHNSB0610-TH	6
03WHNSB0320-TH	6		03WHNSB0620-TH	6
03WHNSB0330-TH	7		03WHNSB0630-TH	7
03WHNSB0340-SD	0		03WHNSB0650-TH	3
03WHNSB0340-TH	7		03WHNSB0660-TH	6
03WHNSB0350-TH	7		03WHNSB0670-TH	7
03WHNSB0360-TH	7		03WHNSB0680-TH	7
03WHNSB0370-TH	7		03WHNSB0690-TH	7
03WHNSB0380-TH	7		03WHNSB0700-TH	10
03WHNSB0390-TH	7		03WHNSB0710-TH	6
03WHNSB0400-TH	13		03WHNSB0720-TH	7
03WHNSB0405-TH	0		03WHNSB0480-TH	7
03WHNSB0410-TH	7		03WHNSB0490-TH	7
03WHNSB0420-TH	7		03WHNSB0500-TH	13
03WHNSB0430-TH	6		03WHNSB0510-TH	7
03WHNSB0440-TH	7		03WHNSB0520-TH	5

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Thanks for your help guys and gals

 

[hiker95]

tempo896,

I have tried several different approaches to your request - but, to no avail.

I have been trying to learn how to use the Scripting.Dictionary.

Then I found some code by MickG that I have finally understood how the code works.

Thank you MickG for all your help and suggestions.


Sample raw data in worksheet Sheet1:

Excel 2007
	A	B	C	D	E
1	There #s	Qty		Our #s	Qty
2	02WNSB0400-TH	0		03WHNSB0730-TH	7
3	02WNSB0680-TH	23		03WHNSB0740-TH	7
4	02WNSB1030-TH	40		03WHNSB0750-TH	7
5	02WNSB1050-TH	35		03WHNSB0760-TH	7
6	02WNSB1060-TH	0		03WHNSB0770-TH	7
7	02WNSB1080-TH	50		03WHNSB0780-TH	5
8	02WNSB1150-TH	0		03WHNSB0790-TH	7
9	02WNSB1180-TH	65		03WHNSB0800-TH	9
10	02WNSB1230-TH	60		03WHNSB0810-TH	7
11	02WNSB1250-TH	56		03WHNSB0820-TH	7
12	03FWHNSB0800-TH	0		03WHNSB0830-TH	6
13	03FWHNSB1080-TH	0		03WHNSB0840-TH	7
14	03WHNSB0300-TH	10		03WHNSB0600-TH	10
15	03WHNSB0310-TH	6		03WHNSB0610-TH	6
16	03WHNSB0320-TH	6		03WHNSB0620-TH	6
17	03WHNSB0330-TH	7		03WHNSB0630-TH	7
18	03WHNSB0340-SD	0		03WHNSB0650-TH	3
19	03WHNSB0340-TH	7		03WHNSB0660-TH	6
20	03WHNSB0350-TH	7		03WHNSB0670-TH	7
21	03WHNSB0360-TH	7		03WHNSB0680-TH	7
22	03WHNSB0370-TH	7		03WHNSB0690-TH	7
23	03WHNSB0380-TH	7		03WHNSB0700-TH	10
24	03WHNSB0390-TH	7		03WHNSB0710-TH	6
25	03WHNSB0400-TH	13		03WHNSB0720-TH	7
26	03WHNSB0405-TH	0		03WHNSB0480-TH	7
27	03WHNSB0410-TH	7		03WHNSB0490-TH	7
28	03WHNSB0420-TH	7		03WHNSB0500-TH	13
29	03WHNSB0430-TH	6		03WHNSB0510-TH	7
30	03WHNSB0440-TH	7		03WHNSB0520-TH	5
31
Sheet1

After the macro in worksheet Sheet2 (I assume that Sheet1, and, Sheet2 already exist):

Excel 2007
	A	B	C	D	E
1	There #s	Qty		Our #s	Qty
2	02WNSB0400-TH	0
3	02WNSB0680-TH	23
4	02WNSB1030-TH	40
5	02WNSB1050-TH	35
6	02WNSB1060-TH	0
7	02WNSB1080-TH	50
8	02WNSB1150-TH	0
9	02WNSB1180-TH	65
10	02WNSB1230-TH	60
11	02WNSB1250-TH	56
12	03FWHNSB0800-TH	0
13	03FWHNSB1080-TH	0
14	03WHNSB0300-TH	10
15	03WHNSB0310-TH	6
16	03WHNSB0320-TH	6
17	03WHNSB0330-TH	7
18	03WHNSB0340-SD	0
19	03WHNSB0340-TH	7
20	03WHNSB0350-TH	7
21	03WHNSB0360-TH	7
22	03WHNSB0370-TH	7
23	03WHNSB0380-TH	7
24	03WHNSB0390-TH	7
25	03WHNSB0400-TH	13
26	03WHNSB0405-TH	0
27	03WHNSB0410-TH	7
28	03WHNSB0420-TH	7
29	03WHNSB0430-TH	6
30	03WHNSB0440-TH	7
31				03WHNSB0480-TH	7
32				03WHNSB0490-TH	7
33				03WHNSB0500-TH	13
34				03WHNSB0510-TH	7
35				03WHNSB0520-TH	5
36				03WHNSB0600-TH	10
37				03WHNSB0610-TH	6
38				03WHNSB0620-TH	6
39				03WHNSB0630-TH	7
40				03WHNSB0650-TH	3
41				03WHNSB0660-TH	6
42				03WHNSB0670-TH	7
43				03WHNSB0680-TH	7
44				03WHNSB0690-TH	7
45				03WHNSB0700-TH	10
46				03WHNSB0710-TH	6
47				03WHNSB0720-TH	7
48				03WHNSB0730-TH	7
49				03WHNSB0740-TH	7
50				03WHNSB0750-TH	7
51				03WHNSB0760-TH	7
52				03WHNSB0770-TH	7
53				03WHNSB0780-TH	5
54				03WHNSB0790-TH	7
55				03WHNSB0800-TH	9
56				03WHNSB0810-TH	7
57				03WHNSB0820-TH	7
58				03WHNSB0830-TH	6
59				03WHNSB0840-TH	7
60
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub AlignAB_DE()
' hiker95, 09/05/2013
' http://www.mrexcel.com/forum/excel-questions/723704-aligning-2-columns-data-individual-rows-better-way-compare.html
' Thank you MickG
' Original code by MickG, MG03Sep43() has been modified
Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, Dn As Range, nTm As Range
Dim col As Long, nCol As Long, c As Long
Dim k, t, TriCol As String
Application.ScreenUpdating = False
Sheets("Sheet2").Columns("A:F").ClearContents
c = 1
With Sheets("Sheet1")
  Set Rng1 = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
  Set Rng2 = .Range(.Range("D2"), .Range("D" & Rows.Count).End(xlUp))
  Set Rng3 = Union(Rng1, Rng2)
End With
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each Dn In Rng3
    TriCol = Dn
    If Not .exists(TriCol) Then
      .Add TriCol, Dn
    Else
      Set .Item(TriCol) = Union(.Item(TriCol), Dn)
    End If
  Next Dn
  For Each k In .keys
    c = c + 1
    For Each nTm In .Item(k)
      col = IIf(nTm.Column = 1, 1, 4)
      nCol = IIf(nTm.Column = 1, 2, 2)
      Sheets("Sheet2").Cells(c, col).Resize(, nCol).Value = nTm.Resize(, nCol).Value
      Sheets("Sheet2").Cells(c, "F") = k
    Next nTm
  Next k
End With
With Sheets("Sheet2")
  .Range("A1").Resize(, 5).Value = Sheets("Sheet1").Range("A1").Resize(, 5).Value
  .Range("A2:F" & c).Sort .Range("F2"), xlAscending
  .Range("F:F").ClearContents
  .Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the AlignAB_DE macro.