+ Reply to Thread
Results 1 to 10 of 10

Find value follow a conditional

  1. #1
    Registered User
    Join Date
    04-07-2015
    Location
    Vietnam
    MS-Off Ver
    Office 2013
    Posts
    89

    Find value follow a conditional

    Hi All!
    I have file
    my request: I want to take information from Sheet1 to KH 2017
    If at columns O =211 at sheet1 all of information of value sheet 1 into sheet KH 2017
    the Value I need into sheet KH2017
    Please help me find create formulas
    File attach.
    Attached Files Attached Files

  2. #2
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Find value follow a conditional

    Here's what you need to retrieve the data from Sheet1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This formula can be used in columns C thru M.
    As the value for column you can specify the number of the column holding the data or you can specify it as COLUMN(F1) if you need to refer to column F (saves a lot of counting columns).
    The get the value for row you need a formula to find the row having the value "211" in column O.
    That's done with this formula (sse column B):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In this formula the IF-function creates an array of rownumbers having 211 in column O and FALSE-values if not.
    Notice that the formula is an array-formula. Close it by hitting [Ctrl]+[Enter].
    But you need a specific row number. This is done by the SMALL-function which selects the kth smallest number in an array ignoring the FALSE-values.
    The value k is in column A.

    Now for something different:
    though the above solution will work, you will probably run into difficulties very soon to keep it working when changing things.
    In order to prevent that it's better to turn you data into tables. This has the following advantages:
    - extending (or shrinking) the data area in KH 2017 in order to show all (or only) the needed rows will be easier.
    Specially when adding rows the formulas will automatically be copied to the new row (provided that formulas are consistent throughout the entire column).
    - referencing columns in sheet1 will be easier (more readable).

    I added copies of your sheets which show this approach.

    One more thing about this approach:
    the number of rows of table in KH 2017 (Tsjallie) will not automatically adjust to the number of rows in Sheet1 (Tsjallie) having 211 in column O.
    You will have to do that manually by adding or deleted rows.
    Or you can have it done by a macro which automatically executes when a change in Sheet1 is made.
    The workbook I'm posting has this procedure.
    Attached Files Attached Files
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  3. #3
    Registered User
    Join Date
    04-07-2015
    Location
    Vietnam
    MS-Off Ver
    Office 2013
    Posts
    89

    Re: Find value follow a conditional

    Thanks for helping me, Tsjallie!
    I got it, but when I use the table and I need to add more vba in file.
    Let me try to do it with my big file.
    THanks!

  4. #4
    Registered User
    Join Date
    04-07-2015
    Location
    Vietnam
    MS-Off Ver
    Office 2013
    Posts
    89

    Re: Find value follow a conditional

    Dear Mr Tsjallie
    When i applied my file by your formulas and macro
    I can not do it
    Can you help me.
    I attached my file
    http://www.mediafire.com/file/l7aa8d...7+-Link-TK.xls
    Attached Files Attached Files
    Last edited by ninhmoon; 09-22-2017 at 10:47 PM.

  5. #5
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Find value follow a conditional

    I can help you if you tell me what problem you're facing with the file.

  6. #6
    Registered User
    Join Date
    04-07-2015
    Location
    Vietnam
    MS-Off Ver
    Office 2013
    Posts
    89

    Re: Find value follow a conditional

    Quote Originally Posted by Tsjallie View Post
    I can help you if you tell me what problem you're facing with the file.
    if I apply your formulas in my real file.
    I can not file 211 at sheet KH2017
    Maybe I add macro in the sheet and I am wrong some thing when I do.

  7. #7
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Find value follow a conditional

    On sheet KH 2016 there cells with formulas referencing the wrong data.
    Formula in cell G10 is
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    change that into
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Make likewise changes cells H10 to M10

  8. #8
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Find value follow a conditional

    And as for the macro.
    This should go into the codemodule of Sheet1 (or Data in the big workbook you uploaded).

    However the macro will not work properly as it assumes the data in Sheet1 to be in a table.
    In the big workbook the data in sheet Data is not in a table.
    To make the macro work you need to convert the data in sheet Data into a table.
    And also in the macro change the sheetnames (Sheet1, Sheet4 resp) to names they have in the big file.

    Not sure if it will work at once as your data is pretty unstructured.

  9. #9
    Registered User
    Join Date
    04-07-2015
    Location
    Vietnam
    MS-Off Ver
    Office 2013
    Posts
    89

    Re: Find value follow a conditional

    Quote Originally Posted by Tsjallie View Post
    And as for the macro.
    This should go into the codemodule of Sheet1 (or Data in the big workbook you uploaded).

    However the macro will not work properly as it assumes the data in Sheet1 to be in a table.
    In the big workbook the data in sheet Data is not in a table.
    To make the macro work you need to convert the data in sheet Data into a table.
    And also in the macro change the sheetnames (Sheet1, Sheet4 resp) to names they have in the big file.

    Not sure if it will work at once as your data is pretty unstructured.
    Thank for helping me.
    I am using A-tool
    when I use your code, it is not work. and makes my file is very slowly
    Thanks again.

  10. #10
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Find value follow a conditional

    What is A-tool?
    Your file is slow because is huge and highly unstructered.
    Try to gid rid of unnecessary data and obsolete sheets.
    Try to restructure your workbook to have separate sheets with fixed data (so no formulas), sheets with calculations and sheets which just present data.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to use vlookup or any formular to find data follow array.
    By thaimic in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-14-2016, 01:47 PM
  2. How can I find value follow array
    By thaimic in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2016, 03:32 AM
  3. How to use vlookup or any formular to find data follow array.
    By thaimic in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-13-2016, 12:01 AM
  4. find and index follow 2 conditions
    By ninhmoon in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-01-2015, 06:42 AM
  5. [SOLVED] Find/copy/paste follow header of column
    By lamvienthien in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2015, 02:31 AM
  6. want to find a template for managing customer follow-up
    By Ed Maurina in forum Excel General
    Replies: 1
    Last Post: 01-05-2013, 06:19 PM
  7. Conditional Formatting Charts with follow-up data
    By Drew Goldberg in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-16-2010, 04:36 PM

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