data change between two work sheets

[pgalgiani]

this is far beyond my basic macro skills, wondering if someone might help --

I have an xls file using several work sheets -- in particular- data on the worksheet "AMAZON" needs to change based on data in work sheet "IDEA" -

Beginning with row 2 in IDEA -- the data in B2,C2 - that exact combination of values - must first be matched in a row ( col A,B ) in the worksheet "AMAZON"

Once finding that row in AMAZON, the data in the K column of that row needs to be replaced by the H2 value from the the "IDEA" worksheet)

This procedure needs to be done for every further row in "IDEA"

Any help on this greatly appreciated --- ( regards Phillip ) I'll attach a copy of the file

[jaslake]

Hi Phillip

Do you wish the Code to be triggered by a CHANGE in Column H of Sheets IDEA?

[pgalgiani]

no, these are one off files, in other words, each week I need to set up a similar file ( with different data on each page ) and need to enact changes in the AMAZON sheet based on the data in the IDEA sheet - hopefully by running a macro to do that. That make sense

[jaslake]

Hi Phillip

I'm not familiar with the term

these are one off files

so let me explain what I THINK it means.

Each week you have two NEW Worksheets (Amazon and IDEA). Sheet IDEA has 0 or greater than 0 in Column H (in your sample file they're all 0).

You wish to look for a combination of Sheet Idea Columns B and C and find that SAME combination in Sheet Amazon Columns A and B. If the combination is found in Sheet Amazon then column K of Sheet Amazon is changed to the value of Sheet Idea column H.

If a match is found, matters not what the value of column K of Sheet Amazon is, it'll be changed to the value of Sheet Idea column H.

Yes, No, Maybe?

[pgalgiani]

yes you got it --
thanks

[pgalgiani]

so... what I am thinking is to save the file as a template ( incl the Macro ) and each week replace the IDEA and AMAZON data , run the macro to make the changes then saving the file under an ID name --

Following week use the template again etc

the number of rows varies from week to week by the way

[jaslake]

Hi Phillip

Our terms may be different but the idea is similar. I'm thinking a Macro Book that has the Code. The Code will then operate on IDEA and AMAZON data.
In your sample file IDEA and AMAZON are in the same workbook. In real life will they be in the same workbook or will IDEA and AMAZON be in separate workbooks?

This is not an issue

the number of rows varies from week to week by the way

[pgalgiani]

a Macro Book that has the Code.////

That sounds like what I need -- so each time I do this operation I will add the two separate files IDEA and AMAZON as a worksheets ( same name IDEA & AMAZON ) into the file --

[jaslake]

Hi Phillip

Let me play with it a bit...

[jaslake]

Hi Phillip

The Code below is in the attached and appears to do as you require.

Place the "Macro Book.xls" and your Amazon File in the SAME folder. Your Amazon File should contain a Worksheet named "IDEA" and a Worksheet named "Amazon". It can contain other Worksheets but must include these two.

The Code will prompt you for which Amazon File to open. The Code does it's thing then saves and closes the Amazon File. Let me know of issues.

Please Login or Register  to view this content.

[pgalgiani]

hmm, take a look and see if t works for you here -- will attach the two files I placed in folder ( called TEST ) When I opened the file you sent , chose the file AMAZON PO - there was action, but in the end no data changed in the AMAZON work sheet K column - doesn't seem to be transposed from the IDEA sheet H Col. See what you think, if its something I'm not doing correctly

[jaslake]

Hi Phillip

Seems to work for me...see Before and After.

Before.jpg

After.jpg

[pgalgiani]

for me too now..... first ran on my other PC, good --
now again on the first one... ALL good --- can't tell you ow many hours this will save me. Thanks and if you need anything from NYC let me know ! best regards Phillip

[jaslake]

Hi Phillip

You're welcome...glad I could help.
Used to get to NYC fairly often on business...not so often since I've retired...thanks for the thought.
If I've helped and you're so inclined, click the Star at the bottom left of my post.

Oh, one other thing...add a line of Code...life will be simpler...

Please Login or Register  to view this content.

[pgalgiani]

hmm- I guess that would have been way too easy -

I think I see now what was driving me crazy --- the data I gave to you had a slight difference in it / different from the file I actually begin with -in particular the IDEA data.

I attach two files -- the Macro will operate on the "corrected" file but not on the "added
spaces" one --
ADDED SPACES refers to the data in the PO column in the IDEA col B -- each PO has two spaces added at the end -- and I think that keeps the Macro from operating as intended -- ( if I copy and look for it on the AMAZ sheet, it won't find it unless I delete the spaces )

Something you can add to the code that will compensate so I don't need to delete the spaces ?

[ sorry, I hadn't realized what I sent you had already been doctered while I was trying to figure this out earlier on my own- I forgot I had already deleted the spaces then.

[jaslake]

Hi Phillip

I'll look at this a bit later...my Daughter and Grandson are coming for Dinner in a few minutes...should be an easy fix.

[pgalgiani]

sounds like a very balanced program you got going there / bon apetite

[jaslake]

Hi Phillip

I believe the issue has to do with the format of Column C of Sheet IDEA...I've added a line of Code in the attached

Please Login or Register  to view this content.

to format Column C to Text...seems to work...let me know.

[pgalgiani]

hi -- I'm back --- it works perfect / so good I tried adapting it to anther task that is pretty much the same thing and I had hoped that I could couple simply figure what in the code needed to be adjusted -- to no avail -- I think it must be no more than a click or two as far as changes, if one knows which ones -- could I ask again ?
I'll attach here the variant file --

in this case the data again is sourced in the IDEA work sheet and needs to be changed on the AMAZON -- the only difference is that the IDEA page has added columns so the search data is now in COL G & H and the data that needs to be copied is in COL Q

The column designations and destination for the copied data are the same in the AMAZON page.

And the only other difference is data starts of on line 5 of the IDEA sheet.

Again to me its amazing that this is possible at all -- I have a feeling you take pleasure in this from your side, but if there is a name of an organization to whom I could make a modest donation, I would also take pleasure in that. Its a small business I have but your help will definitely affect how much time I spend on these kind of very tedious tasks.

[jaslake]

Hi Phillip

How many different IDEA fie structures do you have. Will the same User(s) be exposed to these different IDEA file structures? Will the Amazon file structure always be the same?

I'd suggest you mark this Thread as not SOLVED...Thread Tools...mark as Not SOLVED.

[pgalgiani]

These are the only two IDEA formats but they are for quite purposes. I would keep them in separate folders each with their own macro workbook.

re: same user(s) - its really only me doing this, but part of my goal is to simplify the process so I can easily teach someone else.

Amazon structure is the same, yes- in both operations.

I will changeto Not SOLVED as requested - thanks

[jaslake]

Hi Phillip

Try the Code in the attached on your new File.

[pgalgiani]

my only excuse is I have been trying to do this and deal with the flu at the same time -- I
apparently uploaded the incorrect variant file to you ( I had made changes but hadn't saved them before uploading)
- in particular the "IDEA" sheet was incorrect ( the "Amazon" stays the same throughout - although the COL order for source data / target as well as the search data was correct but it was nontheless the wrong file format --

And I am assuming that is the reason the Macro does not work on what I have- one last try ? If you are game, I attach what I should have sent..... / columns hi-lit

[jaslake]

Hi Phillip

Had the bug myself over Christmas and New Year...about three weeks of it. I'll look at this later this evening...not a big deal.
Get well.

[jaslake]

Hi Phillip

In IDEA it appears we're using Columns B and D for the match and K for the replace...is this correct?

[pgalgiani]

sorry - my colors confused it --- forget them ---

You are matching the G&H of the IDEA page to the A&B of the Amazon -- and copying the Q of the IDEA onto the K of the Amazon -

I double and triple cked that last statement. I'm sure.

[jaslake]

Hi Phillip

In your new file spell it out...what should be the Matches and Replace between Amazon and IDEA...I don't want to think that hard.

[pgalgiani]

OK --

first the macro would start in the IDEA sheet - row 5 COL G ( Order/PO Number )& H (External ID) -- -- this is the combination to be found also in the Amazon page col A ( Order/PO Number ) & B (External ID)

The data in row5 COL Q (Quantity Confirmed ) in IDEA should be inserted then
at col K (Quantity Confirmed ) in the Amazon page

then further IDEA page row 6 and so on

[jaslake]

Hi Phillip

I'm getting a Security Warning on your File CORRECTED variant FILE example.xls. Please check on your end then reload if all is OK.

[pgalgiani]

yeah -- got that too ..not a clue what's up with that... I made a new file - will uploaded here - and the color hiliting now OK

[jaslake]

Hi Phillip

Try this

Please Login or Register  to view this content.

[pgalgiani]

where should this code go exactly -- I tried replacing the code that was in the lask WORKBOOK Q - will attach that here -- but didn't work on either the file I sent or on another similar one -- am I doing this wrong ?

Maybe just give me the workbook Macro --
I notice that although nothing happens to the data - when I close the file it asks if I want to save changes - so something is happening -

[jaslake]

Hi Phillip

The hidden columns were giving me a problem...I believe that's fixed in the attached.
Let me know of issues.

[pgalgiani]

like a charm .........

so nice working with you ---- snow day here.

will gladly mark as solved

[jaslake]

Hi Phillip...my pleasure.

[pgalgiani]

If you are interested in a step further on what you already helped ne with I posted a new query - Subject is "Macro Challenge" hope all well

[jaslake]

Hi Phillip

All is reasonably well thanks. If you did start a new Thread with this Title

"Macro Challenge"

I'd suggest you change the Title at your earliest convenience...Moderators won't like it

[pgalgiani]

thanks for the tip ----- you might be right -- the posting seems to have disappeared -- I will hold to the ettiquette better

[jaslake]

Hi Phillip

I don't think the Title made your Thread disappear...the Mod's would not do that...they would simply send you a message like this...

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[pgalgiani]

Hello -- can I ask for what I think is an easy modification of the Macro you made for me ?

There was a switch in the position of the column that contains the QTY CONFIRMED data that is moved from IDEA to AMAZON
worksheets ( based on its isbn & PO# ) -- It should now come from WS Idea COL W (not Q as before ) and populate COL K in WS Amazon.

and lastly a final tweak?
for any qty that is 0 when transposed to ws AMAZON, the value showing in COL T should be made to show
"IR - Cancelled: Temporarily out of stock"


I attach a sample file as well as your original macro -
thank you again / best regards Phillip

[jaslake]

Hi Phillip

This Code has been modified to do this

It should now come from WS Idea COL W (not Q as before ) and populate COL K in WS Amazon

and this

for any qty that is 0 when transposed to ws AMAZON, the value showing in COL T should be made to show
"IR - Cancelled: Temporarily out of stock"

However, you also moved this in Sheet Idea

isbn & PO#

so I won't promise it'll work as desired.

Please Login or Register  to view this content.

[pgalgiani]

like a charm -- many thanks again

[jaslake]

Hi Phillip

You're welcome.