Sorting with vba loop?

Hi I have a lot of data stored in column B:G coloumn B I have sorted and copied (+removed duplicates) over to column N

Im trying to sort this in a special manner as im gonna use this data in dynamic list

So now I want to search for keyword N3 and get a match for all corresponding data in column C (also without no duplicates)

here is my attempt

Please Login or Register  to view this content.

but this code copies to much as this gives over 50 of the same result

any help would be much appreciated :=)

Here is my updated formula:

Please Login or Register  to view this content.

This did indeed give me the result I desired, without removing duplicates .

But now comes the real problem I need a this to loop trough all the data I have stored (30 countries or so)

Hi, Thank you for your reply. Here is an sample, the codes are in the modules

I did not include the codes for the the main table, but this table is dynamic and will change pretty often.

http://www.filedropper.com/samplev1

ok will do but , sorry didn't see the upload link before

Nice good job, but its not exactly what I was going for.

I want each league to be sorted under corresponding league, So that "super League" goes under Albania, Then Liga 1 Under Algeria, and so on

Its actually a 2(3) problem.
what you did should actually be sorted horizontally to the right from last column in row 3

And Thank you for your time to help me with this .

I updated your code to this,does it look correct?

Please Login or Register  to view this content.

This will solve my 2nd problem

Now I just need Leagues sorted under the different Countries

And Home (LP) sorted under the different leagues.

Hi sorry for confusing description I added an sample_v_3

Hope this gives an better idea how i want it?

You see the o plan is to make list(s) of this data. All of these countries, leagues and teams changes everyday

something as shown in this sample file.

Ok thank you.
really appreciate it.

Works Perfect ,
I added just something below your code yesterday to remove the format , but liked your updated code much better

Would it be possible with a fellow up question ? as I see you formula was way to complicate for me to duplicate. I was hoping to get an working code for the league and teams also..
this will be a little different requirements as teams from column "E" and "G" needs to be sorted under correct header

I have attached a wb on how it should look. I hope i don't push the line with asking for more help..

Hi nice you made it real quick , however I think i made some mistake again but I'm not able to get it to work, I adjusted the code to this:

Please Login or Register  to view this content.

I made this adjustment in red , but no luck :/

also regarding the var I , its set as start num 20 but shouldn't this be a a dynamic num like lastrow or column ?

it does what i require ., see my edited post above.

but it does not give any results with updated main table.

I understand if you don´t want to , you have already been more then great

I guess I´ll try to add this myself, its just a little hard to figure where to add it into your ninja code

This part is missing in your final ()

Please Login or Register  to view this content.

This code will add all the leagues , just need to integrate it into your master code

I know im bad at explaining things :/
, IM not sure im confortable sharing my main sheet as this contains some sensitive data besides this work.

Ill try my best;

".What does your sheet you are working with look like"

Looks exactly as the sample file only it contains an button to update the main table

".What columns are populated prior to code running"

- Main table is populated by btn get result (CoulomB:G)
- Column N iis populated by another btn: Countries
this data is also sorted horizontally from column 0 to the right
- Latest code I posted here will populate leagues (what was missing, but i had entered manualy in test sheet"

Basically This code (also based on your work) , needs to merge with your final (): and that is it.

Remove column T-X to test in sample_v_6 to test

Originally Posted by sintek

I've tested on all your sample uploads and it seems to work...

OK Nice, I did just check real quick this morning will do further testing later after job.

Hi I just came home tested in main sheet everthing works as expected , This is so great , you would not belive how much work this code saves me for (earlier I entered every all the information from this code manuay) tgis is pure GOLD

I don't know how to thank you enough , you are just the best

(as a side note , reson it did not work foir me was that i didn't copy both of your modules just the final() )


Anyway thanks

Ouch im sorry to report that the code makes (some) duplicates, not with the teams but with the leagues
and also its seams almost random what is duplicated, around 10-20% or so ??? really crazy

Here is results it generated from russia.

Professional Football League Ural-povolzhye
Professional Football League West
Professional Football League South
Professional Football League Ural-povolzhye
Professional Football League West
Professional Football League South


Maybe there is an simple code I can add at the end , where it searches for duplicates and as maybe sort it A to Z

colddeck84

See if the result is how you wanted or not.

Please Login or Register  to view this content.

Hi I just came home from work, maybe ill stay away from the quick fixas you suggested.

"have you checked your spelling..Perhaps an error causing duplicates..."
-I can confirm after testing some that there is something going on with horizontal league sorting as it generates a couple duplicates , im really sorry to say this
I added country name to the lewague name to see as there weere some leagues with same name, but still same results with unique league name.

Also hi jindon, ty for your help. I have not near the same expertise as your guys and the code is already to complicated for me.
Wheere should I insert your line of code ?

I wrote the code based on your workbook in post #5.

Its no joke that you are a genius I can tell Your code was lightning fast, load time went down from 2-3 sec to about 0,00000001
But for this part of the code there was working fine , maybe just a little slow, It is only the second part of the code there is some problem with
: The one that adds the teams under the leagues

I really liked your work on this , would it be possible that you could update this to also load the Leagues after country and teams under (also it would be great that it just make one new sheet and never again)

As a side question. After I started working with this code I get this error when loading my sheet_

"we found a problem with some content in "file name" Do you want us to try to recover as much as we can? "

After clicking yes I get an error in sheet4.bin (which I dont have even"
Earlier it was in sheet 5 ., But I deleted this sheet and everything was fine four a very short while,
Maybe you guys have experienced something like this before ?

If you upload a workbook with before/after clearly showing the logic behind it, it would help me to understand your problem.

It should not a big data but should be Exact result, do not just enter something like "etc".

So that I can take a look at it tomorrow.

Originally Posted by jindon

If you upload a workbook with before/after clearly showing the logic behind it, it would help me to understand your problem.

It should not a big data but should be Exact result, do not just enter something like "etc".

So that I can take a look at it tomorrow.

I appreciate that alot , Ill try to upload the entire code so you can generate the list as well :=)

Hi did you get any chance to look at this ?

or maybe the sample file was not good enough ?

Is the data in N2:FT40 in "test" sheet your desired result?

If so, take Argentina

Your result shows
(AR) Primera Division
(AR) Primera B Nacional
(AR) Primera Division
(AR) Primera B Nacional
(AR) Primera Division
(AR) Primera B Nacional
(AR) Primera Division


Where as my result
(AR) Primera Division
(AR) Primera B Nacional

Do you want not just a unique , but all of them?
If so, change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

" the data in N2:FT40 in "test" sheet your desired result?"

If so, take Argentina - Your results is what im interested in , but you code stops after sorting leagues under countries-

Problem Comes with step 2 of the code which Sintek helped me compile.

In step 2 of the code it should add all the leagues starting from last column , in test sheet this is BA and add them to the right so in the test sheet this is BA:FS
and also load the teams under the leagues (quite similar to step one) , only difference is that data for the teams is fetched from 2 columns : E ang G. Sinteks code does this , but for some reason his code creates a couple of duplicated results of the leagues.

I hope I not explained it to confusing as explaining is not my strongest suit

Number of columns are differ from yours, so check what I missed.

Please Login or Register  to view this content.

I'll run the code, should I first run your first code or just the latest?

Just latest.

P.S the result will appear from N2 in new sheet.

Boom loaded everything in less then a second. You have crazy skills with this , it even didn't take you long for you to compile this and with that vague description as well
Very good job Sir

Could I please ask for two slight modifications ?

1. That it loadlike this A1="#" A2:A & Last row = Countries , B1: Last Column & row 1 = First countries then Leagues
And all the Data under , Almost exactly as you made your first code , except this code also have the league and teams sorted.

2. That all of this data is always generated to same sheet "LeagueDataOrSomething" , Then it will be super easy for me to find all of this information to load into my lists

Upload a workbook showing exact result that you want.

I uploaded a sample here , I included your code in the button sort , And I renamed the sheet where all want all the data loaded to "leaguedata" ,
would be great that your code clears this sheet then loads the data , always in this sheet and never a new sheet.

Change to

Please Login or Register  to view this content.

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

Done , thanks alot for your help with this , your ninja code really helped

Also big thanks to sintek for his time and effort

Hi I ran into one small problem with your sort code, you see that coloumn F in the main sheet "score" in all samples has returned just this "-", buyt if I change some of my settings this coloumn will desplay the ft result like "2-0" "3-1" , somehow this gives this line problems in your code:

Please Login or Register  to view this content.

is there an simple fix for this?

No idea, because my code is nothing to do with Col.F in "test" sheet.

Hi thanks for your swift reply . That's also what I thought was weird , but as soon as I fill column F with results I get this error msg:

Run-time error 9
Subscript out of range

Upload a workbook.

Guess game is no fun.

I have uploaded 2 workbooks:

- The content for the one with error is matches from today , (both finished and not finished matches) - this results to some error in your code.

- The 2nd version are finished matches from yesterday - this file gives no errors , I don't know why

Both no error and the code is not the latest one.

When you change the putput sheet, you must prepare the sheet named "Lists" in this case.

Please Login or Register  to view this content.

It was just for the sample I forget to change this, look at this sample :

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

BTW, do you need a vertical list of country? meaningless to me.

Hi thanks for your help, I guess it looks meaningless for this code, - but your code is used for something else where country is sorted in a list (master) then the other info is dependent upon this

I added a screenshot , all of this is possible thanks to your help

Originally Posted by colddeck84

but your code is used for something else where country is sorted in a list (master) then the other info is dependent upon this

Absolutely NO!.
When I first looked at this thread in you first post, I didn't understand the necessity of this vertical country list and I thought it would something to do with the matrix work.
So, I didn't answer at the first time.

im not sure what you said no to ? hope your not mad ?

- I'm really grateful for your provided help - I'm super happy with the end result