Robert Minneapolis
New Member
- Joined
- Aug 10, 2007
- Messages
- 5
Okay, I've got what I think is a relatively straightforward question about compressing a list in Excel. At work, I inherited a list in spreadsheet form that someone else was working on and I'd like to make it easier to manage. Picture this, if you can: Column A is a list of names. Columns B through E are each potential things that person could've done on a particular day. Let's say that columns B through E are "Apples", "Oranges", "Bananas", and "Pears", to use common Excel Help nomenclature. So if the name in column A is "Paul McCartney", and there is an X in column B, that means that Paul McCartney ate an apple that day. Clear enough?
Okay, so here's my dilemma: the list is thousands of names long, and the same names appear many times throughout the list. Sometimes the names might have an X in the same column (Paul may have eaten an apple on several different occasions), sometimes they might have an X in a different column (Paul may have eaten an apple one day and a pear the next). I'd like to compress this list so that each name appears only once. For instance, if Paul has ever eaten an apple and ever eaten a pear, the list should have his name with an X in column B and an X in column E. I'm not concerned with how many times he may have eaten an apple nor the specific dates that he did it, only that he did it at least once. I've tried writing a macro to jump through all sorts of COUNTIF permutations, but there must be an easier way to do this. I also have to add names to this list every day, and I'd like to figure out something that can tell me if the name I'm entering already appears in the list somewhere, and if so, if that person has already performed the action that I'm entering (to avoid consistenly creating duplicates).
What would you do in this situation? Has this question been answered here before? I wasn't really sure what search terms I could use to find something similar.
thanks in advance,
Robert
Okay, so here's my dilemma: the list is thousands of names long, and the same names appear many times throughout the list. Sometimes the names might have an X in the same column (Paul may have eaten an apple on several different occasions), sometimes they might have an X in a different column (Paul may have eaten an apple one day and a pear the next). I'd like to compress this list so that each name appears only once. For instance, if Paul has ever eaten an apple and ever eaten a pear, the list should have his name with an X in column B and an X in column E. I'm not concerned with how many times he may have eaten an apple nor the specific dates that he did it, only that he did it at least once. I've tried writing a macro to jump through all sorts of COUNTIF permutations, but there must be an easier way to do this. I also have to add names to this list every day, and I'd like to figure out something that can tell me if the name I'm entering already appears in the list somewhere, and if so, if that person has already performed the action that I'm entering (to avoid consistenly creating duplicates).
What would you do in this situation? Has this question been answered here before? I wasn't really sure what search terms I could use to find something similar.
thanks in advance,
Robert
