Match and sort different sized data sets

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,155
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJ
1original listlist to matchdata with list to matchResult I'm looking for isoriginal listlist to matchdata with list to match
2catfoxgolden browncatcatwhite
3dogcatwhitedogdogblack
4pigsheepfatpig
5elephantelephantbigelephantelephantbig
6birddogblackbird
7sheepsheepsheepfat
8antant
9cowcow
10foxgolden 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
 
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
compared.jpg

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
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top