1. Blogs
  2. TDrag27's blog
  3. Excel Nerds - Be my Hero!

Excel Nerds - Be my Hero!

TDrag27

Shared on Wed, 02/04/2009 - 16:32

I have a spreadsheet that is about 4000 records long that I'm working on. There's an identifying number and then a piece of information attached to it.

For example:

1 Horse

1 Cat

1 Dog

2 Cow

3 Dog

4 Cat

4 Mouse

And so on and so forth....So - there are duplicate numbers that indicate the same person and non-duplicating pieces of information accompanying those people. I know how to remove duplicates in Excel 07 and I know how to concatenate. But is there a way to conditonally concatenate based on duplication so that my data could look like this below?

1 Horse, Cat, Dog

2 Cow

3 Dog

4 Cat, Mouse

Googling sucks and there's no way I'm doing this by hand or by copying concatenate forumulas through subtotals - I'm thinking it can't be done and that I'll just have to have someone do it in sql...So I thought I would just ask...

Thanks if you can help. It's your brain teaser for the day!

 

Comments

NormalGuy's picture
Submitted by NormalGuy on Thu, 02/05/2009 - 08:50
Are the numbers in the cell with the name of the item? Meaning does one cell say "1" and the other cell say "Horse" or does the cell say "1 Horse". NG
NormalGuy's picture
Submitted by NormalGuy on Thu, 02/05/2009 - 08:53
Also, are you dealing with a large number of #s? You could pretty easily copy, paste, find, replace and concatenate if you only had 10 different categories. NG
J-Cat's picture
Submitted by J-Cat on Wed, 02/04/2009 - 16:41
I can ask my excel guru tomorrow at work. good luck until then!
Lbsutke's picture
Submitted by Lbsutke on Wed, 02/04/2009 - 16:54
you could do it in Access. If I am understanding you correctly, a cell has the entry 1 horse, 2 cow and so on or are the 1 and the word in seperate cells?
nae's picture
Submitted by nae on Wed, 02/04/2009 - 16:58
if its a database you are looking to create girlie i agree use access :)
r0per16's picture
Submitted by r0per16 on Wed, 02/04/2009 - 17:08
sorry i don't have the answer but... here are a link of sites that i use to help me with problems like this with MS office stuff. http://www.mvps.org hope this helps.
GroovyElm's picture
Submitted by GroovyElm on Wed, 02/04/2009 - 17:10
Maybe a pivot table? That would work better if you went the other way for sure, and if the 1 and dog were in seperate cells. Somehow you can do column headings to sort by as well. Autosort or something that you can limit your view to the qty of 1 or description field.
GroovyElm's picture
Submitted by GroovyElm on Wed, 02/04/2009 - 17:16
we prefer the term spreadsheet jockey BTW
LtBlarg's picture
Submitted by LtBlarg on Wed, 02/04/2009 - 17:59
first thing that came to my mind was a pivot table
VenomRudman's picture
Submitted by VenomRudman on Wed, 02/04/2009 - 19:41
The first thing I thought of, "it's time to learn macros....". But then again, I'm a programmer.....
TDrag27's picture
Submitted by TDrag27 on Wed, 02/04/2009 - 21:16
Macros are good for work that needs to be repeated...But this is a one-time analysis.
hubristes's picture
Submitted by hubristes on Thu, 02/05/2009 - 21:45
There are easier ways, but here's a simple one: Start with a nested if-then statement to test for same numbers in the row above. If it's the same, then add the "animal" or whatever to the text string. The result would give you this: 1 Horse 1 Horse Cat 1 Horse Cat Dog 2 Cow 3 Dog 4 Cat 4 Cat Mouse Then you'd just need to add a forumla to test for the longest text string and get rid of the garbage. I assume you're already done with this. If not, I hope the above made sense or will at least narrow down your googling. Feel free to shoot me an email hubristes@yahoo.com

click for more 2O2P member action

click for more 2O2P member action

© 2007 Media Crumb Inc. All rights reserve Media Crumb Inc.

Join our Universe

Connect with 2o2p