compare two worksheets 1

[OCM]

Greetings,

Based on the attached sample file,
1. In my receiving report I’d like to rename receipt date and store name as in my order report: order date and supplier.

Order Report --- Receiving Report
Order Date --- Receipt Date (should be named Order Date)
Supplier --- Store Name (should be named Supplier)
Account Code --- Account Code
Order Number --- Order Number
Blanket Order ---
Order Type --- Order Type
Distribution Amount --- Distribution Amount

2. Delete the remaining columns in both order and receiving reports so in the end I’m left with the following:
Order Number---Order Date---Order Type---Blanket Order---Supplier---Account Code --- Distribution Amount

3 Reconcile the two reports in a new worksheet: combine duplicate on the basis of order number and distribution amount values for both reports, then
compare and determine outstanding distribution amounts i.e. order – receiving

can a macro/vba be used to accomplish the above manual process?

TIA,
Regards

OCM

 

[SkipVought]

Hi,

Yes, and therefore I'd suggest posting in forum707 where You'ld get better results than here.

 

[SkipVought]

Well I cogitated a tad and thot that there is a straight Excel Feature solution that does not require VBA. It is using MS Query via Data > Get external data > From other sources > From Microsoft Query > Excel Fiels* --- and drill down to your workbook.

Use the following SQL code

SELECT
  ORD.`Order Number`
, ORD.`Order Date`
, RCV.`Receipt Date`
, ORD.`Order Type`
, ORD.`Blanket Order`
, ORD.Supplier
, ORD.`Account Code`
, ORD.`Distribution Amount`
, ORD.`Distribution Amount`-RCV.`Distribution Amount` as [To Go Amount]

FROM 
  `'Order Report 0115 $'` ORD
, `'Receiving Report 0115$'` RCV

WHERE ORD.Supplier = RCV.`Store Name`
  AND ORD.`Account Code` = RCV.`Account Code`
  AND ORD.`Order Number` = RCV.`Order Number`
  AND ORD.`Order Type` = RCV.`Order Type`

File > Return data to Microsoft Excel on a new sheet

Results:
[pre]
Order Number Order Date Receipt Date Order Type Blanket Order Supplier Account Code Distribution Amount To Go Amount
51300 12/1/2014 12/1/2014 STANDARD SHC 101-566100-6101-1 $465.67 $0.00
51303 12/1/2014 12/1/2014 STANDARD_RELEASE 10022 Verizon 101-564400-6220-1 $132.81 $0.00
[/pre]

 

[OCM]

Thanks SkipVought for this tip. I tried your step by step instructions:
In the Query Wizard window, I choose all my columns from both order and receiving reports and I got the following prompt:
“The query wizard cannot continue because it can not join the tables in your query. You must join the tables manually….”
I clicked OK. I then accessed the SQL window that shows the current SQL statement. I did copy your code to overwrite the existing code.
I got the following prompt:
“SQL query can’t be represented graphically. Continue anyway?
I clicked ok, but got another error:
“Could not add the table ‘Order Report 0115 $’
TIA,


OCM

 

[SkipVought]

“Could not add the table ‘Order Report 0115[highlight #FCE94F] [/highlight]$’"

Is not one of your sheets Order Report 0115[highlight #FCE94F] [/highlight]
followed by a SPACE? You might need to EITHER add a space after 0115 or remove the space in the SQL code.

 

[SkipVought]

Here are some tips for using MS Query:

1) In the Choose Data Source window, UNCHECK the Use query wizard box.

2) After drilling down to your workbook, the Add Tables window opens in Microsoft Query.

3) In the Add Tables window, do this ONE TIME: Hit the OPTIONS button and Check ALL the boxes and OK.

4) The Sheet Names in the workbook will be displayed suffixed by $. Named ranges will list as well. ADD the 2 sheets.

5) I like to replace the repeated table name in the FROM clause with an easily identifiable alias, all of the same length

from
FROM 
  `'Order Report 0115 $'` `'Order Report 0115 $'`
, `'Receiving Report 0115$'` `'Receiving Report 0115$'`

to
FROM 
  `'Order Report 0115 $'` ORD
, `'Receiving Report 0115$'` RCV

I copy the SQL to Notepad where it's easier to edit/replace. replace so it looks like what I originally posted and past back into the SQL code window.

6) Now you can JOIN the fields where the data need to match up in the GUI grid by dragging the associated fields together
[pre]
ORD.Supplier == RCV.`Store Name`
ORD.`Account Code` == RCV.`Account Code`
ORD.`Order Number` == RCV.`Order Number`
ORD.`Order Type` == RCV.`Order Type`
[/pre]

7) where you put in the formula
[pre]
ORD.`Distribution Amount`-RCV.`Distribution Amount`
[/pre]
and add a column Alias like
[pre]
ORD.`Distribution Amount`-RCV.`Distribution Amount` as [To Go Amount]
[/pre]
...you'll get a nasty message about can't be represented graphically. This means that all subsequent modifications must be done in the SQL WIndow.

Hope this helps.

 

[OCM]

Thank you Skip Vought for your step by step instructions.

1. For some reasons, the report still displays the “Receipt Date” from the Receiving report. I’d like this to be
renamed Order Data as in the Order report.

2. I wasn’t clear about instructions 5 – 7.
#5. What part of the code & where am I replacing
#6. I did drag & join to the corresponding field, I’m not sure why the Receipt Date column still shows in the
report

3. #7. I wasn’t sure where to put the formula/ alias

4. Can you please clarify a bit about the “To Go Amount”, what I’m looking for in the end is the following:
Let us say for example, order # 1234 in the order report shows a distribution amount of $150.00 and the
same order # 1234 shows a distribution amount of $100 in receiving report, I want to see $50.00 (order –
receiving)

Basically, I’m interested in any negative/positive delta values, not “0”

please let me know if you have any questions

TIA

Regards,

OCM

 

[SkipVought]

First off use the code I originally posted 27 Jan 15 04:07 along with the two order number results.

The steps in a subsequent post were for your information.

Regarding your questions/comments...

1. You cannot have 2 columns with the same name. Receiving is different than order. BOTH refer to the same order and occur at different times, ALWAYS the order date preceeding a receival date. So I have them as two separate and distinct columns. I do not know how else to represent. Perhaps you could enlighten me.

2. 5-7 not that important. Just ignore that for now.

3. Refer to the original code, where you can see the alias (what you see displayed in the heading when the query is executed).

4. In your sample data, there was no other orders with matching account code, supplier/store name, order type. Just the two order results I posted. Did you expect different results? If so, which order numbers?

 

[SkipVought]

BTW, I formatted the Order and Received dates to show the TIME values that are in your tables!
[pre]
Order Number Order Date Receipt Date Order Type Blanket Order Supplier Account Code Distribution Amount To Go Amt
51300 12/01/14 08:58 12/01/14 09:39 STANDARD SHC 101-566100-6101-1 $465.67 $0.00
51303 12/01/14 11:08 12/01/14 11:09 STANDARD_RELEASE 10022 Verizon 101-564400-6220-1 $132.81 $0.00
[/pre]

And you do see that the [To Go Amt] is the difference in [Distribution Amount] from both tables (order - received). Is that what you're looking for?

 

[OCM]

Hi SkipVought,

Thanks for clarifying the To Go Amt.
1.Re: your comment …the [To Go Amt] is the difference in [Distribution Amount] from both tables (order - received). Is that what you're looking for? Yes, this is what exactly I’m looking for.

2.I used the code you originally posted (below) and got: Too few parameters. Expected 3.


SELECT
ORD.`Order Number`
, ORD.`Order Date`
, RCV.`Receipt Date`
, ORD.`Order Type`
, ORD.`Blanket Order`
, ORD.Supplier
, ORD.`Account Code`
, ORD.`Distribution Amount`
, ORD.`Distribution Amount`-RCV.`Distribution Amount` as [To Go Amount]

FROM
`'Order Report 0115$'` ORD
, `'Receiving Report 0115$'` RCV

WHERE ORD.Supplier = RCV.`Store Name`
AND ORD.`Account Code` = RCV.`Account Code`
AND ORD.`Order Number` = RCV.`Order Number`
AND ORD.`Order Type` = RCV.`Order Type`

TIA,

Regards

OCM

 

[SkipVought]

Tate ALL the column names in your sheets identical to the names in the SQL code? It seems that three are not! This code worked with the workbook you uploaded.

 

[OCM]

Thanks for pointing out this to me, I was using a different file as my data source. The report looks very good. One thing I noticed is the same order number listed multiple times in the report, is it possible to combine duplicate records and sum their ‘Distribution Amount”?

May be the following might help:

Order Report
Delete STANDARD_RELEASE records from Order Type column

Receiving Report:
Delete blank records in the Account Code column
STANDARD_RELEASE records should NOT be deleted in Order Type column

In the end the possible values for [To Go Amount] column:
“n/a”, “0”, “positive values”, “negative values”

please let me know if you have any questions.


OCM

 

[SkipVought]

There are some things you're asking for tht are not making sense.

Please post a sample of data that meets each of these criteria and the results you expect in the report.

 

[OCM]

Sample #1 is my original file. After running the SQL code suggested above and ‘Return data to Microsoft Excel on a new sheet’ I expect the [TO Go Amount] to look like (recon report) in my sample#2 attached.

Regards,


OCM

 

[OCM]

SkipVought, sorry for some reasons my Sample #1 wasn't attached...

OCM

 

[SkipVought]

What? This is a whole new ball game???

Please explain!

What is the significance of Sample1 & Sample2???

 

[OCM]

Sure, Sample1 is the origional format and sample2 is the end result I'm expecting. The manual process how I got to Sample2 can be found in my OP. Please let me know if you have any questions?

TIA

Regards,


OCM

 

[SkipVought]

What I asked for is using the original data (sample1) what results did you expect.

Your second example has no direct relationship to your original, so how can I make the connection? It's not just the format but the actual results based on the data in both tables in sample1, and how the tables are related.

So I'm not interested in a hypothetical that is not related to the actual data.

 

[SkipVought]

So here's what I get with your Sample1 data

[pre]
Order Number Order Date Order Type Supplier Account Code Ord Dist To Go
51598 1/14/2015 14:54 STANDARD Troy Biologocal, Inc 101-214200-6110-1 516.89 -206.89
51625 1/20/2015 16:10 STANDARD WB Mason (formerly Kelly's Stationers) 101-565300-6101-1 29.09 -8.97
51642 1/21/2015 16:33 STANDARD Holt Paper 101-973100-6101-1 794.55 -233.16

[/pre]

 

[OCM]

SkipVough,
Thank you, this is exactly what I was looking for.
Records that are highlighted in red in my Sample1 data are found only in order report, but not in receiving report. E.g. (order number: 51609, 51619 51637 51639)
If we can include those records to your final report as follows it would be great. Otherwise, I can manually add &highlight them to show that not in receiving.

[pre]Order number Order date Order type Supplier Acc. code Dist. Amt To Go Amt.
51609 1/15/15 9:51 STANDARD Eastern… ...6020-1 1,697.03 1,697.03
51619 1/20/15 12:24 BLANKET Advertising… ...6040-1 4,375.00 4,375.00
51637 1/21/15 12:12 STANDARD Carpet… ...6000-1 310.00 310.00
51639 1/21/15 13:38 STANDARD Central… ...-61-1 0.06 0.06[/pre]

Regards,

OCM