数据表要​​分组，转置匹配值同一行

本文介绍了数据表要​​分组，转置匹配值同一行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个 Excel 数据表.A 列包含姓名，B 列包含他们的兴趣.每个兴趣都有一个单独的行.我想从这个表中获取数据，并有一行包含客户姓名和一列代表他们的每个兴趣.IE 原始数据:

I have a table of data in Excel. Column A contains Names, Column B contains their interest. Each interest has a separate row. I want to take the data from this table and have a single row with the name of the customer and a column for each of their interests. IE RAW Data:

我希望采用 4000 行表并按名称分组.我不确定每个名字在列表中出现多少次(一次或五十次)，但我希望将兴趣放在一行中，每个兴趣在一个单独的列中 EG Desired Data:

I am looking to take the 4000 row table and grouping by the name. I am unsure how many times each name appears in the list (Once or Fifty times) but I want the interests placed on a single row with each interest in a separate column EG Desired Data:

我已经尝试了标准的转置....html 表....和数据透视表，但无论客户是否感兴趣并使用记录计数 T/F 这意味着数据表更难理解，如果我把它作为一个块并按名称排序

I have tried the standard transpose....html table....and pivot tables but it will put the interests all in a row along the top regardless if the customer is interested or not and using a record count T/F that means the data sheet in harder to understand then if I leave it as one block and sort by name

当然，我并不孤单，但过去 2 小时内的所有搜索都会返回数据透视/转置或重复项.任何表示赞赏

Sure I am not alone with this but all searches for the past 2 hrs keep returning pivot/transpose or duplicate items. Any is appreciated

推荐答案

如果您不想使用 VBA，您可以先添加一列，例如在 C 列中，标题为InterestNum".

If you don't want to use VBA, you could first add a column, for instance in column C, with the title "InterestNum."

在 C2 中，只需输入 1.

In C2, just put 1.

在 C3 中，输入 =COUNTIF($A$2:$A2, $A3) + 1.这将找到该人感兴趣的数字.

In C3, put =COUNTIF($A$2:$A2, $A3) + 1. This will find the number interest it is for the person.

创建一个查找列，例如在 D 列中.在 D2 中，输入 =A2&C2

Make a lookup column, for instance in column D. In D2, put =A2&C2

然后，列出所有人.我假设您将此列表从新工作表的单元格 A2 开始.然后将标题从 B1 开始，以便 B1 包含标题1"，C1 包含标题2"，代表兴趣编号和任意数量的列.

Then, make a list of all the people. I assume that you put this list starting in cell A2 of a new sheet. Then put headers starting in B1 so that B1 contains the title "1" and C1 contains the title "2" standing for the interest number and as many columns as you wish.

然后在单元格 B2 中，输入公式 =IF(ISNA(MATCH($A2&B$1,data!$D$2:$D$5,0)),"",INDEX(data!$B$2:$B$5,MATCH($A2&B$1,data!$D$2:$D$5,0)))

Then in Cell B2, put the formula =IF(ISNA(MATCH($A2&B$1,data!$D$2:$D$5,0)),"",INDEX(data!$B$2:$B$5,MATCH($A2&B$1,data!$D$2:$D$5,0)))

这假设您的原始数据位于 data 选项卡中.我只测试了 4 行，因此您需要将 $D$2:$D$5 更改为与您一样多的行.这是通过查找姓名和兴趣编号的组合来实现的.它首先检查数据中是否存在该组合.如果不是，则该兴趣为空白.如果是这样，它通过转到查找的同一行来找到实际兴趣.

This assumes that your original data is in the data tab. I only tested with 4 rows, so you would need to change $D$2:$D$5 to have as many rows as you do. This works by looking up a combination of the name and interest number. It first checks to see if that combination exists in the data. If not, it leaves that interest blank. If so, it finds the actual interest by going to the same row of the lookup.