2OLD2PLAY
Search

Excel Nerds - Be my Hero!

T
TDrag27

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 (12)

J
J-Cat·
I can ask my excel guru tomorrow at work. good luck until then!
L
Lbsutke·
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?
N
nae·
if its a database you are looking to create girlie i agree use access :)
R
r0per16·
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.\r \r http://www.mvps.org\r \r hope this helps.
G
GroovyElm·
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.
G
GroovyElm·
we prefer the term spreadsheet jockey BTW
L
LtBlarg·
first thing that came to my mind was a pivot table
V
VenomRudman·
The first thing I thought of, \"it's time to learn macros....\". But then again, I'm a programmer.....
T
TDrag27·
Macros are good for work that needs to be repeated...But this is a one-time analysis.
N
NormalGuy·
Are the numbers in the cell with the name of the item? \r \r Meaning does one cell say \"1\" and the other cell say \"Horse\" or does the cell say \"1 Horse\".\r \r NG
N
NormalGuy·
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.\r \r NG
H
hubristes·
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:\r 1 Horse\r 1 Horse Cat\r 1 Horse Cat Dog\r 2 Cow\r 3 Dog\r 4 Cat\r 4 Cat Mouse\r \r 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\r hubristes@yahoo.com