Joining 2 excels with different data

Hello, when i join 2 excels, the data output is missing certain items. i joined with specific fields (company, fund, deal code). Not sure why the inputs are correct but the output is wrong.

Excel 1: 374 investments

Excel 2: 411 investments

Output: 305

Workflow

Accepted answers

Luke_C

Hi @jdkatz1

Manually should give you full flexibility to line up the columns as you need. Without more information it's not possible to help further. Can you share the workflow with sample data that shows the issue you're having, along with your desired output?

All comments

binu_acs

@jdkatz1 some of the key fields company, fund, deal code might not be matching with the two files. you may need to verify manually or if you can provide some sample data we can look into this.

Qiu

@jdkatz1

When using the Join tool, we have to check the three output anchor, L, J, R.

jdkatz1

Essentially, I use the left and right inputs and the J output for them to be combined. the two files are similar however the newer file has more investments. I am only taking certain info from File 1 for comparison as File 2 is the current year. maybe a union tool would be better as the files won't match up exactly?

File 1.xlsx

File 2.xlsx

binu_acs

@jdkatz1 The records which are showing in the anchor R are not present in the File1

Join_0830.yxmd

jdkatz1

i understand joining by those parameters discussed will exclude a lot of info, bc File 2 has more investments than file 1. I am only pulling in certain info from File 1. how can i capture that data from file 1 and combine it with the recent data in file 2?

Luke_C

Hi @jdkatz1

You can use a union tool after the join to add back the data that fell out following the join. It sounds like you may want to union the J output and whichever output has the file 2 data that wasn't in file 1. This will result in the complete file 2 data with any fields from file 1 added

jdkatz1

Okay this enabled me to get all the info. However, i am running into an issue now that the file 2 information that was missing is not aligning correctly with the output excel. See attached. the union of J and R is misaligned do to the fact that file 2 has no "right info" as it is a single file. however i need all the information to be in the "right" columns to due filtering only the new data from file 2.

Joinissue1.PNG

Luke_C

@jdkatz1 How is the union configured? Maybe you need to manually configure and align the columns how you'd like.

jdkatz1

Currently confirg by name or position. I tried to do it manually but same issue arises

Luke_C

Hi @jdkatz1

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703