xladept Script Adjustment - Combining Two Sheets

[NewYears1978]

Okay so xladept (you rock!!!) helped me create/adjust some macros that would compare two sheets and combine them keeping original values. It has worked flawless since we finished it.

I am trying to modify the same script for another workbook - the only difference is that in the original, the macro would compare COLUMN A for matching and then past the columns as you see here

Please Login or Register  to view this content.

My new sheet all I need to change is so that it matches a different column (most likely column E)

There are several instances of Column A in this form so I am not sure which one to change. I tried a few but with no avail. I think I had it once but then lost it...lol.

I tried to read the code and understand what is happening but my small brain was not able to comprehend what the code is doing.

[NewYears1978]

Oh wow, I am stupid. I changed all instances to E and it worked. I had forgot I need to add in my lookup formulas from all the other columns to complete the table.

#moron

[xladept]

If the New is on column E then just change:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

*Nevermind since you already figured it out

[xladept]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[NewYears1978]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

You are literally my favorite person next to my wife, and maybe my kids (lol).

Do you think you could walk me through (explain) the code. Rather than always ask I would love to be able to read the code and understand what it is doing. If it's not too difficult that is.

[xladept]

Thanks for the rep - hope this is clear:

Please Login or Register  to view this content.

*BTW - why aren't we adding the whole record?

[NewYears1978]

What do you mean? I am so far gone and lost on this that I don't even know what is happening anymore.
What do you mean by adding the whole record?

While I am here I was hoping you could help me with another question that has not come up before. Sometimes my data list (Merge sheet) has the same NUMBER in the column we are filtering from, when this occurs it adds that item in twice on the complete form, is there a way to get around that?

In short the data field I am merging by is a Sales order number, there is another column that contains a part number. Sometimes there are multiple part numbers on the same sales order so there would be say 3 lines for the same SO number with different part numbers.

What is happening is that it is creating two rows for those in the final sheet. The problem of course is then my INDEX MATCHES are incorrect because its looking up the sales number and stopping at the first one.

I don't think I explained that very well LOL.

tl;dr - Is there a way to INDEX/MATCH when there are multiple rows with the same looked up value (As in it looks it up, pulls the data, recognizes that its the same value again and goes to the next row of that lookup value)

[xladept]

We're just writing the first field of the new record but could write the whole record.

Please Login or Register  to view this content.

* I'll be considering the rest of your post.

[NewYears1978]

Ah, that might solve the whole problem.

When you originally wrote it, I requested it that way I believe, because I only wanted it to take the CUSTOMER ID number, it was merging that with an existing list (in another part of my macro) and using VLOOKUP to pull the current data. I think it was faster processing to not have to copy the entire row of data, but rather one value (maybe).

I'm sure it would work another way but it worked perfect for my customer list.

Might be possible to change it for this application to be more efficient. Maybe having it pull the whole row of data would solve the issue - I don't know? It works great how it is other than the INDEX MATCH where the lookup value is duplicated.

[xladept]

Well, that would wipe out your formulas, which don't seem to be working anyway??

Is there a different sample, there is no Merge sheet on the one I have?

[NewYears1978]

Ah yeah the sample is really hard to use without being here at my location, it imports a file on a shared drive, it then also adds a similar row to the Merge Tab and the Backorders Tab (otherwise your sub breaks when it doesn't find at least one match)

I can attach a current copy but it will be hard to test locally I think, I'll provide one with an already setup Backorders and Merge tab so you can just run the BackOrdersCombine macro...I will include some dupe rows.


Just runt he BackOrdersCombine macro - you'll see that it's duplicating 2 rows - because there are two rows in MERGE with the same lookup value and of course it's only copying the data from the first row it finds in the index match.


(The formulas work, it's something we weren't able to fix before in the code, I had to add code to create a like record on both sheets so that it continues to work

[xladept]

Well, I got all three rows and the WDONOTDEL row at the top? What needs done?

[NewYears1978]

Well, I got all three rows and the WDONOTDEL row at the top? What needs done?

Okay so if you notice the bottom two rows. They have the same sales order number which is the match column. But the rest of the columns should be different for each row. Look on merger tab

It’s matching the first row in merge and pulling that data

Maybe there’s a way to match two columns. The sales order AND part code column?

[NewYears1978]

Ah yeah the sample is really hard to use without being here at my location, it imports a file on a shared drive, it then also adds a similar row to the Merge Tab and the Backorders Tab (otherwise your sub breaks when it doesn't find at least one match)

I can attach a current copy but it will be hard to test locally I think, I'll provide one with an already setup Backorders and Merge tab so you can just run the BackOrdersCombine macro...I will include some dupe rows.


Just runt he BackOrdersCombine macro - you'll see that it's duplicating 2 rows - because there are two rows in MERGE with the same lookup value and of course it's only copying the data from the first row it finds in the index match.


I guess once easy solution would just be to remove the other lines with the same sales order number on the merge tab before merging.

[xladept]

Here's "M" :

Data Range

	M
1	SalesOrder
2	WDONOTDEL
3	W00673432
4	W00673379
5	W00673279

[NewYears1978]

Yes that is correct but it's doing INDEX MATCH on the Merge sheet and it's only matching the first row with that order number..if that makes sense?

I need it to match one row, then the next row..if that's even possible? If not I will just remove duplicates before running the macro so it only shows one row.

[xladept]

Well, I changed one of the 160's to 170 and it caught it????

[NewYears1978]

Yeh I have no idea..it might be better to just remove the second row but I think my boss will want both rows on there lol.

I'm sure index match can do an IF or AND function? I could match Column M AND Column S?

[xladept]

For M & S:

Please Login or Register  to view this content.

[NewYears1978]

I think that worked, will test further, thanks!

Edit:
Oh man I think it didn't work after all still doing same thing

[xladept]

How can I test that "same thing"?

Thanks for the rep

[NewYears1978]

If you run the macro in the same sample I give you'll see that it is not working..it's listing the same part number (Column S) for both rows (as well as all the other data)

See image of the Merge tab, then the results on the Backorders tab

[xladept]

I changed the S entries to 1,2,3:

Data Range

	M	N	O	P	Q	R	S	T
1	SalesOrder	OrderDate	ExternalOrderNum	CaptureHh	CaptureMm	OrderStatus	StockCode	OrderQty
2	W00673432	Oct 11 2017 12:00AM	5084310	12	46	1	LEV-1461LHC-SP	80
3	W00673379	Oct 11 2017 12:00AM	5084240	12	37	1	LEV-1462LHC-SP	90
4	W00673279	Oct 11 2017 12:00AM	5084104	12	14	1	LEV-1463LHC-SP	140
5	WDONOTDEL
6

And it output the 1,2,3

Data Range

	M	N	O	P	Q	R	S	T
1	SalesOrder	OrderDate	ExternalOrderNum	CaptureHh	CaptureMm	OrderStatus	StockCode	OrderQty
2	WDONOTDEL	0	0	0	0	0	0	0
3	W00673432	Oct 11 2017 12:00AM	5084310	12	46	1	LEV-1461LHC-SP	80
4	W00673379	Oct 11 2017 12:00AM	5084240	12	37	1	LEV-1462LHC-SP	90
5	W00673279	Oct 11 2017 12:00AM	5084104	12	14	1	LEV-1463LHC-SP	140

[NewYears1978]

Maybe I incorrectly tested or didn't run the right code. I will check again when I am back at work next week! TY!

[xladept]

You're welcome - we can always paste the entire record!

[NewYears1978]

Pasting the entire record might work.


The new script still didn't work for me, I always end up with the same data on each row and it also seems to make it delete my formatting, which the old code does not.

I think better to leave it as is. Pasting the whole record would mean I no longer need the INDEX MATCH formulas though..not sure why we didn't do it that way originally..seems more efficient? lol

But if it ain't broke, don't fix it????

[xladept]

But it's broke, aint it?

[NewYears1978]

It's half broke..well it's partially broke...LOL. My boss wanted to use the form as it was so it's already in use, changing it now will mean everyone has to start fresh :D Sure would be more efficient though.

[xladept]

Well, I've got an interview this morning. I'll see what I can do later

[xladept]

Here it is for the first 36 fields:

Please Login or Register  to view this content.

[NewYears1978]

I will try that

Edit:

It works but it doesn't seem to do what I need it to do, it just replaces all the lines with the new lines.it doesn't "merge" the two sheets of old and new data. I think that's the reason I had to use the VLOOKUP to match only one thing?
In other words if I run this, put notes in, run it again, it just removes all the lines and readds them.

[NewYears1978]

I thought about revisiting this but didn't know if you thought you might have a solution xl-

I couldn't get the prev script to work - you've probably forgotten all about it now

Refreshing memory - when it merges if there are two customers the same it pulls them twice but it only puts the data from the first match so:

Please Login or Register  to view this content.

Notice that the Sales order number is the same, which is what i use for the lookup on my INDEX MATCH ...but the Part numbers are two diff ones..when I merge right now what happens is it puts both these lines but all the data from the first line..I need it to retain all the data from both lines.

Any ideas on that? I think you sent me a script before that was supposed to match all fields but it didn't work out for me (I will test again).

EDIT
Actually I lied, I just retried your last formula

Please Login or Register  to view this content.

And it worked this time...I must have done something wrong before! Lol..ugh so this post..was a waste! SORRY!!!

Only thing I need now is it to NOT overwrite my Columns AL, AM and AN as those are note fields that need to return. New script erases those. I tried changing the 36 to 33 but that didn't work. Probably something simple?

[xladept]

Column AN is the 40th column - the sample had no data in those three columns and the e-mail link wasn't included

[NewYears1978]

Right, the last column on the tab that gets merged is AJ, but on my main Tab where the merging happens there is a Column AK-AN, AK is a formula that just gets copied down..and that field is working..however Column AL AM and AN are notes so people can type in notes for that line, the merge script is blanking those lines out rather than keeping them for some reason.

Attached a new sample

[xladept]

Here's the code that obliterates those comments:

Please Login or Register  to view this content.

[NewYears1978]

I commented those out and it seems to work - yay. You rock! Well, I commented the whole block out, I hope that was correct - haha.

Oh wait, that just appended them on bottom instead of deleting..lol Woops.

[xladept]

If it's working great, if not we'll attempt a rewrite. Thanks for the rep!

[NewYears1978]

No, when I comment those lines out it appears to just append all the lines to the bottom instead of deleting and adding

Oh see now, because this script is removing the whole line then repasting it..I don't believe the old script we used did that..(I guess?)

[NewYears1978]

@xl - here's the file we talked about.

(The second tab is hidden called Merge)

You can kinda see what is happening, I have an import macro that pulls new data from a file on shared hard drives here at work, and it pastes it into the Merge Tab. Then your script Combines the Backorders and Merge tabs..just by mastching the SalesOrder column.

The issue is that the SalesOrder column sometimes is duplicated on the Merge tab, but there is a diff part number (StockCode tab). So since I use INDEX MATCH what is happening is your script is pasting the same SalesOrder multiple times (for each instance of StockCode) and then my Index Match is only returning vlues from the first match on the Merge tab.

Does that make sense?

Or is there such index match that can match the same value and take in to account skipping the one already matched? (So if you matched a value it then ignores that cell on the next run to match the same value on the next row???)

[xladept]

Here is the result of the BackOrderCombine macro - please critique. The program doesn't use index match so, I think the output is correct at this stage - if I'm wrong please tell me where:

[NewYears1978]

It's working but I have some conflicting stuff breaking it so I have to work on making the rest of my stuff work with the new code. (Currently if I run it twice it will start to copy down my index formulas again which will break it)

When I run the code (just BackOrderCombine..it keeps readding back in INDEX match formulas even though I have deleted them..can't figure out why)

[NewYears1978]

Check this out, if I manually delete some rows and rerunt he macro..all the new rows now have INDEX MATCH in them...no clue why there is no code telling this to happen!?!?

Look at rows 7 and beyond.

I can't figure out what is happening. Also if I don't have that BLANK WDONOTDEL line in there it won't work..there always has to be one match or it won't work..I think it may also be what is causing it to break?
If I delete all the rows and the index matches out of the top WDONOTDEL row then when I run the macro it just puts all blanks in instead of the data.

[xladept]

This is the correct version of the BackOrderCombine:

Please Login or Register  to view this content.

The earlier version keeps propagating - if you'll replace all versions with this then the issue may well be resolved!

[NewYears1978]

I think that worked, will do some more testing thanks!

Just gotta add some formatting that will highlight diff colors for the rows with the same SalesOrder so they'll now..woo.

[xladept]

Stick this red code in the routine:

Please Login or Register  to view this content.

[NewYears1978]

I think I have a problem that I had previously...it seems like the routine is rewriting EVERY line which means that my custom columns are being deleted..but they need to stay (Columns AL-AN) and also formatting is being wiped. I remember this is what happened a couple pages back when you tried to rewrite it.

I could swear I tested this at work though and it didn't delete everything but here at home it is...weird?

[xladept]

Make sure you're using the latest version - it only writes the first 36 columns.

Thanks for the rep!

[NewYears1978]

I am, I just pasted the new one..not sure why it's going wonky...will keep checking maybe something funky going on here.

I can see it rewriting the whole line...even though I have the right code..I have no clue why...Ahhhhhhhhhhhhh this file will be the death of me.

I'm going to post a video showing what it's doing lol
https://youtu.be/lXywhKDDdCM

(Are YT links allowed?)

[xladept]

But it only does that at home??

[NewYears1978]

Maybe. I will have to check at work on Monday. It was RIGHT at the end of the day so I was testing fast..but I seem to recall it working..why would it be diff at home and work...same Excel? So strange lol

[xladept]

Perplexing!

[xladept]

I've found the problem - there was an indexing error, totally my fault - here's the corrected correct version:

Please Login or Register  to view this content.

* Do we now have new problems??

[xladept]

To handle the perceived problems try this version:

Please Login or Register  to view this content.

[daboho]

To handle the perceived problems try this version:

Please Login or Register  to view this content.

Xladeft how if union sheet1 and sheet2 this is posibel ?

Please Login or Register  to view this content.

[xladept]

@ daboho - I don't see how your code can work both x and x1 are 2D arrays so y would be a 4d array

[daboho]

hehe thank before

[NewYears1978]

Okay I just deleted the rest of my post because it was super confusing

It seems like everything is working now with the new version. I removed the color coding portion because I needed multiple colors which I fixed that up on my own.


Yay - you're awesome.

[xladept]

Let's hope that that does it - but, if not, let me know

[NewYears1978]

I think so - I've tested it multiple times - just gotta make some tweaks to my own part of the code now. You are my best friend. lol


Definitely looks like it is working I have tested it a few times now Just trying to sort out my color issues and then it will be done - wooooo!

[NewYears1978]

I just remembered why I was using the INDEX Match formulas instead of copying the whole row of data in the script... Because sometimes the data in the other columns changes, but with the script it will not update those lines if the SO already exist in Column M it just leaves the other data.

One column has ETA dates, which change on occasion....doh...!

[NewYears1978]

Hey xl - so sorry to bother you AGAIN (sent some PM about it). I am trying to repurpose the vode for ANOTHER form. This one is simple, I am simple Copying Column A through R and using A as the match column (to filter). There are some cells with blanks I don't know if there is a way to correct that too (when it copies blanks it always inputs numbers)


Heeeelp!

[xladept]

Maybe:

Please Login or Register  to view this content.

[NewYears1978]

Pro skill.

[xladept]

Thanks for the compliment and for the rep!

[NewYears1978]

Buddy I need you again! Some of my co workers have added more columns to the end (It used to end in Column W, but now they have X and Y columns, and some just have X column..) and when we update it deletes the data in those 2 new columns.

I can't find which part of the code to edit to modify that bit. Help! =D

Please Login or Register  to view this content.

[xladept]

Is this the correct version? If you look at post#62, we were only copying the first 18 columns ("R") - I forget why?

[NewYears1978]

So many version now I can't even keep up...maybe this user has old version on accident

That's the version I am using too though so I dunno if there was a reason I had to use that one or what..it's been too long. Everyone has been using form just fine all these months It keeps everything just fine up until column W

Oh wait, I know why, that #62 is the same script we modified for a different form I have...the Back Order form. One in question here is the Custom list form. I guess it could be modified also in the same way.

So confusing since there are two diff scripts I use in two different forms (based on your original code)

BackOrderCombineX and NewYearsCombine are the two diff ones. One working on here is the NewYearsCombine one I guess it would work here if I change the sheetnames though?

[xladept]

Try:

Please Login or Register  to view this content.

[NewYears1978]

Brilliant.

[xladept]

Glad it worked! Thanks for the compliment and for the rep!

[NewYears1978]

Hey xl, (or anyone else) - long time no chat on this one. I've run into an issue I can't decipher

Please Login or Register  to view this content.

This code works great however, something I never thought of before that was solved with VLOOKUPS before this non VLOOKUP code. If the line already exists, it ignores that line, the problem is there is one column with dates that change, and we need those to change.

I guess I could change that one column to VLOOKUPS but that is messy. Any other solution you can think of?

[xladept]

Can you post a new sample?

[NewYears1978]

I took the names out for security The colum in question is the one that says Next PO date, that date changes but since we're only matchin by the first column it ignores it.

[xladept]

Try this:

Please Login or Register  to view this content.

[NewYears1978]

Why u so awesome man? Thanks

[xladept]

You're welcome and thanks for the rep!

[NewYears1978]

Hey xladept, been a long time! Wondering if you could help me once again =D

You helped me create those awesome macros for my sheets awhile back and I wanted to change a feature. Right now if you recall it combines to tabs in the sheet and removes data not found in the second sheet anymore (basically it updates the main tab with info from the second)

Some coworkers asked if there was a way to make it move the "deleted" ones to a new sheet when it removes them as sort of an archive? So it would match them like always but copy those over to another sheet before removing them.

This is the macro in question.

Please Login or Register  to view this content.

Thanks man, hope you and family are safe during this corono junk!

[xladept]

I see that you commented out the last 9 lines - see if this runs - it's just a quickie

Please Login or Register  to view this content.

[NewYears1978]

Yeah I use the colors in some forms and not others, not in this one

It works but it's missing a row for some reason (I test just by adding three fake rows with fake data, then update and run macro and it is only taking 2 of the rows i added, it's leaving the 3rd row in the sheet for some reason)

[xladept]

Thanks for the rep! If it's not reading all the rows it's not working. Can you post an example that does that?

[NewYears1978]

Yeah see two files here.

Main one then the other is the one you use when you update (click update macro and select the other file called Manifest)

Thanks man I love you!

[xladept]

I'm rather fond of you too!

See if this works:

Please Login or Register  to view this content.

[NewYears1978]

Did this work for you? For me it went completely bonkers lol

[xladept]

I followed your instructions with the modified code and it worked??

[NewYears1978]

Did you add in some dummy rows to test it? I added in 3 or 4 dummy rows (just copied the row above it but changed Column A since it is the matching column..and it didn't.

The first time I ran it worked but after that it would not...weird?

Let me test it naturally for a couple days waiting for some actual data to not be on the list and I will see if it works =D thanks buddy!

[xladept]

When I ran it there were already 4 dummy rows and it put every one on the deleted sheet

[NewYears1978]

I tried it again today and no bueno..doens't work and it is putting a date in the header column of the deleted sheet so something def going wonky.

It works one time (sorta) but puts a date in the column a header for some reason..and then it won't work after the first time.