Trying to match sort Sheet2 verse sheet1

Z

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,129
Office Version
  1. 365
Platform
  1. Windows
Hi i need to sort sheet2 rows by Id number in column D from Sheet1 order. So whatever order sheet1 is in by id number I need sheet2 to look the same. The reason why I cant sort sheet1 id numbers by numbers then sheet2 is because. The order that Sheet1 is in I need it in the same order on sheet2 whole row.

Sheet1

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
1​
[/td][td]ORG UNIT[/td][td]LAST NAME[/td][td]FIRST NAME[/td][td]ID[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
2​
[/td][td][/td][td][/td][td][/td][td]39[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
3​
[/td][td][/td][td][/td][td][/td][td]149[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
4​
[/td][td][/td][td][/td][td][/td][td]26[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
5​
[/td][td][/td][td][/td][td][/td][td]10[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
6​
[/td][td][/td][td][/td][td][/td][td]30[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
7​
[/td][td][/td][td][/td][td]Frank[/td][td]29[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
8​
[/td][td][/td][td][/td][td]frank[/td][td]29[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
9​
[/td][td][/td][td][/td][td]kenny[/td][td]62[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
10​
[/td][td][/td][td][/td][td][/td][td]6[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
11​
[/td][td][/td][td][/td][td][/td][td]6[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
12​
[/td][td][/td][td][/td][td]john[/td][td]7[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
13​
[/td][td][/td][td][/td][td]john[/td][td]7[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
14​
[/td][td][/td][td][/td][td][/td][td]11[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
15​
[/td][td][/td][td][/td][td][/td][td]11[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
16​
[/td][td][/td][td][/td][td][/td][td]1[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
17​
[/td][td][/td][td][/td][td][/td][td]1[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
18​
[/td][td][/td][td][/td][td][/td][td]14[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
19​
[/td][td][/td][td][/td][td][/td][td]14[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
20​
[/td][td][/td][td][/td][td][/td][td]4[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
21​
[/td][td][/td][td][/td][td][/td][td]4[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
22​
[/td][td][/td][td][/td][td][/td][td]3[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
23​
[/td][td][/td][td][/td][td][/td][td]3[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]


Now need sheet2 to look the same. Which you can see its not. If the numbers are the same its fine as long as it sorts the whole row by number to be exact from sheet1. Any help would be great.

Sheet2

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
1​
[/td][td]ORG UNIT[/td][td]LAST NAME[/td][td]FIRST NAME[/td][td]ID[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
2​
[/td][td][/td][td][/td][td]frank[/td][td]
29​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
3​
[/td][td][/td][td][/td][td]frank[/td][td]
29​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
4​
[/td][td][/td][td][/td][td]kenny[/td][td]
62​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
5​
[/td][td][/td][td][/td][td]john[/td][td]
7​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
6​
[/td][td][/td][td][/td][td]john[/td][td]
7​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
7​
[/td][td][/td][td][/td][td][/td][td]
6​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
8​
[/td][td][/td][td][/td][td][/td][td]
6​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
9​
[/td][td][/td][td][/td][td][/td][td]
11​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
10​
[/td][td][/td][td][/td][td][/td][td]
11​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
11​
[/td][td][/td][td][/td][td][/td][td]
14​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
12​
[/td][td][/td][td][/td][td][/td][td]
14​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
13​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
14​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
15​
[/td][td][/td][td][/td][td][/td][td]
3​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
16​
[/td][td][/td][td][/td][td][/td][td]
3​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
17​
[/td][td][/td][td][/td][td][/td][td]
4​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
18​
[/td][td][/td][td][/td][td][/td][td]
4​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
19​
[/td][td][/td][td][/td][td][/td][td]
30​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
20​
[/td][td][/td][td][/td][td][/td][td]
10​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
21​
[/td][td][/td][td][/td][td][/td][td]
39​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
22​
[/td][td][/td][td][/td][td][/td][td]
149​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
23​
[/td][td][/td][td][/td][td][/td][td]
26​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]
 
Last edited:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
You could put this in col E of sheet 2
=MATCH(D2,sheet1!D$2:D$23,0)

and then sort on that
 
Upvote 0
Fluff said:
You could put this in col E of sheet 2
=MATCH(D2,sheet1!D$2:D$23,0)

and then sort on that
Click to expand...

Thanks for the reply. Left my computer at the moment will try this later.
 
Upvote 0
Hi so this didn't work it was pulling from a different line and not returning the right numbers. I am trying to sort the whole row on sheet2 to match sheet1 the number order that sheet1 has in Column D. The match formula pulls in some random numbers.
 
Upvote 0
Using your data I get


Excel 2013/2016
ABCDE
1ORG UNITLAST NAMEFIRST NAMEID
2frank296
3frank296
4kenny628
5john711
6john711
769
869
91113
101113
111417
121417
13115
14115
15321
16321
17419
18419
19305
20104
21391
221492
23263
Sheet2
Cell Formulas
RangeFormula
E2=MATCH(D2,Sheet1!D$2:D$23,0)


Which when sorted becomes


Excel 2013/2016
ABCDE
1ORG UNITLAST NAMEFIRST NAMEID
2391
31492
4263
5104
6305
7frank296
8frank296
9kenny628
1069
1169
12john711
13john711
141113
151113
16115
17115
181417
191417
20419
21419
22321
23321
Sheet2
 
Upvote 0
ok i got it. I wasnt understanding how it was working. Thats my fault now i understand and get it. I build this into a small macro, but i have to fix something now so it can work off of this. May start a new post or come back thanks alot
 
Upvote 0
Glad it's working & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC
Z
  • Question Question
Formula ? on match
Replies
6
Views
474
zone709
Z
P
  • Question Question
Transfer data from Sheet2 to Sheet1
Replies
9
Views
605
Panoos64
P
S
  • Question Question
Looping Through and Sorting Multiple Rows in a Range
Replies
1
Views
515
stephenm93
S
R
  • Question Question
Sort all worksheets in ascending order (except sheet1 and sheet2)
Replies
3
Views
738
Ruddles
Ruddles

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

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