« previous next »
Pages: [1]

Author Topic: copy and paste nested list into excel to include indents?  (Read 3457 times)

  • Offline zpyder

  • Posts: 6,946
  • Hero Member
copy and paste nested list into excel to include indents?
on: May 14, 2013, 17:11:02 PM
Basically, I am curious as to how many orders, families, genus's and species I have on my invertebrate website.

I have a sitemap as follows:

http://www.microphoto.co.uk/sitemap/

I figured the easiest way to find this information, would be to copy and paste the sitemap into excel, and then do a count for each column, assuming the indented sections carried across into new columns.

Sadly this isn't the case. I've tried paste special etc, also tried pasting into notepad++ etc, but those indents don't seem to copy over.

The reason I thought about doing the above was that it would save installing another widget that would need to be kept updated etc. It's basic, but would do the job.

Any suggestions?
Logged
Logged

  • Offline zpyder

  • Posts: 6,946
  • Hero Member
Re: copy and paste nested list into excel to include indents?
Reply #2 on: May 14, 2013, 21:17:24 PM
Thanks man.

Out of interest, how did you do that, so that in the future when I add the next batch of photos I can reproduce the results?
Last Edit: May 14, 2013, 21:23:54 PM by zpyder #187;
Logged
Re: copy and paste nested list into excel to include indents?
Reply #3 on: May 14, 2013, 21:27:21 PM
Copied the list from the webpage into word and then into excel, that kept the indent level.  Then managed to find a bit of vb on the net to tell me what the indent level is for each cell:

Function IndentCount(rCell As Range) As Long
    IndentCount = rCell.IndentLevel
End Function

Which went into the vb editor as a new module (never done that before myself but there after following a few links I managed it), that let me put the =indentcount() into column A.  Then its just a case of using an if statement to put the text into each cell depending on what level it is and if its not the right level put in nothing and lastly doing the formula at the bottom to count the number of cells then take away the number of cells that have nothing in them, by nothing its actually whatever is in between two quotes in excel (ie "") which might just be null but I can't remember.
Logged
Re: copy and paste nested list into excel to include indents?
Reply #4 on: May 14, 2013, 21:47:05 PM
That's quite interesting :)

To be honest though, if you only need the numbers and not separated visually in to it's respective tiers, then you could just drop the first column in to a pivot table or just use a countif:

Code: [Select]
=COUNTIF(A:A,1)
Replacing 1 with the tier you want to count
Logged
Re: copy and paste nested list into excel to include indents?
Reply #5 on: May 14, 2013, 21:52:14 PM
That's true, never really used pivot tables though, probably should get round to trying them out properly one day!
Logged
Re: copy and paste nested list into excel to include indents?
Reply #6 on: May 14, 2013, 22:04:10 PM
That's why I also included the countif :) Pivot tables are easy anyway, they just look scary at first but take a couple of minutes to learn and can save a lot of hassle
Logged
Re: copy and paste nested list into excel to include indents?
Reply #7 on: May 15, 2013, 02:14:54 AM
Copy paste.. Hit "convert text to columns" button on the data ribbon.

Specify the delimiter as the correct white space character. Bosh. Done.
Logged

  • Offline zpyder

  • Posts: 6,946
  • Hero Member
Re: copy and paste nested list into excel to include indents?
Reply #8 on: May 15, 2013, 09:29:44 AM
Quote from: M3ta7h3ad on May 15, 2013, 02:14:54 AM
Copy paste.. Hit "convert text to columns" button on the data ribbon.

Specify the delimiter as the correct white space character. Bosh. Done.

Can't seem to get that one to work, though that's about as complicated as I expected things to be. I hadn't expected Russells solution to require so much to achieve the desired result!

I've seen in word that when I paste the list, the indents aren't visible, but in the ruler at the top you can see there is a marker showing indents in the right places. Using space or tab as a delimiter in excel just seems to split species into 2 columns, nothing else really.

I feel pretty dumb not being able to do this :|
Logged
Re: copy and paste nested list into excel to include indents?
Reply #9 on: May 15, 2013, 13:25:42 PM
Quote from: zpyder on May 15, 2013, 09:29:44 AM
Quote from: M3ta7h3ad on May 15, 2013, 02:14:54 AM
Copy paste.. Hit "convert text to columns" button on the data ribbon.

Specify the delimiter as the correct white space character. Bosh. Done.

Can't seem to get that one to work, though that's about as complicated as I expected things to be. I hadn't expected Russells solution to require so much to achieve the desired result!

I've seen in word that when I paste the list, the indents aren't visible, but in the ruler at the top you can see there is a marker showing indents in the right places. Using space or tab as a delimiter in excel just seems to split species into 2 columns, nothing else really.

I feel pretty dumb not being able to do this :|

Don't worry I didn't expect it to be as involved and always thought there might be an easier way of doing it, I've used text to columns before as well but forgot about it.

Wouldn't say your dumb for not being able to do it, just that I've had to do similar things in the past and now you'll have an idea of how to do it in the future.
Logged
Pages: [1]
« previous next »

0 Members and 1 Guest are viewing this topic.