+ Reply to Thread
Results 1 to 30 of 30

Copy values that meet a criterion

  1. #1
    Registered User
    Join Date
    09-07-2019
    Location
    greece
    MS-Off Ver
    2010
    Posts
    49

    Copy values that meet a criterion

    hi again, how we can have such a formula which work like:if A1>3 ,extract the values of B1:B18 to The F1:F18 column?thanks
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 09-08-2019 at 01:10 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Copy values that meet a criterion

    Your thread title was weak. It should really resemble the words you would use in a Google search. I have changed it for you, on this occasion. n future, your thread may be locked until YOU make the required changes.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    09-07-2019
    Location
    greece
    MS-Off Ver
    2010
    Posts
    49

    Re: Copy values that meet a criterion

    ok , bro ,thanks

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Copy values that meet a criterion

    Change A1 to 3 and use:

    =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$1:$B$18)/($B$1:$B$18>$A$1),ROWS(F$1:F1))),"")
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-07-2019
    Location
    greece
    MS-Off Ver
    2010
    Posts
    49

    Re: Copy values that meet a criterion

    sorry, where is the 1,2,3 at F1:F18,they are not extracted ,if A>=3,copy values to f1:f3,if A1<=3 dont copy

    and can we use another formula ,more simplier.
    Last edited by sakis777; 09-08-2019 at 01:24 PM.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Copy values that meet a criterion

    Please explain again. You said you wanted ONLY those values > 3 extracted. That is what the formula does.

    If that is NOT what you want, amend your attachment to show your expected results and stop us having to guess.

  7. #7
    Registered User
    Join Date
    09-07-2019
    Location
    greece
    MS-Off Ver
    2010
    Posts
    49

    Re: Copy values that meet a criterion

    Dear Glenn, a formula at f1:f18 which copies the data of a1:a18 only if A1>=3. if A1<=3 not to copy the data of a1:a18 so f1:f18 will be empty

  8. #8
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Copy values that meet a criterion

    F1=IF(B1>=RIGHT($A$1,1)+0,B1,"")

    copy down

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Copy values that meet a criterion

    If Caracall a is not right... read what I said at Post 6 and act on it. Post a sheet with expected results.

  10. #10
    Registered User
    Join Date
    09-07-2019
    Location
    greece
    MS-Off Ver
    2010
    Posts
    49

    Re: Copy values that meet a criterion

    no,simply,is if a1>3 copy all values of B1:B18 and paste them at F1:F18
    Last edited by sakis777; 09-08-2019 at 02:07 PM.

  11. #11
    Registered User
    Join Date
    09-07-2019
    Location
    greece
    MS-Off Ver
    2010
    Posts
    49

    Re: Copy values that meet a criterion

    there no expexted results,is just what i said,please help me Glenn

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Copy values that meet a criterion

    How about in F1 & copied down
    =IF($A$1>3,B1,"")
    then input a number into A1

  13. #13
    Registered User
    Join Date
    09-07-2019
    Location
    greece
    MS-Off Ver
    2010
    Posts
    49

    Re: Copy values that meet a criterion

    that formula does not do anything,i try

  14. #14
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Copy values that meet a criterion

    Did you put a number into A1?
    Or do you mean if A1 equals ">3" rather than A1 is greater than 3?

  15. #15
    Registered User
    Join Date
    09-07-2019
    Location
    greece
    MS-Off Ver
    2010
    Posts
    49

    Re: Copy values that meet a criterion

    maybe kind of Vlookup formula if a1>3 or coutif ?

  16. #16
    Registered User
    Join Date
    09-07-2019
    Location
    greece
    MS-Off Ver
    2010
    Posts
    49

    Re: Copy values that meet a criterion

    i put a number 3, ok you are right i put 4 and is working

  17. #17
    Registered User
    Join Date
    09-07-2019
    Location
    greece
    MS-Off Ver
    2010
    Posts
    49

    Re: Copy values that meet a criterion

    can we use a range B1:B18 insted of B ?

  18. #18
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Copy values that meet a criterion

    I don't understand.

  19. #19
    Registered User
    Join Date
    09-07-2019
    Location
    greece
    MS-Off Ver
    2010
    Posts
    49

    Re: Copy values that meet a criterion

    ok ,its fine ,thank you

  20. #20
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Copy values that meet a criterion

    It's very simple. Put what you WANT to see in column F in colums F YOURSELF. Just enter your expected answers and repost.

  21. #21
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Copy values that meet a criterion

    Sakis, I have no idea who you are talking to in ANY of your recent posts. SAY who you are talking to... otherwise this is becoming a total mess.

  22. #22
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Copy values that meet a criterion

    Did you look at the file I posted? In what way was it wrong????
    Last edited by Glenn Kennedy; 09-08-2019 at 02:43 PM.

  23. #23
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Copy values that meet a criterion

    Quote Originally Posted by sakis777 View Post
    ok ,its fine ,thank you
    You're welcome & thanks for the feedback

  24. #24
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Copy values that meet a criterion

    Glenn
    I think the op wanted all the contents of col B in col F, if A1>3, rather than just values from col B that were >3

  25. #25
    Registered User
    Join Date
    09-07-2019
    Location
    greece
    MS-Off Ver
    2010
    Posts
    49

    Re: Copy values that meet a criterion

    yes , is solved . thanks

  26. #26
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Copy values that meet a criterion

    delete post
    Last edited by CARACALLA; 09-08-2019 at 02:46 PM.

  27. #27
    Registered User
    Join Date
    09-07-2019
    Location
    greece
    MS-Off Ver
    2010
    Posts
    49

    Re: Copy values that meet a criterion

    =IF($A$1>3,B1,""), is doing the job..its fine.thanks

  28. #28
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Copy values that meet a criterion

    Quote Originally Posted by Fluff13 View Post
    Glenn
    I think the op wanted all the contents of col B in col F, if A1>3, rather than just values from col B that were >3
    That was the other possibility, but it might have been nice to have been told....

  29. #29
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Copy values that meet a criterion

    Yeah, the op wasn't very clear on that.

  30. #30
    Registered User
    Join Date
    09-07-2019
    Location
    greece
    MS-Off Ver
    2010
    Posts
    49

    Re: Copy values that meet a criterion

    you are right,thanks Glenn

+ 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. Extract until next criteria
    By sp0ck1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-20-2018, 12:55 AM
  2. [SOLVED] Extract table with criteria
    By rajeshn_in in forum Excel Formulas & Functions
    Replies: 26
    Last Post: 01-10-2017, 10:29 AM
  3. Replies: 8
    Last Post: 05-08-2016, 12:48 AM
  4. Replies: 0
    Last Post: 05-02-2016, 07:59 AM
  5. [SOLVED] Extract number with criteria
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-03-2015, 02:23 AM
  6. Replies: 3
    Last Post: 12-18-2014, 10:27 AM
  7. Extract data with 2 criteria
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-15-2012, 06:48 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