Rearranging data

excel_beta_345User

New Member
Joined
Jun 17, 2024
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I have two tables as per attached images. Now I want to take data from table 2 and arrange that in table 3 in such order that it matches names as per Column A from table 1. If name matches the cell from Colum A of table 1 A then only it should list all the details in row in table 3. In case if it does not match then it should not be added at all, row should remain blank. It would be great if those non-matched name and their details from table 2 are added at the end of table 3 but if not, I can apply conditional formatting to find out those non matching between table 2 and 3 and cut paste them to move out at the end of table 3, hope that works.

Common factor between table 1 and 3 should be Column with Header "Name". Name in column J should be able to match the same order as of Column A. Can you please help to achieve this?

Sorry that I cannot use XL2BB add-in, unfortunately 0365 policies does not allow that hence had to share image again.
 

Attachments

  • 4.jpg
    4.jpg
    178.8 KB · Views: 28
How about
Excel Formula:
=SORTBY(F3:H8,XMATCH(F3:F8,A3:A12))
 
Upvote 0
I'm sure it can be done with less code than this but try:
Excel Formula:
=LET(orig,F3:H8,
srt,A3:A12,
srtCnt,ROWS(srt),
origCol1,INDEX(orig,0,1),
srtOrder,srtCnt-IFERROR(XMATCH(origCol1,srt,0),-1),
combined,HSTACK(orig,srtOrder),
srtCol,COLUMNS(combined),
DROP(SORTBY(combined,INDEX(combined,0,srtCol)),,-1))
 
Upvote 0
I'm sure it can be done with less code than this but try:
Excel Formula:
=LET(orig,F3:H8,
srt,A3:A12,
srtCnt,ROWS(srt),
origCol1,INDEX(orig,0,1),
srtOrder,srtCnt-IFERROR(XMATCH(origCol1,srt,0),-1),
combined,HSTACK(orig,srtOrder),
srtCol,COLUMNS(combined),
DROP(SORTBY(combined,INDEX(combined,0,srtCol)),,-1))
Thank you for the help but unfortunately it is still not fixing the order, after applying this order appears as below. It should be as per sequence from Table 1 Column A


George Best
Garrincha
Franz Beckenbauer
Alfredo Di Stéfano
Ferenc Puskás
Xavi Hernández
 
Upvote 0
OK minor adjustment try this:
Excel Formula:
=LET(orig,F3:H8,
srt,A3:A12,
srtCnt,ROWS(srt),
origCol1,INDEX(orig,0,1),
srtOrder,IFERROR(XMATCH(origCol1,srt,0),srtCnt+1),
combined,HSTACK(orig,srtOrder),
srtCol,COLUMNS(combined),
DROP(SORTBY(combined,INDEX(combined,0,srtCol)),,-1))
 
Upvote 0
How about:

Book1
ABCDEFGHIJKL
1
2NameJerseyTeamDateNameCarCountryNameCarCountry
3PeleAlfredo Di StefanoG3H3Franz BeckenbauerG7H7
4Diego MaradonaFerenc PuskasG4H4GarrinchaG6H6
5Lionel MessiXavi HernandezG5H5George BestG8H8
6Cristiano RonaldoGarrinchaG6H6Alfredo Di StefanoG3H3
7Johan CruyffFranz BeckenbauerG7H7Ferenc PuskasG4H4
8Szinedine ZidaneGeorge BestG8H8Xavi HernandezG5H5
9Michel Platini
10Franz Beckenbauer
11Garrincha
12George Best
13
Sheet5
Cell Formulas
RangeFormula
J3:L8J3=SORTBY(F3:H8,XLOOKUP(F3:F8,A3:A12,ROW(A3:A12),999,0))
Dynamic array formulas.
 
Upvote 0
I fail to see why Fluff's formula didn't work for you.
 
Upvote 0
I don't know why I thought I needed to provide an alternative to a straight and simpler SortBy approach 🤦‍♂️. All the above methods produce the same results and I can't reproduce the results in post #5.
Can you post (by copy pasting) the exact formula you are using (please use either Fluff's or Eric's).
If the results deviate from Post #5 show us the results you are getting.

Also an image showing the Row and Column References for both Table1 & Table2.
Ideally add a column to Table2 and do an XMatch to Table1 and check that anything that should be matching is matching.
 
Upvote 0
See if this is what you want.

25 01 23.xlsm
ABCDEFGHIJKL
2NameJerseyTeamDateNameCarCountryNameCarCountry
3PeleAlfredo Di StefanoG3H3 
4Diego MaradonaFerenc PuskasG4H4
5Lionel MessiXavi HernandezG5H5Lionel MessiG9H9
6Cristiano RonaldoGarrinchaG6H6
7Johan CruyffFranz BeckenbauerG7H7
8Szinedine ZidaneGeorge BestG8H8
9Michel PlatiniLionel MessiG9H9
10Franz BeckenbauerFranz BeckenbauerG7H7
11GarrinchaGarrinchaG6H6
12George BestGeorge BestG8H8
13Alfredo Di StefanoG3H3
14Ferenc PuskasG4H4
15Xavi HernandezG5H5
16
Rearrange
Cell Formulas
RangeFormula
J3:L15J3=VSTACK(MAKEARRAY(ROWS(A3:A12),3,LAMBDA(r,c,IFERROR(INDEX(F3:H9,MATCH(INDEX(A3:A12,r),F3:F9,0),c),""))),FILTER(F3:H9,ISNA(MATCH(F3:F9,A3:A12,0))))
Dynamic array formulas.
 
Upvote 0
Solution

Forum statistics

Threads
1,226,838
Messages
6,193,259
Members
453,786
Latest member
ALMALV

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