# Keeping Excel Rows Together



## tcassell (Jul 25, 2007)

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:


----------



## jimr381 (Jul 20, 2007)

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.


----------



## slurpee55 (Oct 20, 2004)

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).


----------



## jimr381 (Jul 20, 2007)

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?


----------



## slurpee55 (Oct 20, 2004)

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.


----------



## OBP (Mar 8, 2005)

I would Use Access!


----------



## jimr381 (Jul 20, 2007)

I have to Agree with OBP on that one hehe. I thought about suggesting Access early on.


----------



## slurpee55 (Oct 20, 2004)

I am baffled here - how would Access be better?


----------



## jimr381 (Jul 20, 2007)

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.


----------



## slurpee55 (Oct 20, 2004)

I was assuming it was like this:
Name1
Project1
Name2
Project2


----------



## bomb #21 (Jul 1, 2005)

tcassell said:


> 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:


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.  )


----------



## slurpee55 (Oct 20, 2004)

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!


----------



## slurpee55 (Oct 20, 2004)

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


----------



## bomb #21 (Jul 1, 2005)

Only good for 1 column.


----------



## OBP (Mar 8, 2005)

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.


----------



## slurpee55 (Oct 20, 2004)

True that it only works for one column at a time, but, hey, it is a lot easier than retyping 5000 names and addresses.
OBP, why am I not surprised? LOL One problem with the data I tend to get is that after, oh, 25 (or 50 or - well, no specific number) there is often a line or 3 restating the job batch number, date, and other junk. This varies from sample vendor to sample vendor.

Of course, this is not my thread...tcassell, are you there?


----------



## bomb #21 (Jul 1, 2005)

slurpee55 said:


> True that it only works for one column at a time, but, hey, it is a lot easier than retyping 5000 names and addresses ...


The TRANSPOSE array function is your friend. 



OBP said:


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


Hi OBP. From the info so far, I doubt this particular project even qualifies as a pseudo-d/base.


----------



## slurpee55 (Oct 20, 2004)

well, bomb, as a Blue Peter badge holder, I am not surprised you know the answer! 
And I agree with you about your note to OBP.


----------



## jimr381 (Jul 20, 2007)

I think I might have a sample Access Database floating around that tracks employees, projects and project participation. Let me know if you want it. It already has the many-to-many relationship established and everything.


----------



## slurpee55 (Oct 20, 2004)

Actually, we seem to be talking amongst ourselves...tcassell, why don't you post your spreadsheet the next time you are online here? Then we could work directly for you.


----------

