Join data without removing rows in between

[Jenina]

Hello,

I am trying to join 2 files cause the other file contains the data needed (lookup function) but my first file has fields in between like in pivot table. My output is not getting the result I wanted. Please see sample files attached.

Thank you

[alexnajm]

I think you should look at the Find Replace tool!

[alexnajm · Accepted solution]

Looks like there's a bit more work needed as well, so here's a good start on the process: 

[geraldo · Accepted solution]

@Jenina 

I took advantage of another example you asked for and put the join with file2. Maybe it will help with your problem!

[Jenina]

@geraldo sorry, If I have a currency column in file 2, would that make a difference? There are same account units and they will differ depending on the currency.

[Jenina]

@alexnajm sorry, If I have a currency column in file 2, would that make a difference? There are same account units and they will differ depending on the currency.

[alexnajm]

@Jenina it makes a difference because if you are matching on two fields, the Find Replace doesn't work. You'll either need to concatenate them on both sides, or you might have to use a Join tool but then you'll likely have to rethink some of the other tools to ensure you keep the options you want. You can play around with my workflow and see how it affects the logic

[geraldo]

@Jenina 

I believe you are not sending a correct sample. I just replicated your Procv() using the join multiple tool that simulates a procv(). As you only use units, I sent an example. Please review your sample data and submit it so we can help you.

[Jenina]

@geraldo  I think I figured this out, but I still have more work to do on the workflow I am building. I will post another query. Thank you!