How to best transfer values between two sheets with different “active” cells

Topic

New Reply

I am working on two spreadsheets with currently about 5000 rows. Both spreadsheets contain stock items and their sales price and resulting margins. Layout is identical, however they relate to different campaigns and not all items are active in each campaign. I maintain the main campaign with updated prices provided to me by the person deciding which items will be included. I use vlookup to transfer the revised prices each time this gets updated.

The problem is while prices between campaigns should match, so I should be able to just copy and paste between the Big Campaign and the Small Campaign, the source sheet for my vlookup will only show active items. So every update removes pricing info on inactive items. This is a problem as these items still are promoted online.

Is there any easy way to only update those cells where the price is in both sheets. So if the new pricing info has a blank cell (as this item is not currently run), then the cell in the destination row already having a value remains unchanged.

Please see a copy of header as an example. The row in question is G – so if the source spreadsheet has a new price for cell G2 (e.g. $ 24.95) this should be allowed to override. However if the source sheet has no price at all for cell G4 the current data should be left as is.

Reply | Quote

Replies

[zeddy]

Sample-A-zeddy

Hi beethoven

In the attached file, I changed the sheet1 name to [Campaign] and added a new sheet [new prices] that had the latest pricing data.

I changed the formula in column [J] for Margin, to allow for any change in the GST (gov. sales tax??) in column A.

I also added a Margin% in column K, just for info.

To demonstrate the process, I added two routines Step1 and Step2.

Step1 uses helper-columns to ‘fetch’ the corresponding new prices, based on the product Item No. in column E

If the product Item No does NOT appear on the [new prices] sheet, then the existing prices as per column G and H will be used

Step2 will then overwrite columns G and H with the updated pricing.

You can adapt this to meet your setup.

If you have any questions, please ask.

zeddy

• This reply was modified 4 years, 8 months ago by zeddy.
• This reply was modified 4 years, 8 months ago by zeddy.
• This reply was modified 4 years, 8 months ago by zeddy.

1 user thanked author for this post.

beethoven

Reply | Quote

Zeddy,

thanks for that – I will study this and integrate it into my workflow. Much appreciated.

Reply | Quote

Hi Zeddy,

thanks again for your help.  I have looked at this trying to replicate with my worksheet but hope you can excuse my ignorance.  I can follow your overall logic and can see the match formula when clicking the Step 1 button but I am not sure if I do this correctly.

Apart from the extra tab with the pricing info new, do I need to add two further columns for match now and 1 unit and will these be hidden during normal use of the spreadsheet. Ie I am using them only when doing updates on pricing and still have to copy / paste value the output from column Q to column G so that my other columns can still operate?

Reply | Quote

Hi beethoven

There are many ways of automating the update of pricing info via Excel vba, all depending on your exact setup.

Here’s another way.

Let’s assume you have a separate file which has the latest pricing info. Maybe this pricing file is updated daily/weekly/monthly. You would probably have a naming convention for this pricing file. Let’s assume the latest pricing file is monthly e.g. [pricing2020-09.xlsx]

Unzip the attached files to a folder of your choice.

It contains two files: [Sample-B-zeddy.xlsm] and latest-pricing-file [pricing2020-09.xlsx]

The Sample-B file has a single worksheet which has data as before.

It has a macro assigned to the button labelled

[Choose pricing file and update prices]

When you click this button, you will be prompted to browse and click on the latest pricing-file.

The latest pricing-sheet is then imported as a temporary sheet, then steps 1 and 2 are run as before.

The new Step 3 routine then does clean-up by removing the temporary-imported pricing data.

In the exanple Sample-B file, I have highlighted cell [G4] to show that it gets updated!

You could adapt this as required.

Please ask if you need further info..

zeddy

Sample-B-zeddy

Reply | Quote

Hi beethoven

To answer your specific questions..

Re: do I need to add two further columns for match now and 1 unit and will these be hidden during normal use of the spreadsheet

..you don’t need to ‘add’ these columns, these are only used temporarily and are cleared as part of the Step2 process. If these columns are already in use, you would need to adjust the vba code to use other ‘unused’ columns etc etc etc

Re: and still have to copy / paste value the output from column Q to column G

..the Step2 vba routine does the actual copy/paste action – no manual action required.

zeddy

Reply | Quote

Zeddy,

thank you but I am still struggling.

I was originally hoping for some formula that I could copy and paste and adapt as necessary, obviously I would need to understand how the command is created.  As you are using vba, all the magic is running in the background, so I am not sure how to adapt this. I tried patiently copy the formula I can see in the “match now” column adapted to my real cell locations but do fail.  Could I use the original match formula without the Step 1 and 2 buttons?  How do I determine Lookup Array and match type? Your formula seems to look only at column A (item no) and then match type 0.  What does the 0 stand for. How does the formula go for Column C as the relevant one?

Looking at your second more elaborate offer, a similar issue occurs for me in that my sample file was not exactly like the real file. To make my sample less confusing, I cut out some rows.  Now either by trying cut/paste of the real info into the sample file or by adding columns to match the real layout, something goes wrong.  Not sure if this is related to the error I am getting from excel “the range you are pasting contains formulas that cannot be pasted…the formulas will be pasted as values…”  While your vba reports success, the visible outcome is wrong.

Sorry to be such a pain.

 

Reply | Quote

Hi beethoven

No need to be sorry! you are not being a pain. We are here to help you.

Let’s go back to your original sample file.

You have stock [Item no.] in column E

You have sales price for [1 unit] in column G

You have [USD] in column H

I am assuming that for any given stock-item-number in column E, you might have new values for the sales price and USD value in some other spreadsheet i.e. you want to update column G and column H with new values from that other spreadsheet, if that stock-item-number is in both lists.

So the first thing we need to do is find out whether or not the stock-item-number is in the other spreadsheet.

If we were only interested in fetching a <i>single </i>value for the relevant stock-item-number, we could just use a lookup formula. But we want to fetch <i>two </i>values, so it is more efficient to use a combination of MATCH and INDEX functions. MATCH figures out the <i>position</i>, and INDEX returns the <i>value at that position.</i>

So in our case, we need to find out the row number in the latest-pricing-worksheet that matches the stock-item-number (if it exists). Then, using this row number, we can fetch the sales price and the USD value from whatever columns these are in.

The MATCH function is basically..

=Match(what, where, how)

what = stock-index-number

where = location of the stock-index-number column in other worksheet

how = match-type = 0 = use exact match for the stock-index-number

NOTE: other settings for match-type are 1 and -1, which give <i>approximate </i>matches i.e. nearest-value-if-exact-value-not-found. We need to use match-type of 0 because we want to match the EXACT stock-item-number.

So in your posted file, we could use a ‘spare’ column to place our match formula.

Let’s put it in column P, cell [P2], so we have..

[P2] = MATCH(E2, where, 0)

We use E2, because that is the cell that has the stock-item-number.

We use 0, because we want an exact match for the stock-item-number

Now we just need to get the <i>where </i>part put into the formula.

If the new prices were in the <i>same </i>file on say, sheet named [fred], in say, column AK, then we would use

[P2] = MATCH(E2, fred!AK:AK, 0)

Note that if we were typing this formula manually, we need to add the exclamation mark ! after the worksheet name i.e. use fred!

If the worksheet name includes any ‘space’ characters, then we must also ‘wrap’ the worksheet-name with apostrophes i.e if say, the pricing worksheet was named [new prices] and the stock-item-number on that worksheet was in column A, we would use..

[P2] = MATCH(E2,’new prices’!A:A,0)

If the latest pricing was in a different workbook file, we would use something like..

[P2] =MATCH(E2,'[bookname.xlsx]latest pricing’!$T:$T,0)

(if the stock-item-number was on column T on worksheet named [latest pricing] in workbook named bookname.xlsx)

The result of this MATCH formula will be the row number that corresponds to the stock-item-number if it is found in that stock-number column in the specified location. If there isn’t an entry for that stock-item-number, the result of the MATCH formula would be N/A.

We copy that formula down for all the rows we need e.g. if you have 5000 rows, you would copy it down to [P5000]

Once we have the matching-row-number for the stock-item-number, we can easily use the INDEX function to return a value from another column in the pricing location worksheet. But remember, if we can’t find that stock-item-number in the new pricing location, we want to just use the existing values we already have.

So, our formula in adjacent spare-column Q is

=IF(ISNA($P2),G2,INDEX(where,$P2))

This formula says ‘if the contents in column P is N/A, then use the value from column G, otherwise use the row-number in column P to fetch the value from the specified-location-column identified by where, for example..

=IF(ISNA($P2),G2,INDEX(‘new prices’!C:C,$P2))

This says the location for the unit-price is in column C on the worksheet tab named [new prices] in the same workbook.

To fetch the USD value, we use a similar formula..

=IF(ISNA($P2),H2,INDEX(‘new prices’!D:D,$P2))

This formula says ‘if the contents in column P is N/A, then use the value from column H, otherwise use the row-number in column P to fetch the value from the specified-location-column D

You would copy the formulas down to required number of rows.

When you have done this, the spare-columns you used now contain the updated values, ready to be copied and pasted back as values. After manually copying, and pasting as values, you can then delete the spare columns you used.

Does this help?

Please let us know if you want more help. That is what we are here for.

Or provide us more details like where your new pricing data is held i.e. what workbook, what worksheet, what columns etc etc etc.

zeddy

 

1 user thanked author for this post.

beethoven

Reply | Quote

Zeddy,

excellent work – I now can manage to get my sheets updated easily following your clear instructions. I really appreciate your help.  Thank you very much

Reply | Quote