How to turn a list into a spreadsheet?

Excel XP
January 22, 2011 at 04:33:24
Specs: Windows XP
I could probably write a program in basic that would do this, but I just want to stick my info into excel and be done with it.

I need to take a list like this
Name: Abominable
Genus: Chiro
Scientific Name: chiro monstus
Rarity: Legendary Rare
Season: winter10
Location Details: Holiday Forest
Locations Found: Holiday Forest
Name: Abominable
Genus: Shisa
Scientific Name: shisa abomus
Rarity: Admin Distributed
Location Details: Seasonal Shop
Locations Found:
Name: Abyssal
Genus: Phanto
Scientific Name: phanto abyss
Rarity: Very Rare
Location Details: Fissures
Locations Found: Fissures

and turn it into a checklist type thing....
name | genus | scientific name |rarity | season | location details | locations found
Abyssal | Phanto | phanto abyss | very rare | N/A | fissures | fissures
Abominal | chiro | chiro monstus | Legendary rare| winter 2010 | holiday forest| holiday forest


Is there an easy way to make excel do that?


See More: How to turn a list into a spreadsheet?

Report •


#1
January 22, 2011 at 06:20:34
In Excel 2007 (& 2010 I guess) all you do is copy & paste the data into a single column in Excel, highlight the column, go to 'data', select 'text to columns' - go through the wizard selecting the colon as your delimiting text qualifier.

"I've always been mad, I know I've been mad, like the most of us..."


Report •

#2
January 22, 2011 at 06:42:37
re: "Is there an easy way to make excel do that?

Everyone's idea of "easy" is different, so that's a tough question to answer.

Your first "grouping" has a line item for Season but none of the other groups do.

If each group has exactly the same line items, which means that each group takes up the same number of rows, things get a little "easier".

Please clarify that.

In addition, please read the How To referenced in my signature line before posting any more data in this forum. The How To will explain how to format your data so that it is easier for us to read.

Thanks.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
January 22, 2011 at 07:58:12
johnr:

Text-To-Columns will only separate the data into 2 columns.

It will not transpose it into a table with the categories (Names, Genus, etc) used as column labels and the individual pieces of data list under the appropriate column.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 23, 2011 at 01:12:59
I knew there had to be a way to make the spaces stay, but my boyfriend was complaining about me still being up at 4am....

name       genus     scientific name     rarity       season    location details     locations found
Abyssal    Phanto    phanto abyss        very rare      N/A      fissures              fissures
Abominal   chiro     chiro monstus   Legendary rare  winter 2010   holiday forest       holiday forest


That's what I want it to look like, and there's probably 1000+ entries like those 2 (it was worse before I removed all the other crap from the text with text mechanic... this project mean I have to copy from a website 10 entries like those per page with pics and other stuff and then clean that up, and there are I think at least 100 pages!) Yes some have a 'season' field and some don't, and yes I need that field... well, maybe I could live without it - I'm already thinking of skipping the 'scientific name' field, I could probably do without the season for now since I'm mostly just wanting a checklist. Go ahead and pretend the 'season' line isn't in my data. And please be very clear in how to do what you suggest, I know practically nothing about excel except how to open cvs(?) files. If you can't help I'll probably just use Just Basic to write a little program to change the data to cvs(?) format - if line includes locations found then print line and then new line else print line and add a comma. That should put all of each pet into a separate line with commas between each field.... It's just with having to copy so much to text mechanic and all that, I really don't want to have to run it through another step before the spreadsheet.

Ayways I'm rambling..... if you can help I appreciate it


Oh yeah, if you could explain how the formula's etc that you suggest work that would be much appreciated (then maybe next time I can figure it out myself lol)


Report •

Ask Question