+ Reply to Thread
Results 1 to 14 of 14

Index & Row match to bring over row data to other sheet

  1. #1
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    105

    Index & Row match to bring over row data to other sheet

    Hi,

    I am looking to try and use an index/match variation to bring lines over to new sheet.

    on attached workbook. The Feb sheet is only data that get dropped in.

    On the 2020 List, I want to bring over only Date, List, Description and sum.

    the lookup criteria is communications. But then you can see below it is also Janitorial.

    Ideally I want the function to be if the 2020 list sheet says communication, janitorial, etc, then bring over only the relevant cells and rows as stated above.

    formula used thus far;
    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Index & Row match to bring over row data to other sheet

    Try changing the formula in row 5 to
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then copy C5:F5 and paste into cell C13.
    Dave

  3. #3
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    105

    Re: Index & Row match to bring over row data to other sheet

    Hi FlameRetired,

    I tried that, but it is not copying over the rows from Feb sheet as the change. Wonder why?

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Index & Row match to bring over row data to other sheet

    I don't understand.

    Do you see how it works in my upload?

  5. #5
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    105

    Re: Index & Row match to bring over row data to other sheet

    Yes, I saw that. The only thing is it is only bringing over the first match row.

    so for example the data from the Feb sheet:

    Action Number Date List Description Sum
    1 Communications 123 2/8/2020 Comcast service 200.00
    2 Communications 456 2/15/2020 Comcast service 300.00


    the formula is only bringing over the first one, when I want it to bring over all.
    Please Login or Register  to view this content.
    I am wondering if this
    Please Login or Register  to view this content.
    part of the formula needs looked at. Really I want any row from Feb sheet that has communications and a number, so really anything greater than zero.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Index & Row match to bring over row data to other sheet

    I do not see where your upload reflects that possibility. There are two 'actions' associated with "1". Can you upload a complete sample of what you are describing hand typed?

  7. #7
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    105

    Re: Index & Row match to bring over row data to other sheet

    Flameretired, sure thing.

    I attached a comment on this newly attached excel file. Does this help?

    comment says:

    Here is what I want to do. Use that index formula to bring rows 2 thru 6 from Feb sheet over to 2020 list sheet, and only bring over the cells needed being, date, list, dec, sum. Then run the formula down for columns C,D,E,F and return "" if noting is met. So then we would do this same thing for janitor and so on.

    Essentially I am looking for a formula to copy and paste instead of manual. So for example that Communications has 5 rows on the Feb sheet. So on the 2020 sheet, I want to bring those 5 rows over via formula.
    Attached Files Attached Files

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Index & Row match to bring over row data to other sheet

    Is this what you mean for the output?


    A
    B
    C
    D
    E
    F
    G
    1
    Header
    2
    3
    Date
    List
    Description
    Sum
    Total
    4
    Communications
    5
    2/8/2020
    Comcast
    service
    200.00
    6
    2/15/2020
    Comcast
    service
    300.00
    7
    2/15/2020
    Apple
    Account service
    400.00
    8
    2/15/2020
    Apple
    account late service
    500.00
    9
    2/18/2020
    Microsoft
    bills
    600.00
    10
    2,000
    11
    12
    Janitorial
    13
    2/1/2020
    Kemps
    service
    1,000.00
    14
    2/1/2020
    Lucys
    late service
    2,000.00
    15
    3,000
    16
    17
    5,000
    5,000

  9. #9
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    105

    Re: Index & Row match to bring over row data to other sheet

    yep, that is it. What adjustment to the formula was made to get this result?

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Index & Row match to bring over row data to other sheet

    For communication purposes only (so I am clear on the concept) I merely copy/pasted that before posting.

    Now that I know that this is the desired output I can write the formula. BTW: This is why we ask for representative upload samples of desired output ... not just verbal descriptions.

    Now are you OK with helper cells/columns in either sheet?

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Index & Row match to bring over row data to other sheet

    Never mind.

    This is without helpers.

    In C2:F16
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Index & Row match to bring over row data to other sheet

    You didn't ask but if you are interested this will yield Totals in column G. In G5:G16
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    02-13-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    105

    Re: Index & Row match to bring over row data to other sheet

    FlameRetired, this worked like a charm! Thank you

  14. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Index & Row match to bring over row data to other sheet

    Good to hear. You are welcome. Thank you for the feedback and marking your thread Solved.

+ 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. [SOLVED] Index & Match to Bring Over Information
    By MatthewIJClark in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-18-2018, 11:15 AM
  2. [SOLVED] Index Match to bring a value between a range
    By kricek7 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-12-2017, 08:51 AM
  3. Replies: 2
    Last Post: 01-10-2017, 10:25 AM
  4. VB Code to match data and bring to another sheet.
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-06-2016, 04:28 AM
  5. Using Match/Index to bring multiple values
    By javi07 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 12-27-2014, 03:02 AM
  6. Look in another sheet match & bring data back
    By gill389 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-31-2011, 01:56 PM
  7. index and match in multiple worksheets and bring corresponding row
    By nickgoldie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-14-2010, 04:32 PM

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