+ Reply to Thread
Results 1 to 12 of 12

Formula to lookup a range of numbers and then return data in next 2 columns

  1. #1
    Registered User
    Join Date
    05-04-2013
    Location
    Melbourne Australia
    MS-Off Ver
    Excel 2007
    Posts
    24

    Formula to lookup a range of numbers and then return data in next 2 columns

    Hi All,
    I have a consolidated report for two locations that I wish to split into individual locations. The report has a unique code number in the first column, then a description in the next column and finally a dollar value in the last column. I want to extract all code numbers in a range and then copy the code, description and $'s to a new sheet so I end up with 2 sheets, one for each location and that the two locations include all codes in the consolidated report. The range of code numbers are as follows 4-0001 to 4-0099 are one location, 4-0101 to 4-0199 are the second location. I have 3 groups of numbers 4-xxxx, 5-xxxx & 6-xxxx and all of the groups need to be sub totalled by group. I realise I can match each individual number but I want to allow for the fact that new codes will be created from time to time and these need to be copied as well. (Not all numbers are in use between the ranges) I am not sure if there is a formula that can do this or if it's even possible in excel? My excel skills are quite basic so if you could please explain any possible solutions in simple to understand instructions that would be greatly appreciated!

  2. #2
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Formula to lookup a range of numbers and then return data in next 2 columns

    Hello,

    The short answer is yes, you can do it by formulas and / or macros.

    The long answer is, you will need to upload a small sample file with dummy data. It will also make it much easier for others to help you also.
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  3. #3
    Registered User
    Join Date
    05-04-2013
    Location
    Melbourne Australia
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Formula to lookup a range of numbers and then return data in next 2 columns

    Fantastic, sample data report attached for 2 sites. Thanks for your help!
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Formula to lookup a range of numbers and then return data in next 2 columns

    Please check if the attached file is what you are expecting.

    This is an approach using Array formula. When you editing it (if not using Ctrl-F), you will need to hold Ctrl-Shift and hit Enter when you confirm. If you see the formula being wrapped inside a { } like in the attached file, then you did it right.

    All you have to do is drag the formula on Worksheet 2 and 3 down as far as you see blank (in the attached file, I dragged it down to row 30).

    Edit: In case you want to keep them in one column like in your worksheet 1, I have attached another file.
    Attached Files Attached Files
    Last edited by Lemice; 05-05-2013 at 01:29 AM.

  5. #5
    Registered User
    Join Date
    05-04-2013
    Location
    Melbourne Australia
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Formula to lookup a range of numbers and then return data in next 2 columns

    Oh wow!!! Thank you so much this works perfectly I added a couple of new accounts and it updated correctly too. I tried to go a step further and future proof it but I can't figure out what to do if there was a 3rd site that say used the code x-x2xx. (this may well be needed not too far away). Would you be so kind as to explain that process to me as well? I did look at the formula but I have to admit it's way beyond my understanding
    I also see that in the example of the single column the extra accounts that start with 8-xxxx & 9-xxxx appear but they don't in the separate column example. I know I left them out of the explanation but I now see that there is a place for them to be included as well. Also I think that both of the formats provided will be very useful
    Finally I really appreciate your help, this will save a lot of messing around, so my sincere thanks!

  6. #6
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Formula to lookup a range of numbers and then return data in next 2 columns

    Hello,

    The seperated-columm need to be added every time you have a new group ( I didn't see the group 8 and 9 at the end of the table, I'm sorry )

    About the 1-column file, just drag the formula down until you see "blank", for example on both worksheet 2 and 3, drag them down to row 60, and you will see all code.

    About code x-x2xx, I still see them working on the 1-column file too.

  7. #7
    Registered User
    Join Date
    05-04-2013
    Location
    Melbourne Australia
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Formula to lookup a range of numbers and then return data in next 2 columns

    Hello again,

    Just wanted to stop by and thank you so much for helping me out Everything works perfectly and this is going to be so very useful, you're a genius

    best wishes from Oz!

  8. #8
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Formula to lookup a range of numbers and then return data in next 2 columns

    Glad that I could help.

    If you have found a fitting solution to your problem, please mark the Thread as [SOLVED] using the Thread Tools right above post #1. It keeps things neat and tidy.

    Have a great day.

  9. #9
    Registered User
    Join Date
    05-15-2013
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Formula to lookup a range of numbers and then return data in next 2 columns

    I think this is along the lines of the above question....
    I am very new to Excel and cannot figure out this very simple problem

    I have 2 columns of numbers.

    I need to have Excel take the lesser number in each column..... for each row....

    AND.... place that number in a third row...!!!!!

    Again... I know this is very elementary.... but I am danged if I can remember how I did this
    the last time I needed it.... 8-10 years ago...

    Thank You all in advance

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula to lookup a range of numbers and then return data in next 2 columns

    smoken....Welcome to the Forum.

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  11. #11
    Registered User
    Join Date
    05-15-2013
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Formula to lookup a range of numbers and then return data in next 2 columns

    Apologies Mr Dibbons

    Will do so Thank You

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula to lookup a range of numbers and then return data in next 2 columns

    thanks...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1