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

 

[Fluff]

Feel there must be an easier way, but how about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H
1	original list		list to match	data with list to match		list to match	data with list to match
2	cat		fox	golden brown		cat	white
3	dog		cat	white		dog	black
4	pig		sheep	fat
5	elephant		elephant	big		elephant	big
6	bird		dog	black
7	sheep					sheep	fat
8	ant
9	cow
10						fox	golden brown
11
12
13
14
Main

Cell Formulas
Range	Formula
G2:G12	G2	=IFNA(INDEX($D$2:$D$6,MATCH(F2,$C$2:$C$6,0)),"")
F2:F13	F2	=IF(A2<>"",IF(ISNA(MATCH(A2,$C$2:$C$6,0)),"",A2),IFERROR(INDEX($C$2:$C$6,AGGREGATE(15,6,(ROW($C$2:$C$6)-ROW($C$2)+1)/(ISNA(MATCH($C$2:$C$6,F$1:F1,0))),1)),""))

 

[jasonb75]

I don't think that there will be an easier way @Fluff. I read the question that the original list should be sorted rather than reproducing it, which I've got a formula for but it's messy and relies on assumptions and potential human error.

If that is what is required then I'll post what I came up with but I'll hold back for now because I think that your method would be preferable by far, even if it not what was originally required.

 

[jasonb75]

I've been over this multiple times trying to make it work and this is the most stable method that I can come up with. The named ranges cover the majority of the possible errors that I found, however the formula needs to be filled down far enough to allow for all possible mismatches in between the 2 columns (until the formula returns a blank).

Sorting columns C:E by column E smallest to largest matches the example provided. I've left column B empty to reduce the risk of column A being sorted in error.

Book1 (version 2).xlsm
	A	B	C	D	E
1	original list		list to match	data with list to match	Sort order
2	cat		fox	golden brown	9
3	dog		cat	white	1
4	pig		sheep	fat	6
5	elephant		elephant	big	4
6	bird		dog	black	2
7	sheep				3
8	ant				5
9	cow				7
10					8
11
Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=IFERROR(IFERROR(MATCH(C2,Original,0),IF(C2<>"",MAX(COUNTA(Original),E$1:E1)+1,AGGREGATE(15,6,ROW(Original)/ISNA(MATCH(Original,Match,0)),COUNTBLANK(C$1:C1)+1)-1)),"")

Named Ranges
Name	Refers To	Cells
Match	=Sheet1!$C$2:INDEX(Sheet1!C:C,COUNTA(Sheet1!Original,Sheet1!C:C))	E2:E11
Original	=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH("zzz",Sheet1!$A:$A))	E2:E11

 

[John Caines]

Hi Fluff & jasonb75!
Hope you are both well and safe, trying times for everyone.

Good to hear from you both again, and many thanks for both your replies!
1st Jasonb75, my actual sheet this is for has about 613 rows of data, and I'm sorry, I just can't seem to get your formula's to work on it.
If you wanted to take a look at the sheet I have, I can PM it to you, as I can't share it publicly here, but I'm not asking for me to send you my sheet as a PM jasonb75. Only if you are curious as to why I can't get it to work / why your formula doesn't run correctly on this sheet I have.

To be honest, I didn't realise this would be so much trouble, but looking at both your codes, I can see there's a lot going on here! (Not that I fully understand them!)

As to your code Fluff.
Brilliant! This does exactly like I needed.
I'm going to save this sheet, as I can use this in so many ways, it's really great.
Quite often I have a list of data, and I need to resort / match new columns to this original list, but the new list could be much longer.

As said previously, when I was looking for a solution, every website or video I found,, always points to tables with the same amount of columns/rows of data.
I just couldn't finds anything like what you have managed to achieve.

This is really great,
I ran your code Fluff on a sheet with over 613 rows of data in the original colA
and 515 rows of data in Col C & D

It sorted the data perfectly.
I will definitely be saving this sheet to use many times in the future.

Really grateful to you both for finding the time to take a look and digging up solutions.
Have a great day both of you,

Best regards
A very grateful
John CV

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[jasonb75]

To be honest, John, what I came up with was more than a little clunky.

As I mentioned in post 3, Fluff and myself interpreted the question differently so I just went with the alternative theory. In all honesty Fluff's method would be preferable by far, to sort the original data in place would be much better done with vba than formulas, although I have no idea how you would even go about it with the way you wanted it done.

@Fluff, would a dictionary object work for this using row numbers as a key? It's something that I really should try to learn but I keep avoiding it.

One thing that I didn't test my theory with was duplicate entries. Also I had assumed that the data entries were all constants, if they are formula generated with blanks instead of empty cells then that would most likely be throwing the counts that I've used to index the data.

If you can send a link to the file in a PM then I would like to have a look and see why it failed out of curiosity, but as you have a solution that you're happy with it is not essential.

 

[John Caines]

Hi Jason, & Fluff,
many thanks to you both.

Yes Jasonb75, I'll gladly sent a link to my sheet, (It will be a dropbox link) but I'll also create a short screencam (like 1 min) just to explain, so it's clear what I was trying to do.

Again, really glad for your help here, I've just saved this sheet now, so I can use it again!

Best regards all
John C

 

[jasonb75]

As to your code Fluff.
Brilliant! This does exactly like I needed.

I just noticed that you flagged my non-working suggestion as the solution, John. That credit really should go to @Fluff's working suggestion in post 2

 

[John Caines]

Hi Jasonb75!
Sorry, but yes, you are right! I've checked the wrong solution!
I've just altered it, Going mad here! LOL

Sorry about that.
Also, as a note, I've just contacted Fluff as he's a moderator I see here, and I don't seem to be able to PM you, so I asked him to contact you if I can send you my sheet.

Glad you spotted my error!
Many thanks again Jasonb75

Best regards
John C