A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Reload this Page Moving data
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Moving data


« Previous Thread | Next Thread »

 
 
Thread Tools Display Modes
  #1  
Old July 4th, 2004, 10:48 PM
gary
external usenet poster
  
Posts: n/a
Default Moving data
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
  #2  
Old July 5th, 2004, 12:49 AM
Dave Peterson
external usenet poster
  
Posts: n/a
Default Moving data
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 wrote:

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
  #3  
Old July 5th, 2004, 01:50 AM
gary
external usenet poster
  
Posts: n/a
Default Moving data
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
  #4  
Old July 5th, 2004, 12:35 PM
Dave Peterson
external usenet poster
  
Posts: n/a
Default Moving data
Couldn't you just use the same formula in 2 additional helper columns (adjusting
the columns, of course).



gary wrote:

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
  #5  
Old July 5th, 2004, 04:25 PM
gary
external usenet poster
  
Posts: n/a
Default Moving data
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
  #6  
Old July 5th, 2004, 04:56 PM
Dave Peterson
external usenet poster
  
Posts: n/a
Default Moving data
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 wrote:

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
  #7  
Old July 6th, 2004, 02:01 PM
gary
external usenet poster
  
Posts: n/a
Default Moving data
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)) _
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 wrote:

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

.
 



« Previous Thread | Next Thread »

Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to create graphs in a monthly report where the base data can change John Clarke Charts and Charting 3 June 25th, 2004 02:22 AM
moving data [email protected] Outlook Express 1 June 16th, 2004 12:15 AM
Moving data to the right and up. yh73090 Worksheet Functions 1 April 25th, 2004 12:10 AM
Having both the date and total of series data displayed underthe data Jon Peltier Charts and Charting 0 April 14th, 2004 08:39 PM
Generate new document with data Jon Barchenger[MS] Worksheet Functions 0 November 18th, 2003 03:30 PM


All times are GMT +1. The time now is 07:32 PM.

Feed Icon - Contact Us - OfficeFrustration excel & word help home - FAQ - Links - Privacy Statement - Top

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.