use this in Column C:C
=IFERROR(VLOOKUP(A1,B:B,1,0),"")
Rajan.
On Sat, Mar 12, 2011 at 3:42 PM, Sanjoy Nath <sanjoy.nath...@gmail.com>wrote:
> no need to write macro
> sort any one column
> and use lookup to other column
> that will work fine
>
>
>
> On Sat, Mar 12, 2011 at 6:06 AM, bpascal123 <bpascal...@googlemail.com>wrote:
>
>> Hi cyberspace,
>>
>> I have spent quite some time trying to make this work but at this
>> point from adding many msgbox checks, using the watch window for
>> variables values everything seems coherent to me.
>>
>> I have 2 columns with sorted identical and not identical numercial
>> values in both columns, see below :
>>
>> col.A col.B
>> 251120 251130
>> 251140 272505
>> 251145 291101
>> 272505 292100
>> 272535
>> 291130
>> 292100
>>
>> I need to align identical value and to place single value alone on one
>> row :
>>
>> 251120
>> 251130
>> 251140
>> 251145
>> 272505 272505
>> 272535
>> 291101
>> 291130
>> 292100 292100
>>
>> Now with the vba code, I get this :
>>
>> Option Explicit
>> Option Base 1
>>
>>
>> Public Sub RowMatching()
>>
>> Dim wkb As Workbook
>> Dim wks As Worksheet
>> Set wkb = Workbooks("code_row_v2.xls")
>> Set wks = wkb.Worksheets("Sheet1")
>>
>> Dim trouve As Boolean
>>
>> Dim LigCol1 As Integer 'numéro de ligne pour la premiere colonne
>> Dim LigCol2 As Integer 'numéro de ligne pour la seconde colonne
>> Dim LastRow As Long
>> Dim tmp
>> Dim Numligne(256) As Long
>> Dim marchehaute As Integer
>> Dim marchebasse As Integer
>> Dim marche As Integer
>>
>> wks.Cells(1, 1).Select
>>
>> LastRow = 0
>> LigCol1 = 1
>> While wks.Cells(LigCol1, 1) <> ""
>> LastRow = LastRow + 1
>> LigCol1 = LigCol1 + 1
>> Wend
>>
>> LigCol1 = 1
>> wks.Cells(LigCol1, 1).Select
>> While LigCol1 <= LastRow '''MAIN LOOP
>> Numligne(LigCol1) = wks.Cells(LigCol1, 1)
>> 'MsgBox wks.Cells(LigCol1, 1)
>> For LigCol2 = 1 To LastRow
>>
>> If Numligne(LigCol1) = wks.Cells(LigCol2, 2) Then '2a-IF7
>>
>> If LigCol2 < LigCol1 Then '3a-IF9
>> Cells(LigCol2, 2).Select
>> marchehaute = LigCol1 - LigCol2
>> marche = 1
>> While marche <= marchehaute
>> Selection.Insert shift:=xlDown
>> marche = marche + 1
>> Wend
>>
>> ElseIf LigCol2 > LigCol1 Then
>> Cells(LigCol1, 1).Select
>> marchebasse = LigCol2 - LigCol1
>> marche = 1
>> While marche <= marchebasse
>> Selection.Insert shift:=xlDown
>> marche = marche + 1
>> LastRow = LastRow + 1
>> Wend
>>
>> End If '3a-IF9
>>
>> End If '2a-IF7
>>
>> Next LigCol2
>>
>> LigCol1 = LigCol1 + 1
>>
>> Wend
>>
>> LigCol1 = 1
>> wks.Cells(LigCol1, 1).Select
>> For LigCol1 = 1 To LastRow
>> MsgBox wks.Cells(LigCol1, 1) & " - " & wks.Cells(LigCol1, 2)
>>
>> If Not IsEmpty(wks.Cells(LigCol1)) Then
>>
>> If wks.Cells(LigCol1, 1).Value <> wks.Cells(LigCol1, 2).Value
>> Then
>> Rows(LigCol1).Select
>> Selection.Insert shift:=xlDown
>> Cells(LigCol1 + 1, 1).Select
>> Selection.Cut
>> Cells(LigCol1, 1).Select
>> ActiveSheet.Paste
>> LastRow = LastRow + 1
>> End If
>>
>> End If '2b-IF5
>>
>> Next LigCol1
>>
>> MsgBox LastRow
>>
>> End Sub
>>
>>
>> Variable names are in french but it's easy : consider marche is
>> floor : marchebasse = lowerfloor, marchehaute = upperfloor... in fact
>> marche means step but steps has many meaning in english, here it would
>> be stairway.
>>
>> Ok, this is what I get when i run the code from above :
>>
>>
>> 251120
>> 251130
>> 251140
>> 251145
>> 272505 272505
>> 272535 291101
>> 291130
>> 292100 292100
>>
>> Although, the switch is completed for values 251120 and 251130
>> initialy on the same row, they are now on 2 distinct rows as stated
>> in For LigCol1 = 1 To LastRow loop
>>
>> But when it comes to values 272535 and 291101, no new rows is added as
>> it should for two different values on the same row. msgbox even show
>> the loop is going though these values as with 251120 and 251130
>>
>> Could you point where I am missing something?
>>
>> Then if one can rearrange the whole thing, I feel recursion could make
>> me spare a few lines here but I admit I don't have the skills to deal
>> with algorithms and make thing simple when it is.
>>
>> Thanks,
>> Cyberuser
>>
>>
>>
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> Your Sincerely
> Sanjoy Nath
> Engineer
> EOL Gurgaon
> EOL/M/NZ/Engg.Dept.(Haryana Division)
>
>
>
> We prepare custom tools (Softwares) to prepare Autocad Drawings
> Of the Engineering Components.Some Of Our Clients Provide the
> Design Calculation Steps And Sample Drawings (Templates for Eg the
> Nuts , Bolts , Screws , Stair Cases , Patterns , Geometries , LandScapes,
> Layouting, General Engineering Components , Mechanical Engineering
> Components,
> Civil/Structural Engineering Components,Architectural Components) to
> follow
> and to meet their standards and specifications.The Tools we develop are
> entirely customised and specific for clients own requirements and
> they are not re distributed to others.
>
> Let me be precise regarding our job profile and what we can do for you
> Suppose you have to develop any design component repeatedly for different
> projects
> Only Some of its parameters are changing and you need the drawing for that
> component.
> you cannot prepare the block for that.Since even in the custom Block in
> Autocad you
> cannot controll all the parameters automatically and very few users in
> Autocad can follow
> the technicalities to handle the customisable blocks in Autocad.
> On the Other hand , customisable blocks cannot decide the dimensions by
> themselves nor can
> compute the design calculations.But And it takes a long time to develop the
> drawings.
> Moreover if there is any revision in the General Arrangement drawings or
> Facilities,
> the entire things need to change and you have to start from the scratch
> again and
> draftsman forget to change some of the dimensions or the BOQ values in the
> tables
> attached to the drawings.These things are ridiculous to your clients.
> We can understand these issues since we are in the design/Detailing and
> drafting industry
> for last 10 years and have worked for more than 6 companies(Directly as
> employee or in parttime)
> in 3 domains
> (Piping,Architecture and Structural)
> And now we have attained the expertise to develop the dxf files from
> Excel/EXE Applications.
> We can develop and provide the tools (EXE tools or Excel Files as you need
> and as much you ask for)
> to develop these drawings automatically.
>
> Suppose you want to develop the drawings of doors/Panels with some given
> features many/some of
> which will change every time from task to task or from revision to revision
> or from project to project.
> You have to revise the drawings and your draftsman/detailer will take 30
> minutes to 1 hour to revise each of
> these several components.We can save this valuable time for you through the
> tools.
> Moreover many a time it happens that you have to prepare the scaled
> drawings from the data in STAAD pro
> and you have to type the utilisation ratios and reactions beside the
> members including the sizes also.
> Have you ever calculated how much time you spend on that and how much
> errorprone are these ??????
> We can help you in these matters also.
> We can extract the data in tables/Dimensions in the Autocad Drawing to
> Excel (With Automations)such that you can prepare the
> BOQs easily.Suppose you have a drawing and the dimensions of some feature
> in a certain layer.We can provide
> tools to you which will read the data from your dimensions in specific
> layer and will type them
> in the excel format.And can prepare the drawings of some other feature from
> the extracted dimensions.
>
> We can develop the same kind of tools for Word ,STAAD Pro , Tekla , PDF ,
> Excel Also which are applicable for
> MIS purposes and the custom checking tools for word processing
> industries/Quality control departments.
> We have given you the examples with CAD and Excel Only.But we have
> expertise in STAAD Pro ,TEKLA
> PDF technologies also.We work in the digitisation works also.
>
>
>
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
Regards
Rajan verma
+91 9158998701
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
