+ Reply to Thread
Results 1 to 5 of 5

Copy data from one sheet to another if criteria met

  1. #1
    Registered User
    Join Date
    04-08-2016
    Location
    California
    MS-Off Ver
    windows 7
    Posts
    28

    Copy data from one sheet to another if criteria met

    Hi,

    I am trying to copy and paste column data from Sheet1 into Sheet2 if a text is found in Sheet 1. I would like to grab the Content Code column (Column C) from Sheet1 if the Content System column (Column A) has a the word Oranges. Then I would like to grab the respective Content Cost. The data in Sheet 1 is dynamic and a table. Every month there could more or less rows added or removed. So I would like the formula to be dynamic as well.

    I have tried Index Match and Vlook Up and nothing as worked! My formula is "INDEX(Sheet1!C:C,MATCH("Oranges",Sheet1!A:A0))"

    Could someone advise on this problem? Is there another formula for this. I have attached a sample.

    Thanks alot for your help!
    Attached Files Attached Files
    laryhaty

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Copy data from one sheet to another if criteria met

    Minor change on your formula

    =INDEX(Sheet1!C:C,MATCH("Oranges",Sheet1!A:A,0))

    Or try at K4 copy to L4
    =INDEX(Sheet1!$A:$H,MATCH($K$1,Sheet1!$A:$A,0),MATCH(K$2,Sheet1!$A$1:$H$1,0)),0))

    Above formula just return the first Orange on the list.

    if you want all list of orange then

    K4 copy to L4 and copy down
    =INDEX(Sheet1!$A:$H,AGGREGATE(15,6,ROW(Query1)/(INDEX(Query1,,1)=$K$1),ROWS(K$3:K3)),MATCH(K$2,Query1[#Headers],))
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-08-2016
    Location
    California
    MS-Off Ver
    windows 7
    Posts
    28

    Re: Copy data from one sheet to another if criteria met

    Hi Bo_Ry,

    Thank you for your response.

    The third formula is what I am looking for! Thank you!

    Can you explain that formula for me so that I can tweak it the future for my purposes. What the do you divide by Index and have two Matches?

    Thanks!

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Copy data from one sheet to another if criteria met

    =(INDEX(Query1,,1)=$K$1)
    Query is your table in sheet1, Index (INDEX(Query1,0,1) is table1, row 0 mean take very rows , 1 is column index this gives sheet1 columnA

    =$K$1 value is "orange" give True, if not give False

    ROW(Query1)/(INDEX(Query1,,1)=$K$1) row#/{true; False} row#/True = row#/1 = row# , row#/Fasle = row#/0 = #DIV/0


    AGGREGATE(15,6, row(), ROWS(K$3:K3))
    15 for small , 6 for ignore error like #DIV/0 , ROWS(K$3:K3) runing number start from 1

    at K3 will give small rank 1 from all row# that has "orange"
    at K4 will give small rank 2 from all row# that has "orange"


    =AGGREGATE(15,6,ROW(Query1)/(INDEX(Query1,,1)=$K$1),ROWS(K$3:K3)) give all the row# that has "orange"

    =MATCH(K$2,Query1[#Headers],) is columns index that matches header.

    Then combine all together.

  5. #5
    Registered User
    Join Date
    04-08-2016
    Location
    California
    MS-Off Ver
    windows 7
    Posts
    28

    Re: Copy data from one sheet to another if criteria met

    Thank you! Never heard of the combining a row formula and then dividing by index!

    Your formula works great

+ 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. Replies: 19
    Last Post: 10-16-2017, 12:34 PM
  2. [SOLVED] Copy specific column data from one sheet to another sheet using Certain filter Criteria
    By xlhelp7 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-02-2017, 12:09 AM
  3. Replies: 2
    Last Post: 01-23-2016, 01:16 PM
  4. [SOLVED] Consolidated Summary Sheet - Copy data from one sheet to other based on criteria
    By sabha in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-21-2015, 03:29 AM
  5. Replies: 1
    Last Post: 11-14-2013, 09:24 PM
  6. [SOLVED] Copy and paste data from sheet 2 to sheet 1 based on specific criteria on sheet 1
    By VBADUD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2012, 04:18 AM
  7. Replies: 4
    Last Post: 07-24-2012, 07:09 AM

Tags for this Thread

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