将具有唯一标题的唯一数据从一列转换为新表中的1行,其中包含1个以上带有标题的分隔符列 [英] Transpose unique data from column into 1 row in new sheet with more than 1 separator columns with headers
问题描述
在寻找要使用的函数时,我发现以下公式可用于调整.
While searching for a function to use, I found the below formula to adapt from.
=TRANSPOSE(ArrayFormula(TRIM(SPLIT(QUERY(A1:A30&"|"&rept("|",1),,2^99),"|",1,0))))
到目前为止有效的方法是使用以下公式,但
What has worked so far is the use of the below formula but
-
我似乎无法将其更改为多个分隔符列.
= {(ArrayFormula(TRIM(SPLIT(QUERY(UNIQUE(A2:A)&"|"& rept("|",1),, 3 ^ 99),"|",1,0) )))}
={(ArrayFormula(TRIM(SPLIT(QUERY(UNIQUE(A2:A)&"|"&rept("|",1),,3^99),"|",1,0))))}
此外,是否可以按以下示例顺序将标头添加到当前为空的分隔符列:"qty","expiry","available qty"
在下一列之前,然后再次重复"qty","expiry","available"数量"
Also, is it possible to add headers to the currently empty separator columns in the sequence of example: "qty","expiry","available qty"
before the next column and repeat again "qty","expiry","available qty"
在单元格I1中测试电子表格(由于存在限制,联接功能不起作用): https://docs.google.com/spreadsheets/d/1w64RQd_qqlQrQdQ7Q edit#gid = 799170483
Test spreadsheet in at cell I1 (Join function doesn't work as there's a limit): https://docs.google.com/spreadsheets/d/1w64R9QR_pAvdGy7IZLsHFvhIYIGL7lqsl_vFXrqsQMc/edit#gid=799170483
推荐答案
请使用以下公式并根据您的需要调整范围:
Please use the following formula and adjust ranges to your needs:
={(ArrayFormula(TRIM(SPLIT(QUERY(UNIQUE(A2:A)&"|qty|expiry|available qty"&rept("|",1),,2^99),"|",1,0))))}
使用的功能:
ArrayFormula
TRIM
SPLIT
QUERY
UNIQUE
这篇关于将具有唯一标题的唯一数据从一列转换为新表中的1行,其中包含1个以上带有标题的分隔符列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!