Auto copy data based on multiple criteria from one sheet to a specific location in another

[onepoefan]

Hi Excel Experts, I've been researching this for quite a while to no avail.
First, I'd like to confirm if this is possible without using VBA-I don't think it is.
If I need to use VB, can you point me in the right direction. I'm new to VB but eager to learn.

I have three sheets in one workbook. The main sheet, "SA" has a summary of all soda sales by week. I need a formula or VB code smart enough to search sheets "SD" and "SD2" and return any new unique records into the corresponding section in sheet "SA".

In this example since Costco and Walmart are now receiving Vanilla Soda, I'd like the new product, Vanilla Soda, to be added as a line item under Costco and Walmart sections respectively on sheet "SA". I'll probably just use a sumifs + sumproduct + indirect formula to the weekly total of [cases delivered "TOTAL CASES" from sheets "SD" and "SD2".

I don't need the entire row returned. I only need the first four fields on sheet "SA".
"SD" and "SD2" will each have thousands of records refreshed daily from other master spreadsheets.
I'm not sure how many records will need to e transferred from those sheets to sheet "SA" daily.
I've been asked not to use a pivot table.
I don't know if it matters, but I'll be creating a dashboard based on the "SA" sheet.
I hope I've explained this well.
Any help you can offer is greatly appreciated.

master sheet.PNG
sheet2.PNG
sheet 3.PNG

[JohnTopley]

Please post a sample file: many of us cannot view PNG files and of course we do not have any data to work with.

To upload a file: Click "Go advanced" then scroll down to "Manage Attachments"

[onepoefan]

Thanks John I uploaded the file SodaSales.xls.

[JohnTopley]

You will need VBA to do this: I would repost on the VBA/MACRO forum.

Why two sheets SD and SD2?

And I assume product number should not be 11111 for "Vanilla Soda" ??

[onepoefan]

Thanks John
I have both SD and Sd2 because my IT guy says there will be too many records to merge them into one sheet.
You're correct anout the product number for Vanilla Soda. That's a typo I need to correct.
I will repost in the VBA/macro forum.
Thank you for your time!

[JohnTopley]

Just an observation: your posts indicate that the volume of records is very large (750,000 - 1,000,000??). In your sample, there was no "flag" to indicate whether a product was new or existing so any comparison would have to match/lookup all product codes/records to determine the old/new status.

You need to look carefully at how this is handled as it could (will) impact on performance. Be sure to give a very clear specification to the VBA forum on volumes and how the data is received/to be processed.

........ SD" and "SD2" will each have thousands of records refreshed daily from other master spreadsheets.

I can write VBA but am not an expert and given the data volumes I thought it better to point to those who are more proficient than myself.


I see your posting is now on the VBA/Macro forum: so just to stress the importance of giving a much detail as possible on volumes (transaction per day, new products per day/week/month, number of customers (COSTO,WALMART etc ), expected volumes in all sheets.

I have briefly looked at a VBA solution and (if I were doing it - and I might as "exercise"!) would like to see some method of identifying new products (the identifier could be removed when added to the "SA" sheet).

my 2 cents worth!!

I'll follow with interest.

[onepoefan]

Hi John, thanks for the information. I hadn't considered flagging the new product records, and I will be very specific when I describe what I'm looking for. I really appreciate your input.

[JohnTopley]

I have a VBA version (I know ... like a dog with a bone!) so ....

Please Login or Register  to view this content.

I added a column (H) in SD and SD2 with "N" to identify a new product. The N gets set to blank when added to SA.

Click RUN on SA sheet (Data in SD/SD2 ready to go).

[onepoefan]

Hi John, That's amazing! It works. I'll test it out on my file tomorrow. Do I need to flag new records in my SD and SD2 files in order for this code to work? Thanks

[JohnTopley]

Either just add flags ("N" in column H) to existing records or add new ones: a random selection would be a good test as we need to be sure it stands up to a real life situation!

If new products can be placed anywhere in SD / SD2 worksheets i.e. not just the end, then please test this situation:. and try and do a "volume" test which is representative of a daily update.

It dose not matter if the data added is the same product so you can simple repeatedly copy and paste a block of new records,
You could "serial number" the "Total Cases" from 1 to "n" so you have an easy way to validate all records have been added.

Look forward to hearing from you (only good news please!!)

[JohnTopley]

I did a "volume"/"variety" check myself: All appears OK.

Check the attached where yellow highlighted rows in SD & SD2 were added to SA. I just changed the product number (not descriptions) to identify new additions.

[onepoefan]

Hi John, I tried the code on my file but received. I think it's because the columns on sheets 2&3 in my sheet are in a slightly different order than sheets 2&3 in SodaSales. I get a run time error, and debug shows mr this line:"ws1.Range(FindRng).Offset(1).EntireRow.Insert". Can you take a look? Thank you

Also, can you point me to a sample formula I can us to flag the unique records? Thank you

[JohnTopley]

Please Login or Register  to view this content.

See attached.

I haven't looked at the unique question (or should it be duplicate?): whichever, how are these records to be identified ( what data)?

Having problems with attachment : "Hmm, we can’t reach this page" message

If I can't solve it, copy above code into your workbook.

[JohnTopley]

.... managed to attach file.

[onepoefan]

Hi John,
Thank you for updating the code. It works perfectly, and I am grateful for your help. I apologize profusely for not uploading the file in the original test file in the correct format. I hope pass along the kindness by helping someone else when I become an Excel Jedi Master.

[JohnTopley]

What about .... ????

Also, can you point me to a sample formula I can us to flag the unique records? Thank you

Re the changes: They were not too difficult to do but where it is practical to do so it is advisable to post files which do represent the actual "production" files. this avoids problems when OP is unable to "translate" the test file formulae/VBA to the real file and hence responders possibly having to repeat work.

I did note you had changed the VBA to reference the new sheet names and changed the parameters to the functions so well done.

You will see I added column headings rather than numbers to make it easier to reference the data. You can change the others as below


ws1.Cells(rr, 1) = .Cells(r, "AG") ' Warehouse

to

ws1.Cells(rr, "A") = .Cells(r, "AG") ' Warehouse

This makes the VBA more "self-documenting": who can say that column 33 is "AG" !!!!

[onepoefan]

Thanks for the explanation of the code.

When I asked... "Also, can you point me to a sample formula I can us to flag the unique records?" I was looking for a formula I can use to flag the unique records that I'll need to set to "N" in order to trigger the code. I don't see a way to flag the unique records with an "N" by using a conditional formatting rule so I assume I'll need to this using a formula. Is that correct? If so, can you point me in the right direction? I'd like to try and write the formula myself. Thank you

[onepoefan]

Hi John, If you have time, will you please take a look...
I haven't been able to find a formula that flags unique records. The closest I've come is:
=IF((COUNTIF($G$2:$G2,G2)>1)*(COUNTIF($L$2:$L2,L2)>1)*(COUNTIF($AG$2:$AG2,AG2)>1)," ","N")
Expected results: Excel will look at columns
G (SHIPTO_NUMBER) &
L (PRODUCT_NUMBER) &
AG (WAREHOUSE)
and only flag the unique values=new products being shipped to a warehouse.
We're using the flag to trigger the VBA code you wrote.
This formula isn't smart enough to evaluate all of the conditions across all of the columns.
Can you suggest a better formula?
I uploaded the production file VA4 and pasted the formula into column AT of the WD tab.
Thank you