Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Keeping Excel Rows Together


(!)

tcassell's Avatar
tcassell tcassell is offline
Computer Specs
Junior Member with 1 posts.
THREAD STARTER
 
Join Date: Jul 2007
Experience: Intermediate
25-Jul-2007, 12:57 PM #1
Keeping Excel Rows Together
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's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
25-Jul-2007, 01:30 PM #2
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's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
25-Jul-2007, 03:37 PM #3
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's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
26-Jul-2007, 10:07 AM #4
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's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
26-Jul-2007, 10:23 AM #5
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,617 posts.
 
Join Date: Mar 2005
Location: UK
26-Jul-2007, 10:25 AM #6
I would Use Access!
jimr381's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
26-Jul-2007, 10:27 AM #7
I have to Agree with OBP on that one hehe. I thought about suggesting Access early on.
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
26-Jul-2007, 10:41 AM #8
I am baffled here - how would Access be better?
jimr381's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
26-Jul-2007, 10:57 AM #9
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's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
26-Jul-2007, 11:16 AM #10
I was assuming it was like this:
Name1
Project1
Name2
Project2
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
26-Jul-2007, 02:38 PM #11
Quote:
Originally Posted by tcassell
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's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
26-Jul-2007, 02:47 PM #12
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's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
26-Jul-2007, 03:01 PM #13
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-u...ilities=Format
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
26-Jul-2007, 03:10 PM #14
Only good for 1 column.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,617 posts.
 
Join Date: Mar 2005
Location: UK
26-Jul-2007, 03:13 PM #15
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.
__________________
OBP
I do not give up easily
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑