英文:
Filtering for Unique Values with different ID Numbers
问题
我一直在尝试从一个标签筛选出唯一值到另一个标签。然而，在两个标签中，我都有一个ID列。我基本上想将唯一的值与它们各自的ID编号对齐，然后使用筛选视图来隐藏空白。
任何帮助将不胜感激。
谢谢
我使用了这个公式：
={"PO#", "MP#";ARRAYFORMULA(IFERROR(VLOOKUP(A4:A,'SHEET1'!A5:H,{7,8},0))}
然后得到了这个：
={"PO#", "MP#";ARRAYFORMULA(IFERROR(VLOOKUP(A4:A,FILTER('SHEET1'!A5:H,UNIQUE('SHEET1'!G5:G)),{7,8},0))}
第二个标签中的PO#和MP#是具有重复项的值，并将从SHEET1的列G和H中导入。两个标签的ID列在列A中（它们只是从不同位置开始）。
我期望具有重复的PO#和MP#的行为空，直到下一个唯一行。
我还链接了一个Google表格在此处来说明我的问题。
谢谢
英文:
I have been trying to filter for unique values from one tab to another. However in both tabs, I have an ID Column. I basically want to align the Unique values to their respective ID Number, then use the filter view to hide the blanks.
Any help will be appreciated.
Thanks
I used this formula:
={"PO#","MP#";ARRAYFORMULA(IFERROR(VLOOKUP(A4:A,'SHEET1'!A5:H,{7,8},0)))}
and came up with this:
={"PO#","MP#";ARRAYFORMULA(IFERROR(VLOOKUP(A4:A,FILTER('SHEET1'!A5:H,UNIQUE('SHEET1'!G5:G)),{7,8},0)))}
PO# and MP# in the second tab are the values with duplicates and will be imported from SHEET1's Column G and H respectively. Both tab's ID Column are in Column A (They just start in different positions).
I expected the rows with duplicated PO# and MP# to be blank, until the next unique row.
I have also linked a Google Sheets here to illustrate my problem.
Thanks
答案1
得分: 0
以下是翻译好的内容：
一种方法是使用 vlookup() 和 unique()，如下所示：
={ Sheet1!A1:A }
=arrayformula(
iferror(
vlookup(
A1:A,
vlookup(
unique(Sheet1!B1:B),
{ Sheet1!B1:B, Sheet1!A1:C },
sequence(1, columns(Sheet1!A1:C), 2),
false
),
{ 2, 3 },
false
)
)
)
请查看 解决方案 选项卡。
英文:
One way is to use vlookup() and unique(), like this:
={ Sheet1!A1:A }
=arrayformula(
iferror(
vlookup(
A1:A,
vlookup(
unique(Sheet1!B1:B),
{ Sheet1!B1:B, Sheet1!A1:C },
sequence(1, columns(Sheet1!A1:C), 2),
false
),
{ 2, 3 },
false
)
)
)
See the Solution tab.
答案2
得分: 0
已添加公式到您的表格:
=BYROW(A2:A, LAMBDA(z, IF(z="", , INDEX(IFNA(VLOOKUP(z, FILTER({Sheet1!A:C}, BYROW(Sheet1!B:B, LAMBDA(Σ, COUNTIF(Sheet1!B1:Σ, Σ)))=1), {2,3}, )))))
英文:
Added formula to your sheet:
=BYROW(A2:A,LAMBDA(z,IF(z="",,INDEX(IFNA(VLOOKUP(z,FILTER({Sheet1!A:C},BYROW(Sheet1!B:B,LAMBDA(Σ,COUNTIF(Sheet1!B1:Σ,Σ)))=1),{2,3},))))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论