Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to sort through a a few excel columns and can't seem to nail down an easier way besides manually going through line by line. Column A has names where names can repeat, column b has state initials, column c has a number, column d has a start date and column e has an end date. So a name in column a could have say five different states each corresponding to a unique number in column c. I need something that would go through and for each name check if the name had any of the 50 states (plus dc). if they have a state in column b, then I need the values corresponding in column c, d and e. If they don't have that state, return an N/a or blank.

For example, Name1 has AZ and returns number 1234 with start date 01/01/2014 and end date 05/05/2014. Name1 doesn't have CA so returns N/A, N/A, N/A.

Any help is appreciated!

share|improve this question
Version please? (add the relevant tag to the question) – gparyani Jan 31 '14 at 1:35
It is version 2007 – Kyle Jan 31 '14 at 1:41
In your example, how do you know "CA" is a member of "States"? Is this perhaps derived from the list of all state values in column B (regardless of association with column A)? If this assumption is correct, a pivot table might work. – andy holaday Jan 31 '14 at 2:57
Have you tried using a filter? – Excellll Jan 31 '14 at 14:59
The question is not clear. In your example AZ is part of the 50 states, so why would you return N/A ? – P. Obertelli May 13 '15 at 16:15

You must log in to answer this question.

Browse other questions tagged .