+ Reply to Thread
Results 1 to 12 of 12

extracting unknown data from sheet 1 to sheet 2

  1. #1
    Registered User
    Join Date
    11-20-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2000
    Posts
    13

    extracting unknown data from sheet 1 to sheet 2

    I am new to posting here. I have two sheets in one file, First sheet has column A containing numbers such as 100.10. They are sequenced but not all numbers are used this week but more could be next week. I need to extract numbers on the first sheet and list them in sheet 2. The numbers are in the middle of the first list starting at 300.51 thru 500.99. I need to use the numbers listed in sheet 2 to lookup data in sheet 1. please help. Ie.

    Sheet 1 Col A Sheet 2 Col A
    100.10 300.56
    200.51 345.12
    300.45 400.69
    300.56 500.80
    345.12
    400.69
    500.80
    600.00
    700.10
    800.20
    900.52

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: extracting unknown data from sheet 1 to sheet 2

    You question is not very clear. It would be great if you attach a sample workbook with a "After" sheet so we can understand what you are trying to achieve.

  3. #3
    Registered User
    Join Date
    11-20-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2000
    Posts
    13

    Re: extracting unknown data from sheet 1 to sheet 2

    how do i do that? I did include an example in the post but it deleted spacing Sheet 2 col A should be 300.56
    345.12
    400.69
    500.80

  4. #4
    Registered User
    Join Date
    11-20-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2000
    Posts
    13

    Re: extracting unknown data from sheet 1 to sheet 2

    Here is the workbook sample requested.
    Attached Files Attached Files

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: extracting unknown data from sheet 1 to sheet 2

    Quote Originally Posted by wurugu View Post
    I need to extract numbers on the first sheet and list them in sheet 2. The numbers are in the middle of the first list starting at 300.51 thru 500.99. I need to use the numbers listed in sheet 2 to lookup data in sheet 1.
    This point is very confusing. You said you need to extract numbers from sheet 1 - on what basis?

    Then again, these numbers need to be looked up in sheet 1? This can be done provided we understand on what basis the numbers will be extracted to sheet 2.

  6. #6
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: extracting unknown data from sheet 1 to sheet 2

    Hello Wurugu


    i can't also get your idea but i've notice your

    Please Login or Register  to view this content.
    should be with $ like

    Please Login or Register  to view this content.
    then drag down maybe this is what you wanted.

    HTH.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  7. #7
    Registered User
    Join Date
    11-20-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2000
    Posts
    13

    Re: extracting unknown data from sheet 1 to sheet 2

    Let me try to explain. Sheet 1 contains a column of numbers that are in sequence but not 100.00,200.00
    ,300.00,400.00,500.00; more like 100.01,100.07,100.12,100.25.............. but no pattern. Sheet 2 must search those numbers for a specific series such as starting with 300.51 and ending with 500.99. List only those that meet this criteria starting in column A of sheet 2. The VLookup function is in Sheet 2 Column B. uses the numbers in column A to look up data from Sheet 1 and put it in Sheet 2 Column B.

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: extracting unknown data from sheet 1 to sheet 2

    Hi wurugu,

    Your problem looks like you want an Advanced Filter of your data. See the attached example and learn a little about Advanced Filters, Criteria etc.
    http://www.contextures.com/xladvfilter01.html
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  9. #9
    Registered User
    Join Date
    11-20-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2000
    Posts
    13

    Re: extracting unknown data from sheet 1 to sheet 2

    Thanks MarvinP. I will review the info you posted, but I think it requires intervention by someone. I am trying to develop a workbook that someone needs not do anything other than import data downloaded from a corporate system and the workbook does all the rest. I have a workaround however it is not very efficient.

  10. #10
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: extracting unknown data from sheet 1 to sheet 2

    Are the starting and ending numbers in Column A always the same? If they aren't then how do you determine the start and end values? That would take user interaction.

    Have you looked at the topic of Dynamic Named Ranges to help determine how long the list is?
    http://www.beyondtechnology.com/geeks007.shtml

  11. #11
    Registered User
    Join Date
    11-20-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2000
    Posts
    13

    Re: extracting unknown data from sheet 1 to sheet 2

    Ok MartinP I will check out Dynamic Named Ranges and see if that will help. The Test data in Column A Sheet 2 always will start with 300.01 and end with 500.99 so the test can be written to use >=300.00 and <=500.99. These are account numbers and new accounts can be added as needed but will have to be added between them.

  12. #12
    Registered User
    Join Date
    11-20-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2000
    Posts
    13

    Re: extracting unknown data from sheet 1 to sheet 2

    Either I am a bit dense or the dynamic named ranges will not work. I understand using the dynamic named ranges requires user input. I do not want user to do anything except import a csv file into sheet one containing data that will automatically get updated in sheet two if the data is between 700 and 800 for example. Sheet one would have numbers ranging from 100 to 1000. I only want the 700 thru 800 to appear in sheet two. Then those in sheet two would be used to lookup the values associated with them in sheet one.

+ 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