Ada has attended:
Excel Intermediate course
Excel Advanced course
Reconciling lists
We spoke briefly about this during the session, but it would be great to get some alternatives and written suggestions on how to make this process easier.
I work a lot reconciling our own data with our clients. I work in shipping insurance and we keep records of vessels that have been "rented in" or "chartered" by our members - this includes the name of vessel(s) and dates during which the vessel has been chartered. These headers are added into columns on a dynamic table which we then populate with the relevant information. Frequently, we have to check our own data against our clients to ensure that we have a) the same vessels documented and b) for the same dates.
Often there are many discrepancies which so far I have had to check / compare individually.
I would like to find an easy way to compare the two tables (i.e. ours against our client's) and easily identify where we are missing vessels and if we have incorrect dates for them.
There must be an easier way to do this which also does not have the same level of human error!
Thank you so much!!
Ada
RE: Reconciling lists
Hi Ada,
Thank you for the forum question.
You have different options to compare lists but which one is the most efficient is a question about the pattern in your lists.
You can use look up and reference functions to get the data side by side on one list and then conditional formatting can highlight differences between the two lists. The advanced filter can extract all records from your list with the same vessel name, period, and client name and then you can compare the list side by side. You can also filter the lists by vessel, period, and client and look at the two lists side by side.
I do not know if you are allowed to show me the lists, but if you are I can have a look at the pattern and format and suggest the best solution for you.
You can also send me two dummy lists, but it is very important that the lists have the exact same format and pattern than your lists.
You can send the lists to:
info@stl-training.co.uk
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Reconciling lists
Dear Jens,
Many thanks for your quick response on this. Highly appreciated - I think that what you suggest about having lists side by side and then recognising patterns and matching these up will work well.
I have sent an email to the address you suggested with two dummy lists to show what we usually work with. All other information has been taken off to show only vessel name, and dates of entry.
I'm very much looking forward to hearing your suggestion on how to do this most efficiently. I spend a lot of time working on these and there must be a way for me to complete them quicker!
Thanks,
Ada
RE: Reconciling lists
Hi Ada,
I have got your lists. I will look at it tomorrow.
I have a question. On your list do you not have a column which identify which client for each row?
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Reconciling lists
Good evening Jens.
Thank you for looking at these sheets!
To answer your question, no. We only ever compare two lists which we know belong to the same client.
Can you see on the sheet that one of the tables is our list (on the right if I remember correctly) and the other table is the list our client sent to us (on the left)?
As you may be able to see already, there are duplicates for the names of the vessels on either lists. There are also dates that are incorrect on either side, but often we are unsure which dates are correct.
To explain again - we assume that our clients lists are correct, however what we aim to do is to highlight any inconsistencies that we need to discuss with them how to rectify.
Let me know if you have any more questions.
RE: Reconciling lists
Hi Ada,
I have got your lists. I will look at it tomorrow.
I have a question. On your list do you not have a column which identify which client for each row?
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Reconciling lists
Hi Ada,
I have got your lists. I will look at it tomorrow.
I have a question. On your list do you not have a column which identify which client for each row?
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
