Moving data

[gary]

Column "A" contains JUNDREDS of unique part-numbers. For
example:

001
002
003
123
234
345
456
567

Column "B" contains MANY THOUSAND part-numbers that match
those in Column "A". For example:

001
001
001
002
002
002
002
003
003
003
123
123
234
234
234
234
234
345
345
345
345
345
345
345
345
345
345
345
456
567
567
567

How can I move the each part-number in Column "A" down to
the LAST occurrence of that part-number in Column "B"? For
example:

Col A Col B
001
001
001 001
002
002
002
002 002
003
003
003 003
123
123 123
234
234
234
234
234 234
345
345
345
345
345
345
345
345
345
345
345 345
456 456
567
567
567 567

 

[Dave Peterson]

How about inserting a helper column and using a formula?

I inserted a new column B and put this in B1 and dragged down:
=IF(ISNUMBER(MATCH(C1,C2:C$65536,0)),"",
IF(ISNUMBER(MATCH(C1,A:A,0)),C1,"Missing"))
(one cell)

If you want you could copy column B and paste special values, then delete column
A.

(But only after double checking your results.)

 

[gary]

Dave,

That works!! (But I had to change your formula because I
lied: columns "A" and "B" are really "D" and "F").

Now, if the part-number in column "D" has associated data
in columns "A", "B" and "C", how would the formula need to
be changed so the associated data "moves" with the
part-number in column "D"?


Gary

 

[Dave Peterson]

Couldn't you just use the same formula in 2 additional helper columns (adjusting
the columns, of course).

 

[gary]

Dave,

I'm having problems adding your formula into the four
helper-columns.

I need the data in columns A, B, C and D to appear next to
the LAST occurrence of the part-number in column E matches
the part-number in column D.

Here's a sample of my original worksheet:

a b c d e
AMT PAY-MONTH PAY-YEAR PART-NBR PART-NBR

549.36 11 1999 151260013-9 151260013-9
1,726.23 11 1999 163080011-8 151260013-9
1,542.24 02 2000 259220028-6 151260013-9
134.80 02 2000 259230002-3 151260013-9
817.19 02 2000 259230005-6 151260013-9
151.10 02 2000 259240004-6 151260013-9
862.49 02 2000 259250001-4 151260013-9
3,411.17 11 1999 278130002-3 151260013-9
710.63 04 2000 308200010-6 151260013-9
200.00 06 2000 317230036-9 151260013-9
1,099.57 09 1999 319200046-9 151260013-9
56.92 04 2000 322180021-9 151260013-9
2,305.38 06 2000 322263010-5 151260013-9
1,188.74 05 2000 323190003-1 151260013-9
340.99 05 2000 341020033-4 151260013-9
353.33 10 1999 343264003-5 151260013-9
340.27 10 1999 343264005-7 151260013-9
340.47 10 1999 343264006-8 151260013-9
448.30 10 1999 343265006-1 151260013-9
372.63 10 1999 343265008-3 151260013-9
4,635.87 08 1999 345260009-3 151260013-9
349.14 09 1999 347210058-6 151260013-9
636.13 06 2000 453502024-3 151260013-9
700.67 11 1999 458120027-1 163080011-8
509.40 11 1999 458120029-3 163080011-8
1,710.00 01 2000 474220022-2 163080011-8
386.29 08 1999 477651024-8 163080011-8
19.97 ?? ???? 528215003-4 163080011-8
686.88 01 2000 572310007-5 163080011-8
1,029.83 04 2000 611066005-5 163080011-8
10,748.76 06 1999 618430032-3 163080011-8
2,362.73 04 2000 620282003-7 163080011-8
359.65 09 1999 645291024-5 163080011-8
563.92 09 1999 677092021-0 163080011-8
213.72 09 1999 765341023-5 163080011-8
1,424.33 04 2000 765382030-8 163080011-8
26,204.67 06 2000 848063031-9 163080011-8
10,616.60 06 2000 913230015-8 163080011-8
42,989.41 06 2000 913230020-2 163080011-8
100.00 05 2000 922053013-5 163080011-8
100.00 05 2000 922054011-6 163080011-8
606.99 04 2000 958230010-0 163080011-8
607.01 04 2000 958230011-1 163080011-8
259220028-6
259220028-6
259220028-6
259220028-6
259220028-6
259220028-6
259220028-6
259220028-6
259220028-6
259220028-6
259220028-6
259220028-6
259220028-6
259220028-6
259220028-6
259220028-6
259220028-6
259220028-6
259220028-6
259220028-6
259220028-6

 

[Dave Peterson]

How about a macro instead?

Option Explicit
Sub testme01()
Dim wks As Worksheet
Dim matchRng As Range
Dim res As Variant
Dim myRng As Range
Dim myCell As Range

Set wks = Worksheets("sheet1")

With wks
'result columns
.Range("e:h").EntireColumn.Insert
Set myRng = .Range("i2", .Cells(.Rows.Count, "I").End(xlUp))
Set matchRng = .Range("d2", .Cells(.Rows.Count, "D").End(xlUp))
For Each myCell In myRng.Cells
If IsError(Application.Match(myCell.Value, _
.Range(myCell.Offset(1, 0), .Cells(.Rows.Count, "I")), 0)) _
Then
'last one
res = Application.Match(myCell.Value, matchRng, 0)
If IsError(res) Then
myCell.Offset(0, -1).Value = "missing"
Else
myCell.Offset(0, -1).Value = matchRng(res, 1).Value
myCell.Offset(0, -2).Value = matchRng(res, 0).Value
myCell.Offset(0, -3).Value = matchRng(res, -1).Value
myCell.Offset(0, -4).Value = matchRng(res, -2).Value
End If
End If
Next myCell

'.Range("a").EntireColumn.Delete
End With
End Sub

Uncomment that "a:d" deletion line when you're happy--or delete it manually.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[gary]

Thank you very much, Dave!!!

The macro was exactly what I needed (it saved me many hours
of manually moving the data).

Gary

That

-----Original Message-----
How about a macro instead?

Option Explicit
Sub testme01()
Dim wks As Worksheet
Dim matchRng As Range
Dim res As Variant
Dim myRng As Range
Dim myCell As Range

Set wks = Worksheets("sheet1")

With wks
'result columns
.Range("e:h").EntireColumn.Insert
Set myRng = .Range("i2", .Cells(.Rows.Count, "I").End(xlUp))
Set matchRng = .Range("d2", .Cells(.Rows.Count, "D").End(xlUp))
For Each myCell In myRng.Cells
If IsError(Application.Match(myCell.Value, _
.Range(myCell.Offset(1, 0),

..Cells(.Rows.Count, "I")), 0)) _