如何从重复的VBA水平返回返回的查找值[pic] [英] How to horizontally return looked up values from duplicates w VBA [pic]

问题描述

请参阅图片.我试图返回与数组中重复单元格对应的值==>水平.因此，查找的第一个实例在列E中进行，第二个实例在列F中，在G中进行第三次；等等.我可以使用数组公式来处理少量数据:

See pic related. I'm trying to return values corresponding to duplicate cells in an array ==>horizontally. So the first instance of a lookup goes in Column E, the second instance in Column F, third in G; and so-on. I can do that with an array formula for this small bit of data:

重复数组公式

但这是问题所在.

使用5、10或15,000+行的数组公式需要花费大量时间. 是否有VBA解决方案按列返回与数组中重复单元格相对应的值?

Using array formulas for 5, 10, or 15,000+ rows takes quite a bit of time. Is there a VBA solution to return values corresponding to duplicate cells in an array by columns?

推荐答案

我确定有非常出色的VBA解决方案，但是为什么不跳过所有内容并在数据透视表中执行呢?

I'm sure there are very performant VBA solutions, but why not just skip all that and do it in a pivot table?

提供您的数据:

其中附加字段列1"是根据公式

where the additional field 'Column 1' is built from the formula

="Payment_" & COUNTIF($A$2:A2,A2)

总结为数据透视表"很简单:

It's a snap to "summarize as pivot table" to this:

这篇关于如何从重复的VBA水平返回返回的查找值[pic]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！