Need help with vba macro to generate report within a spreadsheet

[Lani Laja]

Hello, im trying to automate a process in excel using vba but im getting stuck. Can someone help?
I have a spreadsheet with two work sheets. One worksheet has raw data with different column names. Second worksheet is the report I want to append data to, and has only a few of the columns I need for my report. How do I get data from the raw data to the new worksheet, if im searching by a field called UOM?

Thanks

 

[Shteven]

Hello Lani Laja, welcome to Chandoo!

Please upload a sample workbook so that we have a better idea of your question.

 

[Hui]

You could try using the formula's shown here:

Lookup and summarise a range based on Criteria
Single Criteria
http://chandoo.org/wp/2011/11/18/formula-forensics-003/
Multiple Criteria
http://chandoo.org/wp/2014/11/10/formula-forensics-no-003b-lukes-reward-part-ii/

When posting please use more descriptive names than Help!!

Please refer to the site rules "New Users Please Read" at: http://chandoo.org/forum/forums/new-users-please-start-here.14/

 

[Lani Laja]

Hi SHteven, Please find attached. Thanks

 

[Lani Laja]

Thanks HUI,

but im trying to place the output in a different workbook, so that formula isn't going to work

 

[Hui]

Yes it does
But it has to have the worksheets added as appropriate

 

[Lani Laja]

Yes it does
But it has to have the worksheets added as appropriate

Thanks Hui. How do I reference the output worksheet?

 

[Hui]

What field in the STOManifestRawData worksheet is the key field?
Which values in the CARGO MANIFEST is being looked up?

 

[Lani Laja]

What field in the STOManifestRawData worksheet is the key field?
Which values in the CARGO MANIFEST is being looked up?

The field in STOManifestRawData is The Purchasing Document. The values in the Cargo Manifest are QTY(=ORDER QUANTITY), UOM(=ORDER UNIT), DESCRIPTION(=MM#+SHORT TEXT) & PO/PR NO(=PURCHASING DOCUMENT)

Thank you

 

[Hui]

Will there be a number of PO/PR's entered in the Cargo Manifest or just a single one?

 

[Lani Laja]

Hi Hui, we'll be searching just one at a time. but want to be able to add rows after we search for another PO/PR no

 

[Hui]

please see attached file:

 

[Lani Laja]

Thanks Hui