跳到主要內容

如何快速對行進行排序以匹配Excel中的另一列？

doc排序匹配到另一列1
如果工作表中有兩個列表，並且兩個列表的值均相同但順序不同，那麼如何對一個列表進行排序以匹配另一個列表，如下面的屏幕截圖所示？ 在本文中，我將介紹一個公式來幫助您在Excel中快速解決它。
對行進行排序以匹配另一列

箭頭藍色右氣泡 對行進行排序以匹配另一列

要對行進行排序以匹配另一列，以下公式可以為您提供幫助。

1.在您要求助的列（例如B1）旁邊選擇一個空白單元格，然後輸入此公式 = MATCH（A1，C：C，FALSE），然後向下拖動自動填充手柄以應用此公式。 看截圖：
doc排序匹配到另一列2

尖端：C：C是您要基於的列，而A1是您要排序的列的第一個單元格。

2.然後在公式單元格中顯示數字列表。 選擇要使用的列表和數字列表，然後單擊 數據 > 分類。 看截圖：
doc排序匹配到另一列3

3.在 分類 對話框中，選擇包含公式的列 柱 下拉列表，然後選擇 價值觀 和 最小到最大 來自 排序 和 過往訂單 分別下拉列表。 看截圖：
doc排序匹配到另一列4

4。 點擊 OK。 並對A列進行排序以匹配C列。
doc排序匹配到另一列5

提示：如果不再需要，可以在排序後刪除公式列。

從Excel中的選擇快速隨機排序或選擇單元格/行/列
Kutools for Excel的隨機排序範圍 可以快速按單元格，列或行對數據進行隨機排序或選擇。 點選取得全功能 30 天免費試用！
doc隨機排序
doc隨機選擇
 
 Kutools for Excel：擁有300多個便捷的Excel加載項，可以在30天內免費試用。

最佳辦公生產力工具

🤖 Kutools 人工智慧助手：基於以下內容徹底改變數據分析： 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能，體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！

Comments (25)
Rated 4.25 out of 5 · 2 ratings
Rob
Rob
This comment was minimized by the moderator on the site
Gracias, me ahorraste horas de trabajo y aprendí algo nuevo.
Reply
0
0
Rated 5 out of 5
Manu k R
Manu k R
This comment was minimized by the moderator on the site
Sorting data in rows to match respective column headers in excel.

A B C D E
---------
D
A C
C E
B D E
E
What I want it to look like:

A B C D E
---------
D
A C
C E
B D E
E
Reply
0
0
Manu k R
Manu k R
This comment was minimized by the moderator on the site
What it looks like:

A B C D E
---------
D
A C
C E
B D E
E
What I want it to look like:

A B C D E
---------
D
A C
C E
B D E
E

can we do this in Excel?
Reply
0
0
Rated 3.5 out of 5
JOAT
JOAT
This comment was minimized by the moderator on the site
Annn the formatting for that comment broke.

It would re-order it to:
0230
...........0231
...........0234
0236 0236
...........0240
0241
............0245
0250 0250
............0251

Is what I meant by insert empty cells as needed in each column
Reply
0
0
JOAT
JOAT
This comment was minimized by the moderator on the site
A lot of people are asking this question (on a 4 year old post) but I haven't found an answer.

I have 4 unique columns of time stamps from 4 sources. Not all the time stamps match up. I might have 0230, 0236, 0241, 0250 in one column and 0231, 0234, 0236, 0240, 0245, 0250, 0251 in another column. Is there away to have excel match the columns and insert empty cells as needed?
So instead of:
0230 0231
0236 0234
0241 0236 etc

It would re-order it to:
0230
0231
0234
0236 0236
0240
0241
0245
0250 0250
0251

I have about 13,000 rows I need to do this to across 4 columns so adding by hand or inserting rows is not feasible.
Reply
0
0
Sunny
Sunny     JOAT
This comment was minimized by the moderator on the site
Hi, JOAT, you can use this formula:

=IFERROR(VLOOKUP(G1,$I$1:$I$8,1,FALSE)
See screenshot:
https://www.extendoffice.com/images/stories/comments/sun-comment/doc-lookup-formula-1.png
Reply
Report
0
0
John
John
This comment was minimized by the moderator on the site
Hi, Great article!
I am wondering. If in the example above, column A does not have the value G2, this means that the sorting operation will be out of sync; 1,3,4,5,6,7,8. (missing a match for G2 on row index 2). Assuming that we want the rest of the data to line up, how can we resolve this without manually changing each line?
Is it possible to displace column A and use column B as a row index instead?
Reply
0
0
vicky
vicky
This comment was minimized by the moderator on the site
1 52 63 14 2 HOW CAN above two column data sort same data in one row
Reply
0
0
FRANK
FRANK
This comment was minimized by the moderator on the site
It doesn't help if the info is not exactly the same in both columns eg. column 1 has C,D,B, A and column 2 has B,D,C,A,G,R,T,H. When sorting column A to match column B, they won't match up. Any solution for this?
Reply
0
0
Sunny
Sunny     FRANK
This comment was minimized by the moderator on the site
Sorry, the method only work while two columns have same numuber rows and same data but in different order.
Reply
Report
0
0
Sujith
Sujith     Sunny
This comment was minimized by the moderator on the site
Please share some way to sort if the info is not exactly the same in both columns eg. column 1 has C,D,B, A and column 2 has B,D,C,A,G,R,T,H.
Reply
0
0
Daniella
Daniella     Sujith
This comment was minimized by the moderator on the site
Hey Sujith,

Did you find a solution because I have this exact problem. Please help
Reply
0
0
Mark Henry H Atwood
Mark Henry H Atwood     Daniella
This comment was minimized by the moderator on the site
Daniella/Sujith,
How would the unique members of the resorted column be ordered? If they are numeric or alpha entries, then the ordering is simple; otherwise, it is less intuitive. May be easiest to isolate the 1-1 matching rows between the ref column and subject column, apply the ordering approach detailed above on that subset, then utilize a custom sort on the entire column as a final step. All of this is highly dependent on what type of data you have and ordering you expect to achieve.
-MH
Reply
0
0
Ananya
Ananya
This comment was minimized by the moderator on the site
Thanks a lot for this post.. made my work a lot easier :)
Reply
0
0
Kishore
Kishore
This comment was minimized by the moderator on the site
Anyone can help how to sort like below:

Column A is having and column B is having

1 1

1 2

2 1

2 2

But I need both column as


1

1

2

2
Reply
0
0
Sunny
Sunny     Kishore
This comment was minimized by the moderator on the site
I do not understand your problem, can you give me more details?
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations