Results 1 to 9 of 9

Thread: VLOOKUP

  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VLOOKUP

    I want to do VLOOKUPs based on text fields. The text fields can have up to 5 words and the number of spaces can be different in the lookup_value, and the table_array for what is notionally the same text field. Eg the lookup_value might be New(space)York(space)(space)Yankees and the table_array (space)New(space)(space)York(space)Yankees. Excel cannot cope with this.

    The lookup_value could be easily dealt with by using the Trim function to remove leading and trailing spaces and reduce multiple inter word spaces to single spaces. However to do the same for a couple of hundred table_arrays is a daunting prospect. Can anyone see a way around this?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP

    Do the spaces have to remain in the table? If not, then just go to an empty column and enter a formula like the one below in the cell in same row as the first table row:

    <pre>=Trim(A1)
    </pre>


    Where A1 is replaced by the first cell in the table. Fill this formula down (drag it using the handle in the lower right corner of the cell) down to the same number of rows as the table. Now, select and copy the cells with this formuala. Select the top cell in the table and then select "Paste Special" from the Edit menu. In the dialog box, click on "Values" in the Paste section and click on OK. The table values should now be replaced by the Trimmed values. You should now be able to delete the Trim formulas you added earlier.

    If you must leave the spaces in the table, then just use the column with the Trim formulas for the VLOOKUP.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP

    Thanks Legare. It is a series of Annual Reports available only as Read Only files. I could make copies and manipulate these but I was hoping someone would come up with a sharp idea to save me having to work on 200 sheets. However I was not very optimistic when I posted the query.

  4. #4
    Lounger
    Join Date
    Apr 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP

    Hi Michael. I had a similar problem at one time, and someone gave me this code which automatically copies a formula down the column to the end of the data. As I understand you, you don't want to drag down 200 pages, and who could blame you? Hope this is of some help.

    Sub FillDown()
    'Copies a formula operating on values in column A to column B until there
    'are no more values in column A. Cursor must be in cell A1 before running the
    'sub.
    Range("A1", Selection.End(xlDown)).Offset(0, 1).Select
    Selection.FillDown
    Range("A1").Select
    End Sub

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP

    Just a little tip for a quick filldown method.

    Enter a formula in a cell and then give a quick double click on the drag handle of the selection indicator, i.e. the little black square in the bottom right corner of the selected cell. This will autofill the formula down to the extent of the adjacent coloumn.

    Andrew C

  6. #6
    Lounger
    Join Date
    Apr 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP

    Andrew, that is just too awesome! A couple of times lately I have written code only to discover that the function is built into Excel. Oh well...

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP

    Well there is a certain satisfaction in doing it yourself, even if there is a quicker way.

    Andrew

  8. #8
    LeTaylor
    Guest

    Re: VLOOKUP

    Hi Michael...

    Another way you could attempt to do this is by using ASAP Utilities. It is a collection of Macros built into a toolbar. Very handy utility... This way you just select the whole column(s) or row(s) click on two buttons and you are done...

    ASAP utilities can be downloaded at:
    <A target="_blank" HREF=http://www.asap-utilities.com/>http://www.asap-utilities.com/</A>

    -Leslie

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP

    When you do the auto-click fill down, make sure it's gone all the way. If your adjacent column has a blank in it, the autofill will stop at that point.
    I often do the copy and paste by copying, then going to the bottom of the fill range (Ctrl-End is useful here). Then shift-ctrl-up to get to the top of the range and then paste.

    Ruth

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •