Sort List of items from one sheet to another.

**colddeck84** · 11-18-2017, 12:51 PM

Hi around 6 months ago the great jindon helped me creating this great code:

The code is supposed to sort Country/Leagues and Teams based on "table" in "Matches" sheet.
(see original post and/or the test sheet"lists" for sample)

Please Login or Register  to view this content.

Here is a link to the orginal thread https://www.excelforum.com/excel-pro...ba-loop-6.html
As I stated earlier this code has been working great since day 1, However I have made some changes to the layout. Added one more column and moved some columns to the right to be able to insert a left sidebar. these changes rendered jindons code useless :/ and I dont know how to fix it. This part gets highligted when I try to run it with his code:

Please Login or Register  to view this content.

I tried the simple solution to change

Please Login or Register  to view this content.

I was hoping someone (jindon?) could please help me fix this ?
Ive included a test sheet to show how the layout looks like

frederik

**sysss** · 11-18-2017, 05:38 PM

Hi,

I remember watching this thread in the past...

Try this little correction to the orginal code of jindon based on your posted example.

Please Login or Register  to view this content.

I noticed that result in your sheet 'lists' is a way different from the one received by this macro.
You need to double check if it gives you the result you required.

s.

**xladept** · 11-18-2017, 05:55 PM

I ran it using d11 and the results are all skewed - I wonder about the dates and times fields??

**sysss** · 11-18-2017, 06:06 PM

Hello,

macro should not do anything with dates.
Now when I look at a sample data I see there is a problem in the League names. In the old post example there were a country prefix which is now missing and some League names might have same name in more than one country... so, macro in my post #2 will not give a good result...

s.

**xladept** · 11-18-2017, 06:08 PM

I ran it like this - you'll see what I mean, but I haven't resolved it

Please Login or Register  to view this content.

**jindon** · 11-18-2017, 06:30 PM

colddeck84,

According to your data/result, it would be far different from my original code and don't understand where es/en/rs/sc etc. come from.

You need to explain what you are really trying to do.

**colddeck84** · 11-18-2017, 06:42 PM

Hi guys thanks for all the replies. sorry bout the sample code I tried to paste it as close to the original Jindon code but I guess I messed up somewhere. Here is the code with the changes Ive made to suit my needs/workbook:

Please Login or Register  to view this content.

This is a little different from the code in post number one. But its working. I had to do a little reasearch to figure out what this means:

Please Login or Register  to view this content.

And about my problem: actually in cell "D10" in my original workbook there was some hidden/white colored text that messed up the results. After removing it the code works just fine again.

But I have a small follow up question to you Jindon:
I was wondring if you could update the code so that it only adds the home team into sheets "lists" meaning that the loops stops at col H, And make same results Country/League/TeamB to another sheet "lists2" or something.

Basically It would be nice that it get sorted like this:
Sheet(lists):
Country/League/Team A
sheet(List2):
Country/League/Team B

Originally Posted by jindon

colddeck84,

According to your data/result, it would be far different from my original code and don't understand where es/en/rs/sc etc. come from.

You need to explain what you are really trying to do.

As sysss pointed out the country prefix is missing, my code AddCountryCode/RemoveCountryCode adds country codes to the leagues to make sure that the sorting is done correctly as there are several leagues with exactly the same name. In the original post I think I had the prefix always loaded , but I have made some changes to remove it after your code is finished

Edit:

Also in the sheet lists , you will see that every Country is duplicated. This is because of another code called splitleague:

Please Login or Register  to view this content.

(as you might see scripting dir is to advanced for me to use or understand)

**jindon** · 11-18-2017, 06:59 PM

Just upload a workbook with data and the EXACT result that you want clearly showing the logic behind it.

**colddeck84** · 11-18-2017, 07:17 PM

Thanks for your time looking into this.
Ill upload how the list looks like after the splitleague code has been run after your code.

I renamed the sheets like this:
Matches
listsHome
ListsAway

And in the "listshome" I renamed everything from Col J to "Should be empty" meaning I dont want it in that list
I did the same for "ListsAway" just for Col H for this one.

Does this make any sense to you ?

**jindon** · 11-18-2017, 07:23 PM

Is column I in Matches sheet completely blank?

**colddeck84** · 11-18-2017, 07:27 PM

For the most part yes but , Sometimes I have "-" or results here like 3-1, 2-2

At least that was the original plan ., but I have not been using this Col as much as I expected

**jindon** · 11-18-2017, 07:33 PM

And why you have doubled the headings in both Home/Away?

You didn't answer my question in my 1 st post #8.

**colddeck84** · 11-18-2017, 07:36 PM

Im sorry I don't quite understand what you mean , could you please clarify ?

**jindon** · 11-18-2017, 07:40 PM

Heading in both sheet look like

Country Argentina Argentina Asia Asia Australia Australia

**colddeck84** · 11-18-2017, 07:41 PM

If I understand you correctly you mean the Leagues headers in the lists ?

Basically your code sort everything after country prefix has been added at the end of the league name for instance Premier League (en). But this does not look so great when I pull this information into a dropdown box later from another sheet. Thats why I have my code Split League Duplicating everyLeague then I use Vlookup later looking for Premier League (en) to get just Premier League

**colddeck84** · 11-18-2017, 07:44 PM

Originally Posted by jindon

Heading in both sheet look like

Country Argentina Argentina Asia Asia Australia Australia

Yes it was what I suspected. You will see the small difference where the country have country prefix in the col closest to the left. My code split league handles all of this.

**jindon** · 11-18-2017, 07:48 PM

So you are missing one sheet that enables to convert country code etc...

**colddeck84** · 11-18-2017, 07:50 PM

Without the split split league code, Headers are like this: Algeria, Argentina, Armenia, Asia and so on all to way to Z.
But problem with this was the country code prefix at the end of the league names and it drove me crazy. So I figured this ingenious way: splitLeaguecode to duplicate all the countries And in the duplicated col remove the country code at the end of the league names

**colddeck84** · 11-18-2017, 07:51 PM

Originally Posted by jindon

So you are missing one sheet that enables to convert country code etc...

here it is:

Please Login or Register  to view this content.

**jindon** · 11-18-2017, 07:51 PM

So how could the result like that from Matches data?

**colddeck84** · 11-18-2017, 07:53 PM

here is the data sheet added

**jindon** · 11-18-2017, 07:56 PM

So do you wand the result with and without country codes side by side?

**colddeck84** · 11-18-2017, 07:58 PM

There is alot of stuff going on with your code and my entire workbook depends on it actually. I hope you still are with me

**colddeck84** · 11-18-2017, 08:01 PM

Originally Posted by jindon

So do you wand the result with and without country codes side by side?

Yes it need to be organized exactly like they are in the sample or else it will not work together with the rest of my workbook. I know my code splittleague from post #7 handles this. But I know its probably quite primitive compared to what you can make. And as you can see also from post #7 Ive made quite alot of workarounds to get your code working....

**colddeck84** · 11-18-2017, 08:04 PM

Also please notice that it is only the country headers that are duplicated, Not the league headers in the lists sheets

**jindon** · 11-18-2017, 09:24 PM

See if this is how you wanted.

Please Login or Register  to view this content.

**colddeck84** · 11-19-2017, 05:32 AM

Good morning Jindon. it became were late here yesterday so I had to get some sleep . I just woke up and wow this looks great . I thought everthing was working as expected but I just discovered a problem. The sorting skips the leagues or im not sure whats happening but it looks like teams are sorted under the countries:

here is Argentina: Almirante Brown(AR) and a few more team gets sorted under. But should have been:

Primera B Nacional (ar)
Superliga (ar)
Prim B Metro (ar)

In one Argentina Col, Next argentina Col (to the right) Same names except without Country Prefix)

It supposed to Be Leagues that are sorted under Country. Then The Teams should be sorted under League. The code is sorting the headers correct. Also with the splitting or doubling of Country Headers. But something is messed up with the teams. After your code it should Look quite similar to the test sheet except only half of the teams should be sorted (since we are splitting into 2 sheets)

Also Please note that its only one of the header col that are supposed to have country code Not any of the team names

Hope you have a chance to look at this
frederik

**jindon** · 11-19-2017, 05:35 AM

Just delete 2 lines with .Sort

Please Login or Register  to view this content.

**colddeck84** · 11-19-2017, 05:42 AM

Hi I didn't expect you to be awake must be quite early or late in japan, anyway That didn't quite do the trick not sure what the difference is, It still looks like teams are sorted under the country headers when it should have been Leagues. It actually looks like no League is sorted what so ever..

**jindon** · 11-19-2017, 05:47 AM

Should work.

**colddeck84** · 11-19-2017, 05:57 AM

thanks for your quick responses but no. something is wrong with the core code I think

Lets Look at Algeria, After your code it looks like this:

Algeria	Algeria
Es Setif(DZ)	Es Setif
Cr Belouizdad(DZ)	Cr Belouizdad
Us Biskra(DZ)	Us Biskra

But these items that are sorted are actually teams. It should look like this:

Algeria	Algeria
Ligue 1 (dz)	Ligue 1

Then these teams should be sorted under League 1(dz) like this for sheet Home

Ligue 1 (dz)
Es Setif
Cr Belouizdad
Us Biskra

And for away:

Ligue 1 (dz)
Paradou Ac
Usm Blida
Hussein Dey

**jindon** · 11-19-2017, 06:08 AM

OK, misunderstood.

How do you determine Home or Away?

**colddeck84** · 11-19-2017, 06:29 AM

Home is Teams in Col H
and Away is in Col J

I think you had it correct, They where just sorted into wrong Header col in the lists sheets..

**jindon** · 11-19-2017, 06:55 AM

In that case, Home and Away are exactly the same?

I just don't understand it.

You have Argeria in ListAway, but not in ListHome...

**colddeck84** · 11-19-2017, 07:27 AM

Algeria should Be sorted into both lists like above. Both list should have all Countries and leagues

Only the last item (teams) should be different from list home and away Like this:

Lets say The Only Country/League/team we are gona sort is for Algeria , Then The final result would Look like this

sheets("ListsHome")

#	A
1	Country
2	Algeria

#	B	C
1	Algeria	Algeria
2	Ligue 1 (dz)	Ligue 1

#	D
1	Ligue 1 (dz)
2	Es Setif
3	Cr Belouizdad
4	Us Biskra

sheets("ListsAway")

#	A
1	Country
2	Algeria

#	B	C
1	Algeria	Algeria
2	Ligue 1 (dz)	Ligue 1

#	D
1	Ligue 1 (dz)
2	Paradou Ac
3	Usm Blida
4	Hussein Dey

As you might see only the last Col for League will be different from sheethome to sheetaway.

in post #9 I uploaded a sample on how it should look like, But maybe it was a little confusing ?

**jindon** · 11-19-2017, 07:32 AM

So you missed "Argeria" in you workbook...

Please Login or Register  to view this content.

**colddeck84** · 11-19-2017, 07:37 AM

I did lol , Worst example I could have been using then., but the logic would be same for all Countries ...

**colddeck84** · 11-19-2017, 07:43 AM

You nailed it on your latest edit :=) This is just great. Your skills with this is unreal.
Great job sir I don't know how to thank you enough. Ill test it trough the day and let you know if I run into any problems

**jindon** · 11-19-2017, 08:03 AM

You are welcome.

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

**colddeck84** · 11-19-2017, 08:34 AM

Id give you more rep if I could.

Huge big thanks to you Jindon

**colddeck84** · 11-23-2017, 02:29 PM

@ Jindon. I'm not sure if you will see this or not, if not I guess I will have to make something subpar, but I was hoping you know how to fix. Ive tested this code a few days and I didn't notice before now. It looks like teams are not sorted in alphabetical order. Do you have a ninja trick to get them sorted?

frederik

**jindon** · 11-23-2017, 11:13 PM

If only team, add one line in "HomeAway" sub routine.

Please Login or Register  to view this content.

If you also want to sort league in country then add one line in "GetCountry" sub routine

Please Login or Register  to view this content.

**colddeck84** · 11-24-2017, 12:10 PM

Hi thanks, you are the best jindon both of these edits are useful. Have a great weekend

frederik

**colddeck84** · 12-16-2017, 07:24 AM

Hi Jindon as you suggested Ill add new question in this thread . It took me a couple of days to add your new code into my spreadsheet but now its up and running.
But this kinda messes up this code. Changes are not that big but the "away" team has been moved from Col J to Col L, and a few more Col added , I guess its not much that need to be changed to this code to get it to work but after looking trough your code I get dizzy in need a break

Here is a screen of how it looks now :

Frederik

**jindon** · 12-16-2017, 07:42 AM

Picture doesn't help at all.

I need a workbook with data and the result that you want.

**colddeck84** · 12-16-2017, 07:51 AM

Hi thanks for your swift response. Sorry about the picture (i'm need to stop doing that)
Here is sample workbook without any code with just the correct setup..

**jindon** · 12-16-2017, 08:25 AM

Change to

Please Login or Register  to view this content.

And I assume you can adjust rest.

**colddeck84** · 12-16-2017, 01:03 PM

Hi was out shopping for xmas, I just did as you suggested , but no I wouldn't know what else to change after this . Im sorry but your coding skills is to complex for me to comprehend

Edit:

I think I got it

Please Login or Register  to view this content.

much appreciated Jindon, now I could even edit this code a little on my own at a later point if its needed

Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Re: Sort List of items from one sheet to another.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Dynamic Formula for Getting Unique Items from a list and sort them alphabetically

[SOLVED] Have a list of items, 285, 285-A, 36-B, 23-D, 36 and need to sort

[SOLVED] RE: Have a list of items, 285, 285-A, 36-B, 23-D, 36 and need to sort

Bookmarks

Bookmarks

Posting Permissions