Share via

Copy from one table to another without overwriting or duplicating.

Anonymous
2023-05-10T18:54:00+00:00

Good Afternoon,

I have one table (Table1) that is updated daily using a daily refreshing query. Column A:Column C contain PO numbers and descriptions and change slightly with each refresh.... some added and some are removed but most stay the same. My goal is to be able to move the contents of Column A:Column C after each refresh to Column A:Column C in an existing table (Table2) on a different sheet (Sheet2). Ultimately adding only new entries from Table1 and not duplicating or removing prior entries from Table2 through the use of a script that I can trigger with Power Automate.

Thank you and if you need any additional information please let me know.

Chad

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Lz365 38,201 Reputation points Volunteer Moderator
2023-05-11T11:12:29+00:00

Hi Chad

Given your Table1 query overwrites previous days' data on each Refresh that's pretty straightforward. The "tricky" point is to create a self-referencing Table2 query:

  • Create a query (with Power Query) from your Table1
  • Rename (in the Power Query Editor) that new query Table2
  • Load it to a sheet
  • Rename the resulting table Table2 (below I assume it's currently named Table_wxyz)
  • Edit the query and change the Source step from 
    = Excel.CurrentWorkbook(){[Name="**Table\_wxyz**"]}[Content]

to

 = Excel.CurrentWorkbook(){[Name="**Table2**"]}[Content]

(now query Table2 references itself)

  • Append Table1 to that query
  • Remove Duplicates on [PO Number] & [Part Number]

when you're complete your query should look like:

  • Closed & Load
  • Finaly enable option "Refresh data when opening the file" on query Table2 (as you probably did for Table1)

Was this answer helpful?

1 person found this answer helpful.
0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lz365 38,201 Reputation points Volunteer Moderator
    2023-05-11T15:28:52+00:00

    Hey Chad

    Glad this helped and congrat. for making it work first time

    On reflection and as I suspect Table2 will potentially grow to thousandssss of records we should look at a more efficient option than removing duplicates

    I have it working no problem. If I share it with you do you feel confortable to adapt it? It's not too really complex as long as you understand how Merging queries work

    Let me know...

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-05-11T14:47:13+00:00

    Thanks Lz.

    That is fantastic and works perfectly. It does exactly what I needed it to and you taught me a bit about self-referencing.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2023-05-11T10:02:24+00:00

    Good Morning,

    I am sorry but I cannot share a Onedrive file from work so I am hoping that this screenshot will help. Each of the three tables on the left are a representation of the same table (Table1) being refreshed each day. Table2 is actually on a different worksheet but for screenshot purposes I figured it would be easier to put on one worksheet.

    Absolutely a formula would be fantastic but a macro would not work because the file cannot be a .xlsm because of our corporate policies.

    Image

    Was this answer helpful?

    0 comments
  4. Anonymous
    2023-05-11T03:02:29+00:00

    Do you mind we use macro or formula to meet your goal? Could you share a test file and expected result?

    You can upload to OneDrive or DropBox. Then post a link here.

    If you want a power automate solution, we’d suggest you go to Microsoft Power Automate Community - Power Platform Community to post a new thread, which is the specific channel to handle Office script issue.

    Was this answer helpful?

    0 comments