0

In excel I have two worksheets. The first worksheet is like follows:

1   1
2   0
3   0
4   0
5   0
6   0
7   0
8   0
9   0
10  1

and the second worksheet is like follows:

1   1
2   0
3   0
4   0
9   1
10  1
11  0
12  0
13  1
14  0

(they do not necessarily contain the same number of elements). The first column is some ID (which might differ in the worksheets) and the second column is a flag whether to take this element in the combined worksheet. The combined worksheet should look like:

1
9
10
13

i.e, this worksheet contains only those elements which have a '1' in any of the other two worksheets (second column).

How can I achieve this WITHOUT MACRO? Is there a solution?

Alex
  • 41,580
  • 88
  • 260
  • 469
  • Are there then only two worksheets in reality, or does any solution need to be extendable to work for an arbitrary number of worksheets? – XOR LX Jun 26 '17 at 14:07
  • No, only two (there are more columns in reality, but once I understand a workable solution for one column I should be able to adapt it to multiple columns. But there are only TWO worksheets from which a third, combined worksheet should be created) – Alex Jun 26 '17 at 14:08
  • And is your posted data realistic in the sense that the first column's values are always numeric? – XOR LX Jun 26 '17 at 14:12
  • The first column's values are just some values. Can be anything (in fact, in reality these are more columns), but the SECOND column is always numerical... – Alex Jun 26 '17 at 15:02
  • The problem is easier (solved) if you want all the values with `B=1`, but it's much more difficult because you want them *unique*... – A.S.H Jun 26 '17 at 15:17

1 Answers1

0

Append one to the other in a new sheet, sort on the first column, Remove Duplicates (check both columns), Filter to select 0 in the second column and delete those rows and that column.

An alternative for updating with just a single click is a PivotTable constructed from multiple consolidation ranges with Sum of Value for VALUES and Value filtered to select 1 (and possiby hide all but the first column).

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Does this work automatically when the content in any of the other two sheets change? – Alex Jun 26 '17 at 15:27