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

[marcadimus]

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?

[AliGW]

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.

[marcadimus]

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.

[marcadimus]

Ok, I think I got it attached.

[JeteMc]

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*:

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.

[marcadimus]

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.

[JeteMc]

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.

[marcadimus]

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.

[marcadimus]

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?

[JeteMc]

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:

Please Login or Register  to view this content.

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