跳到主要内容

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

doc排序匹配到另一列1
如果工作表中有两个列表，并且两个列表的值均相同，但顺序不同，那么如何对一个列表进行排序以匹配另一个列表，如下面的屏幕截图所示？ 在本文中，我将介绍一个公式来帮助您在Excel中快速解决它。
对行进行排序以匹配另一列

箭头蓝色右气泡 对行进行排序以匹配另一列

要对行进行排序以匹配另一列，以下公式可以为您提供帮助。

1.在您要求助的列（例如B1）旁边选择一个空白单元格，然后输入此公式 = MATCH（A1，C：C，FALSE），然后向下拖动自动填充手柄以应用此公式。 看截图：
doc排序匹配到另一列2

Tips:：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 Tab 为 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