+ Reply to Thread
Results 1 to 10 of 10

Copy of rows from one table to another based on identifier in one column

  1. #1
    Registered User
    Join Date
    12-21-2017
    Location
    Denver, CO
    MS-Off Ver
    2016
    Posts
    6

    Copy of rows from one table to another based on identifier in one column

    Hello everyone,

    First ever post here. I am an intermediate user in excel, I can do pivots, vlookups, some "ifs" and a few other things. So let me explain what I am trying to do. It is for a Sales Sheet. I have two sales folks who get credit for redemptions of coupon cards. I have a sales table where I enter the date, customer, customer number and some other data. I have two tables next to it, one for each sales rep (sales rep 1, sales rep 2). What I want is to enter the information in the sales sheet for new redemptions for the week. The final column in the sales sheet is a vlookup from a the "customer sheet" that has all customers and their rep and populates with which rep the redeeming customer belongs to. Based on the value that fills in the vlookup column on the sales sheet, I want the information to automatically populate in the proper sales rep tables next to the original sales table. But I want it to populate in the sales rep tables in the next blank row. Then that will fill in some other info via vlookups and determine their bonus. The only step I can't do is the autopopulate from the original sales table to the proper rep table. Is there a way to do this so I don't have to do so manually?

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 2016 365 Subscription (Win 10 - Home)
    Posts
    13,315

    Re: Copy of rows from one table to another based on identifier in one column

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    12-21-2017
    Location
    Denver, CO
    MS-Off Ver
    2016
    Posts
    6

    Re: Copy of rows from one table to another based on identifier in one column

    How do I attach the file? When I hit the attach button a small field shows below but is blank and doesn't open a browse window.

  4. #4
    Registered User
    Join Date
    12-21-2017
    Location
    Denver, CO
    MS-Off Ver
    2016
    Posts
    6

    Re: Copy of rows from one table to another based on identifier in one column

    Ok, I think I got it attached.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4,398

    Re: Copy of rows from one table to another based on identifier in one column

    I don't understand how you would get the info from the original sales table to the individual rep tables as the original sales table doesn't include the sales count and redeem date. What I did was to get the information from the table in columns A:E on sheet1 using the following array entered formula*:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    *Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    I also included a pivot table solution.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    12-21-2017
    Location
    Denver, CO
    MS-Off Ver
    2016
    Posts
    6

    Re: Copy of rows from one table to another based on identifier in one column

    Hello Jetemc. Thank you for the reply, I am just now finally back in the office. So I am not sure I understand your first question, as the sales count and Redeem Date are column D and E. What I was wanting to do was make (in the sheet you attached) row 4 under "Sales Coupons Redeemed" populate into the "Sales Rep 1" table. It looks like it did end up working. Your
    formula is: =INDEX(A$4:A$18,SMALL(IF($E$4:$E$18=RIGHT($I$2,5),ROW($4:$18)-3),ROW(1:1))) Can you give me a brief breakdown of what the formula is saying? Especially "SMALL" and "-3" parts. Thank you again for the help.

  7. #7
    Forum Expert
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4,398

    Re: Copy of rows from one table to another based on identifier in one column

    I may have been confused as to which table you wanted to retrieve information. However as long as the formula is providing satisfactory results, no need to worry about the question.
    As to how the formula works I'd suggest selecting cell H4 on sheet 1 and engaging the 'Evaluate Formula' feature (on the Formulas tab in the 2010 version) The 'ROW($4:$18)' will produce an array of rows that contain the information then the '-3' will turn that into ordinals of the table (row 4 of the spreadsheet is the first row of the table, row 18 of the spreadsheet is the 15th row of the table. The 'IF' function then matches the ordinals to the TRUE's and the 'SMALL' function picks the designated smallest ordinal, in this case 1. Select H10 and repeat the process. Everything will be the same except that the 'SMALL' function is now picking the 7th smallest ordinal (not including FALSE's) in the array which is 15. I hope that makes sense.
    Let us know if you have any questions.

  8. #8
    Registered User
    Join Date
    12-21-2017
    Location
    Denver, CO
    MS-Off Ver
    2016
    Posts
    6

    Re: Copy of rows from one table to another based on identifier in one column

    Sorry another follow up question, as I am just learning the more complex functions. So the Sales Rep tables populated based on column E or "Sales Rep" column, correct?
    I can't really tell. Or did you have it refer to the HCP field, then use the table from sheet 2 to populate the Rep? I see what you mean about the {} as without those you get
    the incorrect value.

  9. #9
    Registered User
    Join Date
    12-21-2017
    Location
    Denver, CO
    MS-Off Ver
    2016
    Posts
    6

    Re: Copy of rows from one table to another based on identifier in one column

    Also, if I add to the first table of sales, and want to have the Sales Rep tables fill accordingly, can I drag the formula down with the brackets or will they revert to standard and not array?

  10. #10
    Forum Expert
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4,398

    Re: Copy of rows from one table to another based on identifier in one column

    So the Sales Rep tables populated based on column E or "Sales Rep" column, correct?
    Yes, the ...IF($E$4:$E$18=RIGHT($I$2,5)... portion of the formula makes sure that the sales rep in column E matches the sales rep in cell I2.
    can I drag the formula down with the brackets or will they revert to standard and not array?
    Yes, after activating the formula (simultaneously pressing the Ctrl, Shift and Enter keys) you can drag it both down and across.
    Having looked at the formula again I am going to suggest that you wrap the formula in an IFERROR function to catch #NUM errors that would occur otherwise.
    This will make the formula in H4 read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Remember to activate as stated above, before dragging.
    Let us know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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