Stumped....Matching Columns

[c0nfusEd :{]

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?

[Special-K]

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

[c0nfusEd :{]

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:

AA 2066196 2066196 BA
AB 2066197 2066196 BB
AC 2066198 2066197 BC
AD 2066199 2066198 BD
AE 2066200 2066199 BE
AF 2066201 2066200 BF
AG 2066202 2066201 BG
AH 2066203 2066202 BH
AI 2066204 2066203 BI
AJ 2066205 2066203 BJ


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

AA 2066196 2066196 BA
2066196 BB
AB 2066197 2066197 BC
AC 2066198 2066198 BD
AD 2066199 2066199 BE
AE 2066200 2066200 BF
AF 2066201 2066201 BG
AG 2066202 2066202 BH
AH 2066203 2066203 BI
2066203 BJ
AI 2066204 2066204 BK

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.

[Toppers]

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
>
>