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

T

tempo896

New Member
Joined
Aug 29, 2013
Messages
6
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
[TABLE="width: 525"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]There #s
[/TD]
[TD] Qty
[/TD]
[TD] [/TD]
[TD]Our #s[/TD]
[TD="align: right"] Qty
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]02WNSB0400-TH[/TD]
[TD]0[/TD]
[TD]
[/TD]
[TD]03WHNSB0730-TH[/TD]
[TD="align: right"]7
[/TD]
[/TR]
[TR]
[TD]02WNSB0680-TH[/TD]
[TD]23[/TD]
[TD] [/TD]
[TD]03WHNSB0740-TH[/TD]
[TD="align: right"]7
[/TD]
[/TR]
[TR]
[TD]02WNSB1030-TH[/TD]
[TD]40[/TD]
[TD] [/TD]
[TD]03WHNSB0750-TH[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]02WNSB1050-TH[/TD]
[TD]35[/TD]
[TD] [/TD]
[TD]03WHNSB0760-TH[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]02WNSB1060-TH[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]03WHNSB0770-TH[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]02WNSB1080-TH[/TD]
[TD]50[/TD]
[TD] [/TD]
[TD]03WHNSB0780-TH[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]02WNSB1150-TH[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]03WHNSB0790-TH[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]02WNSB1180-TH[/TD]
[TD]65[/TD]
[TD] [/TD]
[TD]03WHNSB0800-TH[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]02WNSB1230-TH[/TD]
[TD]60[/TD]
[TD] [/TD]
[TD]03WHNSB0810-TH[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]02WNSB1250-TH[/TD]
[TD]56[/TD]
[TD] [/TD]
[TD]03WHNSB0820-TH[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]03FWHNSB0800-TH[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]03WHNSB0830-TH[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]03FWHNSB1080-TH[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]03WHNSB0840-TH[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]03WHNSB0300-TH[/TD]
[TD]10[/TD]
[TD] [/TD]
[TD]03WHNSB0600-TH[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]03WHNSB0310-TH[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD]03WHNSB0610-TH[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]03WHNSB0320-TH[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD]03WHNSB0620-TH[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]03WHNSB0330-TH[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]03WHNSB0630-TH[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]03WHNSB0340-SD[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]03WHNSB0650-TH[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]03WHNSB0340-TH[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]03WHNSB0660-TH[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]03WHNSB0350-TH[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]03WHNSB0670-TH[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]03WHNSB0360-TH[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]03WHNSB0680-TH[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]03WHNSB0370-TH[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]03WHNSB0690-TH[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]03WHNSB0380-TH[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]03WHNSB0700-TH[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]03WHNSB0390-TH[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]03WHNSB0710-TH[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]03WHNSB0400-TH[/TD]
[TD]13[/TD]
[TD] [/TD]
[TD]03WHNSB0720-TH[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]03WHNSB0405-TH[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]03WHNSB0480-TH[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]03WHNSB0410-TH[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]03WHNSB0490-TH[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]03WHNSB0420-TH[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]03WHNSB0500-TH[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]03WHNSB0430-TH[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD]03WHNSB0510-TH[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]03WHNSB0440-TH[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]03WHNSB0520-TH[/TD]
[TD="align: right"]
5

[/TD]
[/TR]
</tbody>[/TABLE]
Thanks for your help guys and gals ;)
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
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
ABCDE
1There #sQtyOur #sQty
202WNSB0400-TH003WHNSB0730-TH7
302WNSB0680-TH2303WHNSB0740-TH7
402WNSB1030-TH4003WHNSB0750-TH7
502WNSB1050-TH3503WHNSB0760-TH7
602WNSB1060-TH003WHNSB0770-TH7
702WNSB1080-TH5003WHNSB0780-TH5
802WNSB1150-TH003WHNSB0790-TH7
902WNSB1180-TH6503WHNSB0800-TH9
1002WNSB1230-TH6003WHNSB0810-TH7
1102WNSB1250-TH5603WHNSB0820-TH7
1203FWHNSB0800-TH003WHNSB0830-TH6
1303FWHNSB1080-TH003WHNSB0840-TH7
1403WHNSB0300-TH1003WHNSB0600-TH10
1503WHNSB0310-TH603WHNSB0610-TH6
1603WHNSB0320-TH603WHNSB0620-TH6
1703WHNSB0330-TH703WHNSB0630-TH7
1803WHNSB0340-SD003WHNSB0650-TH3
1903WHNSB0340-TH703WHNSB0660-TH6
2003WHNSB0350-TH703WHNSB0670-TH7
2103WHNSB0360-TH703WHNSB0680-TH7
2203WHNSB0370-TH703WHNSB0690-TH7
2303WHNSB0380-TH703WHNSB0700-TH10
2403WHNSB0390-TH703WHNSB0710-TH6
2503WHNSB0400-TH1303WHNSB0720-TH7
2603WHNSB0405-TH003WHNSB0480-TH7
2703WHNSB0410-TH703WHNSB0490-TH7
2803WHNSB0420-TH703WHNSB0500-TH13
2903WHNSB0430-TH603WHNSB0510-TH7
3003WHNSB0440-TH703WHNSB0520-TH5
31
Sheet1


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


Excel 2007
ABCDE
1There #sQtyOur #sQty
202WNSB0400-TH0
302WNSB0680-TH23
402WNSB1030-TH40
502WNSB1050-TH35
602WNSB1060-TH0
702WNSB1080-TH50
802WNSB1150-TH0
902WNSB1180-TH65
1002WNSB1230-TH60
1102WNSB1250-TH56
1203FWHNSB0800-TH0
1303FWHNSB1080-TH0
1403WHNSB0300-TH10
1503WHNSB0310-TH6
1603WHNSB0320-TH6
1703WHNSB0330-TH7
1803WHNSB0340-SD0
1903WHNSB0340-TH7
2003WHNSB0350-TH7
2103WHNSB0360-TH7
2203WHNSB0370-TH7
2303WHNSB0380-TH7
2403WHNSB0390-TH7
2503WHNSB0400-TH13
2603WHNSB0405-TH0
2703WHNSB0410-TH7
2803WHNSB0420-TH7
2903WHNSB0430-TH6
3003WHNSB0440-TH7
3103WHNSB0480-TH7
3203WHNSB0490-TH7
3303WHNSB0500-TH13
3403WHNSB0510-TH7
3503WHNSB0520-TH5
3603WHNSB0600-TH10
3703WHNSB0610-TH6
3803WHNSB0620-TH6
3903WHNSB0630-TH7
4003WHNSB0650-TH3
4103WHNSB0660-TH6
4203WHNSB0670-TH7
4303WHNSB0680-TH7
4403WHNSB0690-TH7
4503WHNSB0700-TH10
4603WHNSB0710-TH6
4703WHNSB0720-TH7
4803WHNSB0730-TH7
4903WHNSB0740-TH7
5003WHNSB0750-TH7
5103WHNSB0760-TH7
5203WHNSB0770-TH7
5303WHNSB0780-TH5
5403WHNSB0790-TH7
5503WHNSB0800-TH9
5603WHNSB0810-TH7
5703WHNSB0820-TH7
5803WHNSB0830-TH6
5903WHNSB0840-TH7
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.

Code: 
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA Code to compare Dates, then run macro to match colums
Replies
4
Views
955
Airstar
A
S
  • Solved
Match value from a specific cell with the same value in multiple columns, return the results of these rows from a specific column
Replies
5
Views
923
Fluff
Fluff
K
  • Question Question
Need VBA help Cut and paste in to another locations if data and condition matches.
Replies
1
Views
782
Kishan
K
M
  • Question Question
VBA to format Cells with Borders based on Column Value ( merged cells)
Replies
1
Views
977
DanteAmor
DanteAmor
B
  • Question Question
Copying/Inserting Rows & Data from one sheet to another based on a date lookup
Replies
0
Views
477
Billman101
B

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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