Match and sort different sized data sets

[John Caines]

Hello All.
I'm trying to match a column of data to another column of data (IE Sort).
But there's a catch!
The data sets are different lengths and I have another column that needs to be sorted/moved at the same time.

Here's an example sheet so it starts to make sense (I hope!)

Book1
	A	B	C	D	E	F	G	H	I	J
1	original list		list to match	data with list to match		Result I'm looking for is	original list		list to match	data with list to match
2	cat		fox	golden brown			cat		cat	white
3	dog		cat	white			dog		dog	black
4	pig		sheep	fat			pig
5	elephant		elephant	big			elephant		elephant	big
6	bird		dog	black			bird
7	sheep						sheep		sheep	fat
8	ant						ant
9	cow						cow
10									fox	golden brown
Sheet1

Basically in Column A I have my Original list. So all sorted data needs to be adjusted to match this column. Column A data doesn't move, never needs to be sorted.
Col C is the column that needs to move to match column A data , so these cells line up on the same matching rows as the data in col A
Col D is data LOCKED to column C
So if a cell in column C moves, then the same cell of data that was on the same row as Col D moves with it

So Column G:J is how the data cells should end up
I left COL B blank as I was thinking a formula would need to go in there?
But if its easier to delete COLB and move the formula to a different Column, then no problem.

I hope the above makes sense.
My issue here is,
I've been googling etc for the solution, but I always seem to find solutions where the data sets are the same size, or don't mention more than 1 column to move and sort.
And my Col A might have a few 100 more rows of data than Col C & D etc

Hope someone can help me out here and it all makes sense

Many thanks in advance
I really hope someone can work this 1 out,
Really done my head in this 1 to be honest with you! LOL
Best Regards
John C

 

[John Caines]

Hi Jasonb75 & Fluff!

Many thanks again for both your replies.
1st off Jasonb75, thanks for your update and taking another look.
Yes, duplicates seem to be an issue, but in my real sheet I'm having to look at presently, there are no duplicates, so no problem with this at the moment. That said, hey, I might get a sheet later that does have some dups in!
So thanks again for taking your time to look, very much appreciated.

Thanks again also Fluff for your update!
You say; "Update to the code in post#18, there were a couple of potential bugs."
I wish I knew what was going on with your code, but it is all alien to me to be honest, this is pretty advance coding, no mistake about it!
I did do a file compare to see what you had changed from your original code;
Image below

Again, really don't know what's happening here, but,,,, is part of the changes you made were in how it looks at empty cells?
Again, sorry, but way above me.

So a really big thank you for this.
I've just saved a new sheet as a macro enabled 1, with an assigned button called FLUFF!

Many thanks again
Hope you both have a great evening.
Really appreciate your help here as always

Best Regards
John C

 

[Fluff]

is part of the changes you made were in how it looks at empty cells?

Yes, it doesn't matter if there are blank cells in col A, so I removed that check, but it now checks if there are any blanks in col C.