Stumped....Matching Columns
 

Hey,

I have no idea how to do this:

I have two columns with almost the exact same numbers. What I need to
do is match the columns together so that the numbers that are similar
match each other...for example the list would look like this:

2066078 2066078
2066079 2066078
2066080 2066078
2066081 2066078
2066082 2066079
2066083 2066080
2066084 2066081
2066085 2066082
2066086 2066083


and it should look like this:

2066078
2066078
2066078
2066079 2066079
2066080 2066080
2066081 2066081
2066082 2066082
2066083 2066083

Any ideas?


--
c0nfusEd :{
------------------------------------------------------------------------
c0nfusEd :{'s Profile: http://www.excelforum.com/member.php...o&userid=36327
View this thread: http://www.excelforum.com/showthread...hreadid=561103

Stumped....Matching Columns
 

Assuming these are columns A and B, how about this?

1) Sort both columns into numerical order
2) Insert/Name/Define and give the second column a name, e.g. Range1
3) In a column C column enter this formula

=IF(LOOKUP(A1,range1)=A1,A1,"")

Then copy the formula down column for as many rows as there are in
column A


--
Special-K
------------------------------------------------------------------------
Special-K's Profile: http://www.excelforum.com/member.php...fo&userid=7470
View this thread: http://www.excelforum.com/showthread...hreadid=561103

Stumped....Matching Columns
 

thanks for the response special K, but unfortunately thats not what im
looking to do. I guess I wasnt clear. I have two sets of data merged
into one spreadsheet, but with similar ID#, I am trying to match the
ID# together so if this is what is in my columns:

F01B 31/14 2066196 2066196 123353
C12N 15/16 2066197 2066196 1051593
C03C 17/34 2066198 2066197 124452
D01F 9/12 2066199 2066198 124454
D04H 1/70 2066200 2066199 123449
G07D 7/00 2066201 2066200 990031
G11B 7/00 2066202 2066201 124455
C07D 209/48 2066203 2066202 1160521
C12N 15/12 2066204 2066203 124456
C07C 2/66 2066205 2066203 1052512


I somehow want to match the 2 columns so that they look like this:

F01B 31/14 2066196 2066196 123353
2066196 1051593
C12N 15/16 2066197 2066197 124452
C03C 17/34 2066198 2066198 124454
D01F 9/12 2066199 2066199 123449
D04H 1/70 2066200 2066200 990031
G07D 7/00 2066201 2066201 124455
G11B 7/00 2066202 2066202 1160521
C07D 209/48 2066203 2066203 124456
2066203 1052512
C12N 15/12 2066204 2066204 103124

Hopefully this is a little more clear. I have 440,000 cells of this
stuff (like 13 spreadsheets) so if anyone could help me with this, your
help would be much aprreciated! As it would take me a month to do it the
way im doing it now.

Thanks.


--
c0nfusEd :{
------------------------------------------------------------------------
c0nfusEd :{'s Profile: http://www.excelforum.com/member.php...o&userid=36327
View this thread: http://www.excelforum.com/showthread...hreadid=561103

Stumped....Matching Columns
 

Give this try (only limited testing):

Sub transform()

Dim ws1 As Worksheet

Set ws1 = Worksheets("Sheet1")

With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lastrow
If .Cells(r, "B") < .Cells(r, "C") Then
.Cells(r, "A").Resize(1, 2).Insert Shift:=xlDown
.Cells(r, "c").Resize(1, 2).Cut .Cells(r, "b")
End If
Next r

End With

End Sub

HTH

"c0nfusEd :{" wrote:


thanks for the response special K, but unfortunately thats not what im
looking to do. I guess I wasnt clear. I have two sets of data merged
into one spreadsheet, but with similar ID#, I am trying to match the
ID# together so if this is what is in my columns:

F01B 31/14 2066196 2066196 123353
C12N 15/16 2066197 2066196 1051593
C03C 17/34 2066198 2066197 124452
D01F 9/12 2066199 2066198 124454
D04H 1/70 2066200 2066199 123449
G07D 7/00 2066201 2066200 990031
G11B 7/00 2066202 2066201 124455
C07D 209/48 2066203 2066202 1160521
C12N 15/12 2066204 2066203 124456
C07C 2/66 2066205 2066203 1052512


I somehow want to match the 2 columns so that they look like this:

F01B 31/14 2066196 2066196 123353
2066196 1051593
C12N 15/16 2066197 2066197 124452
C03C 17/34 2066198 2066198 124454
D01F 9/12 2066199 2066199 123449
D04H 1/70 2066200 2066200 990031
G07D 7/00 2066201 2066201 124455
G11B 7/00 2066202 2066202 1160521
C07D 209/48 2066203 2066203 124456
2066203 1052512
C12N 15/12 2066204 2066204 103124

Hopefully this is a little more clear. I have 440,000 cells of this
stuff (like 13 spreadsheets) so if anyone could help me with this, your
help would be much aprreciated! As it would take me a month to do it the
way im doing it now.

Thanks.


--
c0nfusEd :{
------------------------------------------------------------------------
c0nfusEd :{'s Profile: http://www.excelforum.com/member.php...o&userid=36327
View this thread: http://www.excelforum.com/showthread...hreadid=561103