I have a long list of people and the categories they belong in. Each person only has a 1 or no value in a category - its essentially a yes/no column. There are a number of duplicates that need to be combined based on the first column, ID number. The sheet looks like this:
A B C D E F
ID A1 A2 A3 A4 A5
A1005 1 1 1
A1005 1 1 1
A1006 1
A1006 1 1
I need to combine the rows so that A1005 has a 1 in A1, A3, A4 and A5, for instance. I can't do a Vlookup because it only looks for the first value. I tried sumif - making a new column G to combine just column B for instance, so G2 would =SUMIF($A$2:$B$5,"="&A2,$B$2:$B$5), and it kind of works, but it keeps putting 1s in the wrong rows. Sometimes it puts 1s correctly in rows 2 and 3, but sometimes in the rows below for no apparent reason. The plan would be to just paste the results of that formula back in column B as values, find and replace any values other than 1 with 1, and then remove duplicates. Any ideas?
Bookmarks