按组排列,不包括来自同一行的组成员 [英] Concatenate by group excluding group member from same row
问题描述
是否可以在图像中创建集群列?需要连接所有常见整体值的单位值,但不包括其自身作为连接的值。我可以请求一些帮助来创建集群列吗?
每个集群都具有与结果相同的单位除了单位的所有单位:
整体单元集群
总体1 UnitA1 {UnitA2,UnitA3}
总体1 UnitA2 {UnitA1,UnitA3}
总体1 UnitA3 {UnitA1,UnitA2}
总体2 UnitA4 {UnitA5}
总体2 UnitA5 {UnitA4}
总体3 UnitB62 {UnitB63,UnitB64,UnitB65}
总体3 UnitB63 {UnitB62, UnitB64,UnitB65}
总体3 UnitB64 {UnitB62,UnitB63,UnitB65}
总体3 UnitB65 {UnitB62,UnitB63,UnitB64}
$ b想想它可能是一个两步的过程,连接然后可能是搜索和替换?
可以用公式实现,而是一个rigmarole。假设按照指示排序,总体
在A1中:
-
在C1
集群
,C2(在Row1中假定的标签):= IF (A1 = A2,C1&,B2,{&B2)
-
将这两个公式复制到适合的位置。
-
全选,复制,粘贴特殊值,值。
-
全部选择,DATA>大纲 - 小计,每次更改:
总体
,使用函数计数,将小计添加到:(ColumnD)
,替换当前小计,数据汇总,确定。 -
过滤并为ColumnA,文本过滤器,包含...,
co </ code>确定。
-
在第一行中可以看到下面的标题(假定为Row5)在ColumnC中输入:
= C4
并复制适合。
-
取消筛选,全选,复制,粘贴特殊值。
-
选择ColumnA,HOME>编辑 - 查找&选择,替换...,查找内容:
计数
,全部替换。 (C
之前的空格Count
。) -
全部选择,DATA>大纲,小计,全部删除。删除
Grand
行。 -
过滤以选择所有,但
TRUE
和FALSE
在ColumnD的第一个空白单元格中插入1
,然后复制下来。排除过滤器。 -
按ColumnA值,A到Z,然后按列B值,A到Z排序。
-
筛选ColumnD以选择
TRUE
和FALSE
并删除所选的ColumnC的内容行(离开标签)。 -
取消筛选并选择ColumnC。 HOME>编辑 - 查找&选择,转到特殊...,空白,确定。
-
=
,UP,Ctrl + Enter。 -
全选,复制,粘贴特殊值,值。
-
全部选择
TRUE
和FALSE
在ColumnD中删除所选行。 -
在E2中复制以适应:
=替代(替代(C2,{&,,{),& B2,),,& B2
-
全选,复制,粘贴特殊,值,删除列C:D,输入
集群
在C1。
<
在D2中:
= A1<> A2
。 $ b
Is it possible to create the 'cluster' column in the image? Needing to concatenate the 'unit' values for all common 'overall' values, but not include itself as ones of the values being concatenated. Can I please get some help creating the 'cluster' column?
Each 'cluster' has all Units from the same Overall other than the Unit for the row for the result:
Overall Unit Cluster
Overall1 UnitA1 {UnitA2, UnitA3}
Overall1 UnitA2 {UnitA1, UnitA3}
Overall1 UnitA3 {UnitA1, UnitA2}
Overall2 UnitA4 {UnitA5}
Overall2 UnitA5 {UnitA4}
Overall3 UnitB62 {UnitB63, UnitB64, UnitB65}
Overall3 UnitB63 {UnitB62, UnitB64, UnitB65}
Overall3 UnitB64 {UnitB62, UnitB63, UnitB65}
Overall3 UnitB65 {UnitB62, UnitB63, UnitB64}
Thinking it could be a two step process, concatenate then maybe a search and replace?
Can be achieved with formulae, but rather a rigmarole. Assumes Sorted as indicated and Overall
is in A1:
In C1
Cluster
, in C2 (labels assumed in Row1):=IF(A1=A2,C1&", "&B2,"{"&B2)
In D2:
=A1<>A2
Copy both formulae down to suit.
Select all, Copy, Paste Special, Values.
Select all, DATA > Outline - Subtotal, At each change in:
Overall
, Use function Count, Add subtotal to:(ColumnD)
, Replace current subtotals, Summary below data, OK.Filter and for ColumnA, Text Filters, Contains...,
co
, OK.In first row visible below header (assumed to be Row5) in ColumnC enter:
=C4
and copy down to suit.
Take off Filter, select all, Copy, Paste Special, Values.
Select ColumnA, HOME > Editing - Find & Select, Replace..., Find what:
Count
, Replace All. (There is a space before theC
ofCount
.)Select all, DATA > Outline, Subtotal, Remove All. Delete
Grand
row.Filter to select all but
TRUE
andFALSE
in ColumnD and insert1
in first blank cell of ColumnB and copy down to suit. Take off Filter.Sort by ColumnA Values, A to Z, then by Column B Values, A to Z.
Filter ColumnD to select
TRUE
andFALSE
and delete contents of ColumnC for the selected rows (leave label).Take off Filter and select ColumnC. HOME > Editing - Find & Select, Go To Special..., Blanks, OK.
=
, UP, Ctrl+Enter.Select all, Copy, Paste Special, Values.
Select all but
TRUE
andFALSE
in ColumnD and delete selected rows.In E2 copied down to suit:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"{"&B2&", ","{"),", "&B2,""),", "&B2,"")&"}"
Select all, Copy, Paste Special, Values, delete Columns C:D, enter
Cluster
in C1.
.
这篇关于按组排列,不包括来自同一行的组成员的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!