Replies

Hi Don,

In both, if possible. If I have to choose one, I like your example w/o blanks.

TIA,

Regards

Don,

Thank you, I’ll try this tomorrow in my actual file, but it works in the sample workbook as planned.

The only thing I noticed is blank records in the account code column for order numbers (51258, 51274, 51303, 51304).

I think this is because we need to delete blank ‘account code’ values in the receiving report.

This is on my original post where I listed the manual process steps for receiving report.

Once we fix this and hit the Click Here button, I think everything in accounting code column should be populated.

TIA

Regards,

Hi Don,

Attached is the sample, let me know if this is what you were looking for.
I tried your code and got ‘Object required’ error

TIA,

Regards

Thank you both.
Dan, I’ll try your code, but to answer your questions,

1. However, you don’t say where you want these subtotals —> in a separate sheet would be great.
2. sheet 2 doesn’t have order numbers —>order number in the receiving report (2nd report) is on column L. I think you are aware of this…
3. Assumes sheets sorted as shown in example  No, my original sheets are not sorted.

Regards,

Thanks Zeddy,

If you save an excel file as “.xlsx”, then all vba is ‘removed’.
Can you post your file as a “.xlsm” file, so that the vba is retained?
–> As per your suggestion I saved the attached file as macro enable workbook.

Now, if I ‘manually’ add your code to the .xlsx sample file you attached (and then rename as a .xlsm file), you macro will give the ‘not correct workbook’ message because your sheet names are different.
–> I modified the sheet names the same way as in the macro. But, you brought up a good point. The
sheet names will change every month (e.g. Order report 0115, Order report 0215 etc.) what is the best
way to make this work?

How is your macro executed? Do you use a button? Are there other workbooks open etc??
–>Currently I run the macro manually, but eventually my goal is to create a button
I’m sure we could help you, but we need a little more info.

Regards,

Steve, thanks and I have the needed columns in both my reports (order and receiving) that are in the same workbook. What would be the best way to load them into one report for reconciliation purposes?

Regards,

Thanks Steve,

It worked nicely for my second report (Receiving Report): renamed needed columns, and deleted unwanted columns.
However, I would also like to delete unwanted columns from my first report (Order report). With the exception of the following:

Order Number (A]
Order Date (E)
Order Type (F)
Blanket Order (H)
Supplier (L)
Total (W)
Account Code (X)

Regards,

Thank you Steve,

I found the problem as this is related to space in one value & not the other. I used TRIM function in my VLOOKUP to remove the unnecessary spaces and works fine now.

Regards,

OCM

Thanks for your reply Steve.

Overall, the Vlookup works fine. However, in my wb1 column D I have a value called “STC.TERM” & this value is also in wb2 > Column P (my search target). I applied sort on wb2 column P & verified this value exists (spell the same way, no space etc…)
For some reason, I got #N/A in wb1.

Can this be accomplished using IF then else etc? I was trying to compare it w/ VLookup & see if I get the same result.

Regards,

OCM

Thanks Steve for your reply & suggestions….

OCM

I’ve successfully imported the XML data into Excel and Access (please see attached striped version) and start maneuvering around. My next task is the following:

1) average invoice total amount for application fees only, broken out by division, for the third quarter of this year.
2) Using the relational database, create a deduped mailing list for as many current and complete addresses as possible.
3) Create a reader-friendly report break out the following statistics per division, based on the relational database:
a. Total number of currently accredited sites
b. Total number of applications in process
c. Number of labs that have achieved accreditation in multiple divisions
d. Total number of sites granted accreditation in each app area
e. Average number of sites per application

Do I need to create a query in access 2007, based on the query, design a report to accomplish the above?

Please help.

Thanks,
OCM

Thanks for the reply and the link. I was able to open the xml document in Excel. The next task is:
1. analysis; finding average invoice total broken out by division, for the third quarter of this year , total number of currently accredited sites, average number of sites per application etc.
2.to create deduped mailing list for current and complete addresses etc.
3. Import the data in the spreadsheets into a SQL Server relational database. Submit the CREATE scripts for all of the objects in the database.

How this is better accomplished, Excel, Access or as you mentioned, download the free 2008 express version and go from there.

Regards,

OCM

Thank you for your reply wendellB,

Actually, my SQL Server capability is limited and I do not have access to SQL Server at this moment. I’m using Office 2007, and I am comfortable with Excel and Access. I can e-mail you the XML document if that’s helpful.

Regards,
OCM

Thank you very much Hans, it works beautifuly.

Regards,
OCM

Thank to you and Hans and also for the clarifications. I’ll apply your suggestions and post back if i have any question.

Regards,
OCM