Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VLookup or any Working Formula (Excel 2000)

    I have attached a worksheet example of what I am hoping to fix...It seems the data entry person is doing a lot of repetitive typing and was wondering if anyone would have time to look at the file and see if we can come up with a solution...I have tried a VLookup, but as the comments below indicate, Column One is not in sequential order, nor can it be to accomodate VLookup, so is there another way? I have also included a comment in cell C56 as to what we are looking for.

    Thanks so much...situation follows:

    Is there a way to take the data that is entered into A3:I44 above and drop that into this area of the spreadsheet in sequential order
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VLookup or any Working Formula (Excel 2000)

    How about this scheme:
    In J5 enter:
    <pre>=(COUNTA(C55)=2)*1+J4</pre>


    Copy/autofill this from J6 to J44
    In J56 to J150 enter the numbers 1, 2,3, ....,94, 95 [enter 1 in J56, 2 in J57, and drag the 2 down by btm right corner to J150]

    In A56 enter:
    <pre>=IF(J56>$J$44,"",INDEX($A$5:$A$44,MATCH(J56,$ J$5:$J$44,0)))</pre>


    Copy/autofill to A57:A150

    In D56 enter:
    <pre>=IF(J56>$J$44,"",INDEX($D$5:$D$44,MATCH(J56,$ J$5:$J$44,0)))</pre>


    Copy/autofill to D57150

    Col J may be hidden if desired or even moved elsewhere (change formulas accordingly)

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup or any Working Formula (Excel 2000)

    Will let you know how it goes, thanks for the feedback...
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup or any Working Formula (Excel 2000)

    That worked GREAT. Thank you for showing me how to use the Index and Match functions to do this. They have always been awkward to me, until your explanation. Now I understand them.

    Many Thank yous!
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VLookup or any Working Formula (Excel 2000)

    Yes, the Index/Match combo can be used to Vlookup to the left.

    Steve

Posting Permissions

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