Douglas has attended:
Excel Intermediate course
Excel Power Query course
Excel Advanced course
Cypher cvs in excel
Task: I need to cypher through cvs files saved in a folder to determine which is the correct file was used to upload the budgets into our Financial system. Some of the cvs files have multiple versions, whereby I need to identify, the last correct version that was uploaded.
Question: 1. How would I approach this process
Question: 2. Which function in excel could I use to speed up this process?
RE: Cypher cvs in excel
Hi Douglas,
Thank you for the forum question.
To help you efficiently identify the correct CSV file used to upload budgets into your financial system, here's a structured approach:
1. How to Approach the Process
To determine the correct and most recent version of the uploaded budget file:
Step-by-Step Strategy:
Gather All CSV Files:
Place all relevant CSV files in a single folder.
Ensure file names include versioning or timestamps if possible.
Check Metadata (if available):
Look at file properties (e.g., last modified date) to identify the most recent versions.
If your financial system logs uploads, compare timestamps or filenames with those logs.
Compare File Contents:
Open each CSV and compare key budget figures (e.g., totals, department allocations).
Look for unique identifiers or markers that match what’s in the financial system.
Automate Comparison:
Use Excel or a script (e.g., Python) to compare values across files.
Highlight differences or matches with the uploaded data.
Validate Against System Data:
If you can export the uploaded budget from the financial system, compare it directly with the CSVs.
2. Excel Functions to Speed Up the Process
Here are some Excel functions and features that can help:
Key Functions:
VLOOKUP / XLOOKUP: To match values across different CSVs or against exported system data.
IF: To flag discrepancies (e.g., =IF(A2=B2, "Match", "Mismatch")).
TEXT / DATEVALUE: To standardize and compare dates if needed.
MAXIFS / FILTER: To find the most recent version based on a date column.
CONCATENATE / TEXTJOIN: To create unique identifiers for rows to compare across files.
Useful Features:
Conditional Formatting: Highlight differences visually.
Power Query: Import and transform multiple CSVs efficiently.
Pivot Tables: Summarize and compare budget totals quickly.
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: Cypher cvs in excel
Would it be possible to talk to you on Microsoft teams to clarify process?
contact detail: d.mugisa@malariaconsortium.org
RE: Cypher cvs in excel
Hi Douglas,
I am sorry it is not a part of our service to setup Team meetings.
We answer questions in the forum and we are happy to do this.
We are doing consultancies and help clients with their data and have meetings with the clients but the client will have to pay for the cost of this service when it is outside of the scoop of our forum.
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
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
