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]

Okay, here's your query. You'll need to substitute your workbook path for mine

Select 
  ords.`Order Number`
, ords.`Order Date`
, ords.`Order Type`
, ords.Supplier
, ords.`Account Code`
, [Ord Dist]
, [Rcv Dist]-[Ord Dist] as [To Go]

From 
(
SELECT 
  ord.`Order Number`
, ord.`Order Date`
, ord.`Order Type`
, ord.Supplier
, ord.`Account Code`
, sum(ord.`Distribution Amount`) as [Ord Dist]

FROM 
  `C:\Users\Skip\Downloads\sample1.xlsx`.`'Order Report 0115 $'` ord

Group By
  ord.`Order Number`
, ord.`Order Date`
, ord.`Order Type`
, ord.Supplier
, ord.`Account Code`
) ords

, 
(
SELECT 
  rcv.`Order Number`
, rcv.`Order Type`
, rcv.`Account Code`
, sum(rcv.`Distribution Amount`) as [Rcv Dist]

FROM 
  `C:\Users\Skip\Downloads\sample1.xlsx`.`'Receiving Report 0115$'` rcv

Group By
  rcv.`Order Number`
, rcv.`Order Type`
, rcv.`Account Code`
) rcvs



WHERE ords.`Order Number` = rcvs.`Order Number`
  AND ords.`Order Type` = rcvs.`Order Type`
  AND ords.`Account Code` = rcvs.`Account Code`

 

[OCM]

Thanks Skip,

I did try and got the error below:

Could not add the table '['.

code I used:
-----------------------------------
Select
ords.`Order Number`
, ords.`Order Date`
, ords.`Order Type`
, ords.Supplier
, ords.`Account Code`
, [Ord Dist]
, [Rcv Dist]-[Ord Dist] as [To Go]

From
(
SELECT
ord.`Order Number`
, ord.`Order Date`
, ord.`Order Type`
, ord.Supplier
, ord.`Account Code`
, sum(ord.`Distribution Amount`) as [Ord Dist]

FROM

`C:\Users\dee\Desktop\ market Recon January 2015.xlsx`.`'Order Report 0115 $'` ord
Group By
ord.`Order Number`
, ord.`Order Date`
, ord.`Order Type`
, ord.Supplier
, ord.`Account Code`
) ords

,
(
SELECT
rcv.`Order Number`
, rcv.`Order Type`
, rcv.`Account Code`
, sum(rcv.`Distribution Amount`) as [Rcv Dist]

FROM

`C:\Users\dee\Desktop\ market Recon January 2015.xlsx`.`'Receiving Report 0115$'` rcv

Group By
rcv.`Order Number`
, rcv.`Order Type`
, rcv.`Account Code`
) rcvs

WHERE ords.`Order Number` = rcvs.`Order Number`
AND ords.`Order Type` = rcvs.`Order Type`
AND ords.`Account Code` = rcvs.`Account Code`
---------------------------------------------------------

where do I go wrong?

TIA

Regards


OCM

 

[SkipVought]

The empty lines in my code are not accidental!

 

[OCM]

Hi Skip,
I still get the same error: Could not add the table '['.

Can you please point out where spacing might case the error?

---------------------
Select
ords.`Order Number`
, ords.`Order Date`
, ords.`Order Type`
, ords.Supplier
, ords.`Account Code`
, [Ord Dist]
, [Rcv Dist]-[Ord Dist] as [To Go]

From
(
SELECT
ord.`Order Number`
, ord.`Order Date`
, ord.`Order Type`
, ord.Supplier
, ord.`Account Code`
, sum(ord.`Distribution Amount`) as [Ord Dist]

FROM
`C:\Documents and Settings\student\Desktop\Sample3.xlsx `.`'Order Report 0115 $'` ord

Group By
ord.`Order Number`
, ord.`Order Date`
, ord.`Order Type`
, ord.Supplier
, ord.`Account Code`
) ords

,
(
SELECT
rcv.`Order Number`
, rcv.`Order Type`
, rcv.`Account Code`
, sum(rcv.`Distribution Amount`) as [Rcv Dist]

FROM
`C:\Documents and Settings\student\Desktop\Sample3.xlsx `.`'Receiving Report 0115$'` rcv

Group By
rcv.`Order Number`
, rcv.`Order Type`
, rcv.`Account Code`
) rcvs



WHERE ords.`Order Number` = rcvs.`Order Number`
AND ords.`Order Type` = rcvs.`Order Type`
AND ords.`Account Code` = rcvs.`Account Code`
---------------------------------------------
Regards,

OCM

 

[SkipVought]

I copied the SQL you just posted, changed the path in the FROM statements and...

...it runs using your Sample1 data!!!

 

[SkipVought]

Actually I would do one other spacing thing.

Reference my SQL post of 10 Feb 15 03:35.

Notice the leading space(s) on the lines after SELECT, FROM, WHERE, GROUP BY.

Notice how all the statements on those lines line up vertically. Actually, the space padding is important.

 

[OCM]

Skip,

Thank you very much for your prompt responses. Indeed the SQL error I was getting had something to do w/ spacing. As you stated, I closely review your code from line by line. I also noticed a space before $ for order report, but no space after $ for receiving report (below)

FROM
`C:\Users\Skip\Downloads\sample1.xlsx`.`'Order Report 0115 $'` ord

FROM
`C:\Users\Skip\Downloads\sample1.xlsx`.`'Receiving Report 0115$'` rcv

It’s working beautifully now and I cannot thank you enough!

Best Regards,


OCM

 

[SkipVought]

That's great!