reconciling lists
Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Reconciling lists

Reconciling lists

resolvedResolved · High Priority · Version 2016

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

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

See also:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel articles

Please browse our web site to find out more about
advanced excel seminar training and other training courses.

Excel tip:

Hiding a worksheet in Excel

Want to circulate an Excel workbook file but keep certain worksheets hidden from your colleagues' view?

You can do so by bringing up the sheet you wish to hide on your screen; then going to Format - Sheet - Hide.

It will not be immediately obvious that a sheet is hidden from view unless perhaps the sheet are still labelled Sheet 1, Sheet 2 etc.

To display the sheet again, you can go to Format - Sheet - Unhide on any of the other sheets in the workbook. A dialogue box will appear, allowing you to select the hidden sheet/s. Click OK to make the sheet/s reappear again.

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.1 secs.