Discrepancies issues

[nacaacan]

I am having a problem in creating a worksheet.

So, this is my question. I have to import and export client data to an external database in access format everytime the user clicks a user form named for example: "Import & Export Data" and during the importing any discrepancies - between the records in the worksheet and the database, should be recorded and reported in a separated worksheet called "discrepancies".






Help

 

[C with no eyes]

How would the spreadsheet know there are discrepancies? Do you export a report from your database in order for excel to compare it before data is uploaded?

 

[nacaacan]

Yes. I export the data from my database for excel.

How would the spreadsheet know there are discrepancies? Do you export a report from your database in order for excel to compare it before data is uploaded?

 

[C with no eyes]

OK. It'll be difficult to explain what you need to do without knowing the layout, at least.

I have made quite a few spreadsheets in the past that do what you're asking but they are all set up differently as the reports contain different types of information. Sometimes our reports looked different because different users have different settings. There are lots of variables to consider. It takes me anywhere between 1 to 48 hours to create a template spreadsheet that manipulates data with minimal effort.

When I create a spreadsheet like this it tends to contain the following:

A sheet called 'Paste here'
The data is exported from your database and presumably auto opens into excel. You will need to copy the export and paste to this sheet.

A sheet called 'Workings'
This sheet will pull the relevant information through (remember I said that exported reports could be in completely different orders due to personal settings) then compare as necessary.

A final sheet with the output ('Daily reports', 'Discrepancies')
This will be your nice, tidy report.

Another tip for creating your own spreadsheet, especially if you're unsure of a new formula is to separate functions in a separate cell. A stupidly basic example:


[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width: 48pt; text-align: center;"> </colgroup><tbody>[TR]
[TD="width: 64, align: center"]A[/TD]
[TD="width: 64, align: center"]B[/TD]
[TD="width: 64, align: center"]C[/TD]
[TD="width: 64, align: center"]D[/TD]
[TD="width: 64, align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]=A1+B1[/TD]
[TD="align: center"]=D1+C1[/TD]
[/TR]
</tbody>[/TABLE]

When you know each component of your calculation works then you can cut the formula from D1 and paste it over where it says D1 in E1.

This site deals with specific problems. Yours covers quite a large area and would take time to set up. Do you have any specific questions? How confident are you using excel?