Hi all, I have been trying to find a solution to this but my search has not been fruitful. I have an excel file, that I need to update weekly with new data. Downstream users update this master file and I need to include their input with every update. If there are new claims, I need to add them to the master file with a specific format.
New Data comes in this format. Data comes from upstream DBs and user input from downstream users.
| A | Claim id | B | C |
| data1 | 1 | data2 | data3 |
| data1 | 2 | data2 | data3 |
| data1 | 3 | data2 | data3 |
| data1 | 4 | data2 | data3 |
| data1 | 5 | data2 | data3 |
| data1 | 6 | data2 | data3 |
| data1 | 7 | data2 | data3 |
Current Data are in this format
| A | Claim id | B | C | D | E |
| data1 | 1 | data2 | data3 | user input1 | user input2 |
| data1 | 2 | data2 | data3 | user input1 | user input2 |
| data1 | 4 | data2 | data3 | user input1 | user input2 |
| data1 | 6 | data2 | data3 | user input1 | user input2 |
| data1 | 7 | data2 | data3 | user input1 | user input2 |
My end result should look like this.
| A | Claim id | B | C | D | E |
| data1 | 1 | data2 | data3 | user input1 | user input2 |
| data1 | 2 | data2 | data3 | user input1 | user input2 |
| data1 | 3 | data2 | data3 | NULL | NULL |
| data1 | 4 | data2 | data3 | user input1 | user input2 |
| data1 | 5 | data2 | data3 | NULL | NULL |
| data1 | 6 | data2 | data3 | user input1 | user input2 |
| data1 | 7 | data2 | data3 | userinput1 | user input2 |
Basically I need a way to check if the ID is in the current data and if yes, import the data and user input from the current master file. And if the ID is not in the current master file, import the back end data and add some empty columns for user input. I used the union and join tools, but I can't get the output I need.
