For 2 Columns how to Wraprows and then sort it horizontally accordingly with the rank List?

L

Lacan

Board Regular
Joined
Oct 5, 2016
Messages
198
Office Version
  1. 365
Platform
  1. Windows
Hi Guys,

In this case would like for 2 Columns to Wraprows and then sort it horizontally accordingly with the rank List?
It is possible accomplish it in one unique formula?
Have also the example of the desired results.
Thank you very much for all the help. 🙌👍💪💪
Fórmula Principal para o Ficheiro VDF.xlsm
ABCDEFGHIJ
1
2hmR Region Values Market MAT May-21MAT Rank MKT
3363 St - Setúbal (SSebastião, GPAGuerra, Sado)52930121
4351 St - Seixal (Amora)43081862DESIRED RESULTS
5309 Lx - Lisboa (Benfica - Norte)41430943
6301 Lx - Amadora (Venteira)3931194443251
7362 St - Setúbal (SJulião, SMGraça)37900345301 Lx - Amadora (Venteira)309 Lx - Lisboa (Benfica - Norte)351 St - Seixal (Amora)362 St - Setúbal (SJulião, SMGraça)363 St - Setúbal (SSebastião, GPAGuerra, Sado)
8352 St - Seixal (Corroios)36035076910678
9359 St - Almada (ChCaparica, Sobreda)35842137302 Lx - Amadora (Alfragide, ÁLivres)333 Lx - Lisboa (Carnide)352 St - Seixal (Corroios)359 St - Almada (ChCaparica, Sobreda)361 St - Setúbal (NSAnunciada, SLourenço, SSimão)
10361 St - Setúbal (NSAnunciada, SLourenço, SSimão)347225481312111415
11302 Lx - Amadora (Alfragide, ÁLivres)34193179304 Lx - Amadora (ESol)331 Lx - Lisboa (SDBenfica - Sul)349 St - Seixal (Seixal, APPires, FFerro)350 St - Seixal (Arrentela)353 St - Almada (Laranjeiro)
12333 Lx - Lisboa (Carnide)3308897101617192018
13349 St - Seixal (Seixal, APPires, FFerro)292700211311 Lx - Lisboa (COurique - Oeste)316 Lx - Lisboa (SMMaior)354 St - Almada (CPiedade)356 St - Almada (Feijó)357 St - Almada (Caparica, Trafaria)
14331 Lx - Lisboa (SDBenfica - Sul)2876769122523242122
15304 Lx - Amadora (ESol)286935813300 Lx - Amadora (MÁgua - Norte)308 Lx - Lisboa (Benfica - Sul)310 Lx - Lisboa (Estrela)332 Lx - Lisboa (SDBenfica - Norte)358 St - Almada (CCaparica)
16350 St - Seixal (Arrentela)2783383142627292830
17353 St - Almada (Laranjeiro)273836715299 Lx - Amadora (MÁgua - Sul)303 Lx - Amadora (Falagueira, VNova)313 Lx - Lisboa (Misericórdia)314 Lx - Lisboa (SAntónio - Oeste)320 Lx - Lisboa (SVicente)
18311 Lx - Lisboa (COurique - Oeste)266940216323133
19316 Lx - Lisboa (SMMaior)260330817312 Lx - Lisboa (COurique - Este)315 Lx - Lisboa (SAntónio - Este)321 Lx - Lisboa (Campolide)
20357 St - Almada (Caparica, Trafaria)201403918
21354 St - Almada (CPiedade)200543219
22356 St - Almada (Feijó)186717620
23332 Lx - Lisboa (SDBenfica - Norte)184919321
24358 St - Almada (CCaparica)161565722
25308 Lx - Lisboa (Benfica - Sul)151892323
26310 Lx - Lisboa (Estrela)148704524
27300 Lx - Amadora (MÁgua - Norte)146220825
28299 Lx - Amadora (MÁgua - Sul)143420626
29303 Lx - Amadora (Falagueira, VNova)138264527
30314 Lx - Lisboa (SAntónio - Oeste)138227728
31313 Lx - Lisboa (Misericórdia)122086529
32320 Lx - Lisboa (SVicente)109694130
33315 Lx - Lisboa (SAntónio - Este)88770531
34312 Lx - Lisboa (COurique - Este)84728232
35321 Lx - Lisboa (Campolide)82613333
36
21-07-2024
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Please test this (probably a more complicated than it should be but hopefully somebody will post something simpler):

Excel Formula: 
=LET(
values,B3:B35,
rank,D3:D35,
count,IF(INT(ROWS(rank)/5)=ROWS(rank)/5,ROWS(rank)/5,INT(ROWS(rank)/5)+1),
seq,SEQUENCE(count,,1,5),
array,HSTACK(values,rank),
IFERROR(DROP(REDUCE("",seq,LAMBDA(a,b,VSTACK(a,TRANSPOSE(SORT(HSTACK(INDEX(array,SEQUENCE(5,,b,1),2),INDEX(array,SEQUENCE(5,,b,1),1)),2))))),1),""))
 
Upvote 0
Slightly shorter.
Excel Formula: 
=LET(d,B3:B35,s,D3:D35,
r,ROUNDUP(SEQUENCE(MAX(s))/5,0),
IFNA(DROP(REDUCE("",SEQUENCE(MAX(r)),LAMBDA(a,b,VSTACK(a,TRANSPOSE(SORT(FILTER(HSTACK(s,d),r=b),2))))),1),""))
 
Upvote 0
Solution
hagia_sofia said:
Please test this (probably a more complicated than it should be but hopefully somebody will post something simpler):

Excel Formula: 
=LET(
values,B3:B35,
rank,D3:D35,
count,IF(INT(ROWS(rank)/5)=ROWS(rank)/5,ROWS(rank)/5,INT(ROWS(rank)/5)+1),
seq,SEQUENCE(count,,1,5),
array,HSTACK(values,rank),
IFERROR(DROP(REDUCE("",seq,LAMBDA(a,b,VSTACK(a,TRANSPOSE(SORT(HSTACK(INDEX(array,SEQUENCE(5,,b,1),2),INDEX(array,SEQUENCE(5,,b,1),1)),2))))),1),""))
Click to expand...
Dear @hagia_sofia

Thank you very much for all the help!!! ✨👍👍
 
Upvote 0
Cubist said:
Slightly shorter.
Excel Formula: 
=LET(d,B3:B35,s,D3:D35,
r,ROUNDUP(SEQUENCE(MAX(s))/5,0),
IFNA(DROP(REDUCE("",SEQUENCE(MAX(r)),LAMBDA(a,b,VSTACK(a,TRANSPOSE(SORT(FILTER(HSTACK(s,d),r=b),2))))),1),""))
Click to expand...
@Cubist

Thank you very much!!!
Works Perfect!!!💪💪👍
 
Upvote 0
Lacan said:
@Cubist

Thank you very much!!!
Works Perfect!!!💪💪👍
Click to expand...

Dear @Cubist

After test it just realize it is much better with this visual.
Can you please upgrade your Formula?
Thank you so much. 👍👍🍻
Big hug.

Fórmula Principal para o Ficheiro VDF.xlsm
ABCDEFGHIJKLMNOP
1
2hmR Region Values Market MAT May-21MAT Rank MKT
3363 St - Setúbal (SSebastião, GPAGuerra, Sado)52930121
4351 St - Seixal (Amora)43081862DESIRED RESULTS
5309 Lx - Lisboa (Benfica - Norte)41430943
6301 Lx - Amadora (Venteira)39311944
7362 St - Setúbal (SJulião, SMGraça)379003454301 Lx - Amadora (Venteira)3309 Lx - Lisboa (Benfica - Norte)2351 St - Seixal (Amora)5362 St - Setúbal (SJulião, SMGraça)1363 St - Setúbal (SSebastião, GPAGuerra, Sado)
8352 St - Seixal (Corroios)360350769302 Lx - Amadora (Alfragide, ÁLivres)10333 Lx - Lisboa (Carnide)6352 St - Seixal (Corroios)7359 St - Almada (ChCaparica, Sobreda)8361 St - Setúbal (NSAnunciada, SLourenço, SSimão)
9359 St - Almada (ChCaparica, Sobreda)3584213713304 Lx - Amadora (ESol)12331 Lx - Lisboa (SDBenfica - Sul)11349 St - Seixal (Seixal, APPires, FFerro)14350 St - Seixal (Arrentela)15353 St - Almada (Laranjeiro)
10361 St - Setúbal (NSAnunciada, SLourenço, SSimão)3472254816311 Lx - Lisboa (COurique - Oeste)17316 Lx - Lisboa (SMMaior)19354 St - Almada (CPiedade)20356 St - Almada (Feijó)18357 St - Almada (Caparica, Trafaria)
11302 Lx - Amadora (Alfragide, ÁLivres)3419317925300 Lx - Amadora (MÁgua - Norte)23308 Lx - Lisboa (Benfica - Sul)24310 Lx - Lisboa (Estrela)21332 Lx - Lisboa (SDBenfica - Norte)22358 St - Almada (CCaparica)
12333 Lx - Lisboa (Carnide)33088971026299 Lx - Amadora (MÁgua - Sul)27303 Lx - Amadora (Falagueira, VNova)29313 Lx - Lisboa (Misericórdia)28314 Lx - Lisboa (SAntónio - Oeste)30320 Lx - Lisboa (SVicente)
13349 St - Seixal (Seixal, APPires, FFerro)29270021132312 Lx - Lisboa (COurique - Este)31315 Lx - Lisboa (SAntónio - Este)33321 Lx - Lisboa (Campolide)
14331 Lx - Lisboa (SDBenfica - Sul)287676912
15304 Lx - Amadora (ESol)286935813
16350 St - Seixal (Arrentela)278338314
17353 St - Almada (Laranjeiro)273836715
18311 Lx - Lisboa (COurique - Oeste)266940216
19316 Lx - Lisboa (SMMaior)260330817
20357 St - Almada (Caparica, Trafaria)201403918
21354 St - Almada (CPiedade)200543219
22356 St - Almada (Feijó)186717620
23332 Lx - Lisboa (SDBenfica - Norte)184919321
24358 St - Almada (CCaparica)161565722
25308 Lx - Lisboa (Benfica - Sul)151892323
26310 Lx - Lisboa (Estrela)148704524
27300 Lx - Amadora (MÁgua - Norte)146220825
28299 Lx - Amadora (MÁgua - Sul)143420626
29303 Lx - Amadora (Falagueira, VNova)138264527
30314 Lx - Lisboa (SAntónio - Oeste)138227728
31313 Lx - Lisboa (Misericórdia)122086529
32320 Lx - Lisboa (SVicente)109694130
33315 Lx - Lisboa (SAntónio - Este)88770531
34312 Lx - Lisboa (COurique - Este)84728232
35321 Lx - Lisboa (Campolide)82613333
36
37
21-07-2024 (2)
 
Upvote 0
Try:
Excel Formula: 
=WRAPROWS(TOCOL(SORTBY(HSTACK(D3:D35,B3:B35),ROUNDUP(SEQUENCE(MAX(D3:D35))/5,0),1,B3:B35,1)),10,"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Let formula?
2
Replies
11
Views
342
Eric W
Eric W
L
  • Solved
How to Filter data with criteria and sort it accordingly to another List rank position?
Replies
9
Views
262
Lacan
L
L
  • Question
Very simple and easy question: - How to use Indirect Function + Xlookup with 2 Criteria?
Replies
2
Views
422
Lacan
L

Forum statistics

Threads
1,221,448
Messages
6,159,922
Members
451,604
Latest member
SWahl

Share this page

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