3

I have a table as below,

ID Name 2011 2012 2013 2014
A1 BOB Y
A1 BOB Y
A1 BOB
A1 BOB Y
B1 SAM
B1 SAM
B1 SAM Y Y
B1 SAM
B1 SAM Y
C1 TOM  Y
C1 TOM 
C1 TOM 
C1 TOM  Y
C1 TOM  Y
C1 TOM  Y Y
--- ---- ------ ---- ------ ----

I want to create a table as shown below. That is, only one final row for each ID, and if any row of that ID has "Y" in a particular year, the final row will have an "Y"

ID Name 2011 2012 2013 2014
A1 BOB Y Y Y
B1 SAM Y Y Y
C1 TOM  Y Y
--- ---- ------ ---- ------ ----

I googled but failed to find an answer

user12501522
  • 51
  • 3

2 Answers2

3

This is just one of a number of possible ways. If the tables are arranged as in the screenshot, put this formula:

=IF(COUNTIFS($A$2:$A$16, $A19, C$2:C$16, "Y") > 0, "Y", "")

in cell C19. Copy to the rest of the output table C19:F21 and you're done.

The COUNTIFS statement matches both the IDs in column A and the entries in each year column. The IF statement should be obvious. Be careful with the dollar signs in the references, they're critical here.

Solution spreadsheet

RichardCook
  • 846
  • 2
  • 10
1

Here an array version that spills the entire result, assuming no Excel version constraints as per the tag listed in the question:

=LET(in,A1:F16, h,TAKE(in,1), data,DROP(in,1),ux,UNIQUE(TAKE(data,,2)),
 REDUCE(h, SEQUENCE(ROWS(ux)), LAMBDA(ac,a, VSTACK(ac, HSTACK(INDEX(ux,a,),
  BYCOL(FILTER(DROP(data,,2), TAKE(data,,1)=INDEX(ux,a,1)),
   LAMBDA(f, IF(SUM(N(f="Y"))>0, "Y",""))))))))

Here is the output: output

This is just another application of REDUCE/VSTACK pattern (1). It depends on a single data range (in, i.e. easier to maintain), and then combining TAKE and DROP functions extract the necessary information. It iterates over all row index position of ux (Unique rows from the first two columns of data). On each iteration, concatenate the given row a via HSTACK with the output of BYCOL. This function uses as input the data with year information (without the header), filtered by iteration id (INDEX(ux,a,1)), then on each columns counts the numbers of Ys, in case the counts is greater than 0, put on that column Y otherwise an empty string.

(1): Check my answer to the following question: how to transform a table in Excel from vertical to horizontal but with different length.

David Leal
  • 6,373
  • 4
  • 29
  • 56