Need help in building a Reconciliation between two data sets

[mohit9garg]

Dear Experts,

I need your help in building a reconciliation between two data sets. I am attaching an excel example here but needless to say that actual data set is very large.

Thanks in advance.

Mohit

[mohit9garg]

@AbhilashR ...Thank you its worked. However I am seeing issue in Joining the data. Both Data sets are getting duped not merging properly; i-e, One Line is from Data1 and One Line from Data2.

What could be the issue??

Regards,

Mohit

[mohit9garg]

@DavidP ..Thank you for the solution. Let me replicate this workflow. But could you please advise me the purpose of adding record id here and sorting it?

Kind Regards,

Mohit

[mohit9garg]

Thanks @Grossal for the solution.. Let me try to replicate this.

[AbhilashR]

Hi Mohit, getting dupes in your dataset is usually indicative of data being joined on incorrect columns or another way put, you need to join the datasets on a combinations columns that will yield a one-to-one mapping. Is the solution I submitted giving you duplicates when you run it across your larger dataset?

I am currently joining them on Stock Symbol, Client, Symbol Id, Currency, and it matches the sample output you provided.  Does your larger dataset have additional columns than what was in the sample file?

[DavidP]

It's just in case you want the rows in the same order as they were originally. The join tool will usually change the order of your rows.

[mohit9garg]

@Grossal .. Your solution worked as always. 🙂

I have few more requirements like formatting of rec and converting numbers into USD. I have mentioned all the requirements as comments in attached sheet.

Would you be able to help me please.

Thanks,

Mohit

[grossal]

You are welcome! I am happy to help 😃

A couple questions to clarify before I start my work on the changes.

1) FX Rates

-> I would expect that you want these as an additional multiplier for the values - For all values or only for specific? Or only for "... in USD" ? columns that are an addition if I see it right.

2) Colors aren't a problem. We'll use the reporting tools for this. I am not sure how dynamic we can be with the reporting tools (I am not an expert with these tools)

3) 4 decimals shouldn't be an issue. 

[mohit9garg]

@Grossal ..

1)  I just want to convert only one column in USD. Please convert any column in USD, I will replicate that formula in my actual data. In excel, I did it with multiplier & Vlookup formula. Also this would be in an additional column like I showed in example.

2) I just want to highlight only header & differences column in a color. That would be fixed columns (not dynamic)

3) Price should be at 4 decimal place. Other should n't have any decimal.

4) Is "Grouping" possible?? Refer row 73 to 87 in example rec.

[grossal]

I am a bit confused!

"I just want to convert only one column in USD. Please convert any column in USD" -> hm? *confused stars around my head*

Oh sorry, I forgot to reply to the last one: I think grouping isn't possible as far as I know.

[mohit9garg]

@Grossal

ok, please Convert "Opening Position Data Difference" in USD. Like I did in Col H in example. 🙂