Excel Mapping with multiple matches

excel vba indexing vlookup

36 观看

1回复

59 作者的声誉

my knowledge of excel extends to most types of advanced formulas. I don't know much about how to use VBA or macros. I have a problem which I'm struggling to solve using formulas. I have a sheet with two columns that looks like this:

x1  y1
x1  y2
x1  y3
x1  y4
x2  y2
x2  y3
x2  y4
x3  y1
x4  y2

And I'm trying to map these onto a a sheet like this:

    y1  y2  y3  y4
 x1 1   1   1   1
 x2 0   1   1   1
 x3 1   0   0   0
 x4 0   1   0   0

I usually try to apply a vlookup solution to such problems, but I can't figure out how to get vlookup to work given that the x values appear multiple times in the first table, and vlookup will always just stop at the first appearance.

Please let me know how to best approach solving this problem.

Thanks so much!

作者: Ari Decter-Frain 的来源 发布者: 2017 年 12 月 27 日

回应 1


1

103166 作者的声誉

决定

Use COUNTIFS()

=COUNTIFS($A:$A,$D2,$B:$B,E$1)

enter image description here


But a pivot table may be better suited

enter image description here

作者: Scott Craner 发布者: 2017 年 12 月 27 日
32x32