1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Keeping Excel Rows Together

Discussion in 'Business Applications' started by tcassell, Jul 25, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. tcassell

    tcassell Thread Starter

    Joined:
    Jul 25, 2007
    Messages:
    1
    I have an Excel spreadsheet that contains 2 rows for each record. For example, on row 1 in the person's name and the row underneath it is the project name. I want to use the autofilter and keep these 2 rows together when I filter. Can this be done? Or what is the best way to keep these 2 rows together?
    Thanks:
     
  2. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    Excel thinks of each row as a record. That being said you cannot have it group the two rows together for filtering purposes that I know of. What you could do is enable text wrapping and have it look like multiple rows, but in fact have it is as multiple lines within the cell. Can you post your Excel spreadsheet so I can take a gander at it. Make sure you know that I am gandering and not goosing.
     
  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    You could concatenate the data together, and I am sure someone could write some VBA to pull it together even better (a column of concatenations will give you 1a 1b, 1b 2a, 2a 2b, 2b 3a, 3a 3b, 3b 4a, 4a 4b when all you want is 1a 1b, 2a 2b, 3a 3b, 4a 4b).
     
  4. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    The only issue that I would foresee would be that he/she will have with concatenation is that he is trying to apply filters to the data. When filtering Excel sees each row as a record and as such try to filter the second row as well. Why are you trying to put a second row of data below the first?
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Well, actually, what I would do is do the concatenation. It would give me garbage on every other line but that could be dealt with several ways - the easiest (for me) would be to use ASAP Utilities ( http://www.asap-utilities.com/ ) free add-on for Excel to delete every 2nd row. Alternatively, you could number the rows, divide by 2, copy, paste special values on that column, do a text-to-columns on those numbers with it delimited by a period. This will give you a column with a blank beside each even number and a .5 beside each odd number. Then you could sort by the blank/.5 column or filter by it.
    Once you got rid of the garbage data (I would delete after such a sort, if I didn't have ASAP) he is free to deal with his data however he wants.
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    I would Use Access!:D
     
  7. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    I have to Agree with OBP on that one hehe. I thought about suggesting Access early on. ;)
     
  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I am baffled here - how would Access be better?
     
  9. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    If he/she had multiple datasets for each row and that is the reason why he was using multiple rows then he could setup a 1 to many relationship. I was only trying to visualize since I did not see a post with the actual spreadsheet. :D
     
  10. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I was assuming it was like this:
    Name1
    Project1
    Name2
    Project2
     
  11. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,271
    There's no "best" way to do this, because your set-up goes entirely against what's generally considered to be a golden rule -- "1 row per record". You should seriously consider restructuring your data -- is there no way you can have (e.g.) "Project Name" as an additional field?

    (That said, I have known projects where there really was no other way. If you can't restructure, try adding an extra field named (e.g.) "Filter", enter the project name in this in both rows of each "record". Then filter by that instead. But you'd also have to "double up" in any other fields you might need to filter by. :( )
     
  12. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Sadly, sometimes I get samples for doing market research that are set up in this style (only worse Name, Address, City, State, Phone - all for one person and in cells one above the other in Excel and I have to clean that mess).
    If there is a neat way to have Access extract this into various tables in a sensible fashion, I would love to see it!
     
  13. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Actually, assuming I am right about the layout in post 10, the ASAP utility I mentioned in post#5 will fix this; Get it and install it into your Excel.
    Go to the utilities, click on format, go to Advanced Transposer. Have it convert from 1 column to 2 columns. It will change it to:
    [Name1][Project1]
    [Name2][Project2]
    (I am using the [] to indicate cells)

    :) I thought I had figured out a way around this before!

    See a screenshot here: http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=123&utilities=Format
     
  14. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,271
    Only good for 1 column. :(
     
  15. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    slurpee, I can provide you with the utility for post #12, either to do it in Excel or import it in to Access.
    That is assuming that you can supply example data and it normally remains stable.

    I still think that Databases should be in Access assuming that the user already has Access that is.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/600412