Excel Problem Driving Me Nuts!

UPDATE: Problem solved through use of a pivot table! Thanks to everybody who helped! You've saved me innumerable future headaches! Much obliged!!!

—–

I have a problem with Microsoft Excel that surfaces in various forms from time to time, and I'm hoping that an Excel wiz out there can tell me what to do to solve it. I've tried Googling various ways of formulating the problem and haven't hit the right language to turn up the kind of result I'm after (assuming there is one).

Suppose I have a spreadsheet that looks like this:

EXCEL1

In column A I have some values, each of which is a duplicate (there are two cells that have 50, three that have 70, and two that have 12–and I've got the worksheet so duplicates are showing next to each other).

Each of these values is associated with another value in column B. These other values are either Q, R, or T.

What I want to do is find an automatic (non-manual) way of putting each of the column B values next to each duplicate in column A, like this:

EXCEL2
Here you can see that columns C-E do that.

The column A value 50 has a value of Q in B1 and R in B2, so columns C and D list both Q and R.

The column A value 70 has a value of Q in B3, R in B4, and T in B5, so columns C-E list Q, R, and T.

The column A value of 12 has Q and R in B6 and B7, so C and E list Q and R, just like they did for the value 50.

See what I'm getting at?

I'm basically trying to take data from one line and make it appear on every line that has that same duplicate value. That way (among other things), if I later eliminate duplicates, I'll retain all this data because it's been copied to each line with the value.

This problem has frustrated me for years and keep coming up in different guises.

I know this is the kind of thing that databases do real well, but I don't know beans about database programming.

Any ideas how to solve it in a spreadsheet like Excel?

—–

UPDATE: Problem solved through use of a pivot table! Thanks to everybody who helped! You've saved me innumerable future headaches! Much obliged!!!

Author: Jimmy Akin

Jimmy was born in Texas, grew up nominally Protestant, but at age 20 experienced a profound conversion to Christ. Planning on becoming a Protestant seminary professor, he started an intensive study of the Bible. But the more he immersed himself in Scripture the more he found to support the Catholic faith, and in 1992 he entered the Catholic Church. His conversion story, "A Triumph and a Tragedy," is published in Surprised by Truth. Besides being an author, Jimmy is the Senior Apologist at Catholic Answers, a contributing editor to Catholic Answers Magazine, and a weekly guest on "Catholic Answers Live."

12 thoughts on “Excel Problem Driving Me Nuts!”

  2. Have you tried using MS Access? It is very easy to use (better than most other office programs) and is a good way to learn database principals. It has a pretty simple interface for converting an Excel file to a database.
    Could this work as the “transformed” data set?
    50 Q R
    70 Q R T
    12 Q R
    This could be do-able using VBA programming.

  3. Jimmy- I think it will depend on how you want to use the data. A solution like a pivot table might be helpful. Also Access doesn’t necessarily require any programming really- you could suck this spreadsheet into access as a table then create an easy query with some dragging and dropping, not needing any coding to accomplish. email me if you want to bounce more specifics off me. -Liz by grace at gmail

  4. eBeth: Yes! Your transformation of the data is the ultimate result I’m trying to achieve: What secondary values are associated with each primary value. I would be ecstatic if I could produce that kind of output.
    Liz: I’m afraid I don’t have Access installed, and am inexperienced using it as well. I’ve got Microsoft Works Database, but have no clue how to use it (and there seem to be few handy tutorials for it).

  5. Try the insert pivot table. Select your dataset and then do Insert pivot table to another sheet in same workbook. If you tinker a bit I think you’d see exactly what you want…

  6. I just emailed the address on your contact page with a sample excel with this data on one tab and a pivot that works on another. see if this organizes it or groups it in such a way that the data is useful.

  7. You could use the IF command here.
    In your example, if you make 2 empty rows at the top, the following codes work:
    Column C: =IF(A3, “Q”, “”)
    Column D: =IF(OR(A3=A4, A3=A2), “R”, “”)
    Column E: =IF(OR(AND(A3=A2, A3=A1), AND(A3=A2, A3=A4), AND(A3=A4, A3=A5)), “T”, “”)
    Then just use the little black box to formula drag for however many rows you have to fill.
    Basically it is doing a check on whether:
    i) in column C there is a value in column A and returns Q if there is;
    ii) in column D the value in column A which matches the value in the cell above or below and returns R if there is;
    iii) in column E the value in column A matches at least two other values in the value two cells above or below and returns T if there is.
    This would not work so well for your use for redundancy, because it is dependent on the values remaining in column A.

  8. Those forumulas would go into cells C3, D3, and E3, respectively, after having added the two empty rows at the top, in your example.

  9. You should be able to sort the first two columns by using the first column as the first sort key and the second column as the second sort key (don’t forget to select both columns before you sort!). It won’t list the values horizontally, but you’ll easily see those grouped values of column B that belong to each value of column A. Then depending to what extent the data repeats, you can eliminate duplicates simply by identifying what rows that repeat the data found in the row above. Displaying data horizontally with repetition is really just a formatting preference. That would honestly take more programming than you are likely ready for! 🙂

  10. You can use Java to connect directly to Excel (or Access) and do inserts, updates, and deletes. Here’s an example:
    http://prafullan-technical.blogspot.com/2011/03/you-can-use-jdbcsql-query-in-java-for.html
    I’d do a select of each row in the initial 2-column table and store the results in a Hashmap as I processed each row, storing the column A key and adding the column B value to a List. If the key is not yet in the Hashmap, create a new List and add the first B value and store it with that key. Otherwise, if a List already exists for the A key, just add the B value to the List that’s already associated with that A key. Finally, go through all the keys in the HashMap and print out the key and the values in each key’s associated List.

  11. Glad you got it resolved. A pivot table is the simplest resolution – wish I would have thought of it!

Comments are closed.