Fuzzy Match Across Multiple Columns

Question

Hi All

Hoping I can get some help from the community here.   Going to preface this with saying I am struggling to actually communicate what I am trying to accomplish.  I just have the idea in my head.

File 1 has securities data at a point in time.  In this case 1/1/1997.  To the right of that data I have a formula (done in excel) where I am looking to get the next price at point in the future.   I like to think of this as the horizontal file.

File 2 has the individual security data at those further points in the future.  I like this think of this as the vertical file.

The end result would be to match Symbol and Date across all the dates on the horizontal file.  Something that looks like this.

Thank you for taking the time to read and any help you can provide.

gautiergodard · Accepted Answer

Hey @Mike_at_CG

If i understand correctly, I dont think you be using the fuzzy match for this scenario?

Instead, i think you would need to pivot file 1 so the data is displayed vertically, use 1 or more joins to get the relevant data from file 2, and then pivot you data back so it looks like the end result provided.

dhoover · Accepted Answer

Hi @Mike_at_CG! I think you could also use the Transpose tool under the orange Transform toolkit to rearrange the data to get the result you are looking for, and then join the data strings with a Join tool or Join Multiple tool. Finally, you will want to rearrange the columns with a Select tool in order to have the proper order by date.