Copy and append an array of cells based on a separate date table

**concatch** · 12-30-2017, 12:44 PM

Hi there,

I have a sheet that contains products (column B) and a list of ingredients that go into these products (column A) - see attached

I have roughly 50 products and I need to replicate these according to whether a separate date table is populated i.e. If the date table has Jan '17 and Feb '17 populated, I need the product and the corresponding ingredients to be duplicated twice (one for Jan and then again for Feb). If I then add Mar '17 to the Date table, then the same product and corresponding ingredients need to be appended below the Jan and Feb entries.

I have attached an excel doc with a simple example. The problem I am having is that I have 50 products and need to replicate these 24 times each (which I can currently do manually, but going forward I'm looking to automate this process by having a macro only populate what is in the date dimension table).

In the excel provided I have selected 2 products and have manually populated Jan and Feb '17. Ideally the macro would look at the Date tab and then append a further 22 iterations of each.

Please keep in mind that I do have a master product list and the ingredients do not change.

Thanks a million!

**sintek** · 12-30-2017, 01:14 PM

Hi concatch

Your actual requirement escapes me...Can you perhaps explain in more detail...
Where do the products/ingredients come from?
No sheet in attached housing this...

If the date table has Jan '17 and Feb '17 populated

Populated how?

**concatch** · 12-30-2017, 01:31 PM

I Have updated the test file - please have a look, hopefully the requirement is more clear... Thanks

**sintek** · 12-30-2017, 02:36 PM

I am very certain that someone else will offer a more efficient code...Mind is foggy right now...Will have a look at improving it tomorrow...

Please Login or Register  to view this content.

**mike7952** · 12-30-2017, 05:45 PM

Give this a try

Please Login or Register  to view this content.

**concatch** · 12-30-2017, 05:55 PM

Busy testing now - so far seems to be doing exactly what I'm looking for. Will provide an update in the morning. Thanks a million!

**sintek** · 12-31-2017, 12:58 AM

After I put my head down last night I realized that I had a extra unnecessary loop....

Please Login or Register  to view this content.

**sintek** · 12-31-2017, 01:21 AM

...Deleted...

**concatch** · 12-31-2017, 02:46 AM

Hi,

I've tried for a while to adjust your code as the actual column format I'm working with has 4 columns in this format...Sorry I know I should have mentioned this sooner. Preliminary testing is going well though. Will provide feedback later today. Thanks for your efforts.

Serial No. |Raw Material | Product | Value
1 RW: Baking Powder SWEET: KOURABIEDES 5

**mike7952** · 12-31-2017, 03:25 AM

Can you upload a file with the exact data structure of your workbook?

**concatch** · 01-04-2018, 02:19 AM

Hi mike7952, apologies for the delayed response, I've created a new test file which contains the exact format and outputs.

'Master List' will be unique list of products with their corresponding raw materials

'Date' will run to Dec 2018 - I will likely update this in 2019 then rerun the macro

'Product Lookup' - I have selected 3 products and manually displayed the outputs and the columns

Thanks again for your assistance, much appreciated!

**sintek** · 01-04-2018, 03:17 AM

My amended code...

Please Login or Register  to view this content.

**jindon** · 01-04-2018, 04:51 AM

concatch,
See if this is how you wanted.

Please Login or Register  to view this content.

**concatch** · 01-08-2018, 05:40 AM

Hi sintek - I'm getting an error on the line Set PrGrp = CreateObject("System.Collections.ArrayList")

Error: Run-time error '-2146232576 (801131700)

I have tried to google but with no success in resolving the issue. Any suggestions?

Thanks!

**concatch** · 01-08-2018, 05:45 AM

Hi jindon,

Thanks for your solution -

I receive the following error:

Run-time error '-2147217900 (80040e14)

Syntax error (missing operator) in query expression 'Product' = 'SPECIALITY: FALAFEL (4's - COOKED)".

I suspect it has something to do with this specific Product having certain characters that are not catered for in your code...not quite sure how to get around this without changing the source data... Any thoughts?

Thanks a million!

**jindon** · 01-08-2018, 05:50 AM

Try this one then...

Please Login or Register  to view this content.

**sintek** · 01-08-2018, 06:38 AM

No errors in sample...

Perhaps this is reason???
https://social.msdn.microsoft.com/Fo...forum=exceldev

**concatch** · 01-09-2018, 01:12 PM

It worked - was an update issue. Thanks again for your help over the past few days, much appreciated!

**sintek** · 01-09-2018, 01:16 PM

Glad you got it sorted...Thanks for rep +

**jindon** · 01-09-2018, 06:23 PM

You are welcome and thanks for the rep.
Yes, Dictionary should work on Wnidows regardless of the version.

Copy and append an array of cells based on a separate date table

LinkBack

Thread Tools

Rate This Thread

Display

Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Re: Copy and append an array of cells based on a separate date table

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] On cell change copy and append to bottom of table

[SOLVED] Copy All But the First Row of a Table Databody and Append to the End of Another Table

[SOLVED] Excel2010 IF Formula to return value based on a separate date and two separate text values

Copy cells append to table

Trying to Append an Excel file based on a look up table

Macro to cut rows based on NONBLANK cell and append to list in separate worksheet - x

Append data from a column to separate table array

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions