Converting pasted data to customer follow-up information

[StephenGundrum]

I have a very basic understanding of Excel and I need some help. I'm trying to create a workbook that will take daily transactions and convert them to a customer follow-up list. Is there a way to take the information on the Paste sheet and move it to either the Act or the Up sheet based on Column N? The Act and Up sheet should return Code, Rep Name, BAN, MSISDN, Customer Name, and Date. 

[#2]

@StephenGundrum , looks like a perfect use case for Power Query. Paste more information in the Paste Tab and extend the formulas in the table on the right side. Update the Rep list as well. Then click on Data->Refresh All to update the Act and Up table. Let me know if you run into any issues...

[#3]

@TheAntony [Expression.Error] There were too many elements in the enumeration to complete the operation.

[#4]

@StephenGundrum 

Is this error after pasting in the new data? Also, in your iferror formulas, you have used a " " as the 2nd argument. Make sure it remains " ". You will get an error if you change it.

[#5]

Yes. I pasted more data on the left side of the Paste sheet. I then dragged down the formulas on the right side of the Paste sheet. That part worked. When I went to Data>Refresh All is when that error message popped up.

[#6]

@StephenGundrum , can you attach the updated file? 

[#7]

I'll need to redact customer information first. I'll upload as soon as that is done.

[#8]

@StephenGundrum , of course. :thumbs_up:

[#9]

@TheAntony 

[#10 · Accepted solution]

@StephenGundrum , The original version assumed that there will be only one occurrence of Rep, so it was erroring out when a rep was there twice. I changed the logic to accommodate multiple occurrences of Reps. See if this works. 

[#11]

Thank you very much for your help! That solution is working.

[#12]

@StephenGundrum 

If with Power Query I'd remove all formulas in Paste sheet, you don't need them at all. Something like in attached file.