Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 145514 times)

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11943
  • Total likes: 1973
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1440 on: January 10, 2021, 05:08:46 PM »
I have a google sheet with a column with links to an mp3, a column for artist and a column for title, is there any way to have it automatically download all the mp3s with the filename based on the artist and title column?
This extension might help https://www.downthemall.net/

I don't know how it determines the file names of the downloaded files - but if it uses the display text, you could use formulas to prep an html file with all the links, save locally on your computer, and then use the extension to download them
Workflowy. You won't know what you're missing until you try it.

Offline Yo ssi

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2019
  • Posts: 3378
  • Total likes: 1024
  • DansDeals.com Hat Tips 38
  • Gender: Male
    • View Profile
  • Programs: @myi so list them as Dan does!
Re: Excel Help and Problems
« Reply #1441 on: January 10, 2021, 07:13:51 PM »
This extension might help https://www.downthemall.net/

I don't know how it determines the file names of the downloaded files - but if it uses the display text, you could use formulas to prep an html file with all the links, save locally on your computer, and then use the extension to download them
Thanks that's exactly the question, I doubt it has the ability to custom name it. I assume it would just use the file name as uploaded

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11943
  • Total likes: 1973
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1442 on: January 10, 2021, 07:19:15 PM »
Thanks that's exactly the question, I doubt it has the ability to custom name it. I assume it would just use the file name as uploaded
I figured as well. If you do end up downloading them with the existing filenames, you can use the program mp3tag to rename them and set the metadata. It's a great program.
Workflowy. You won't know what you're missing until you try it.

Offline Yo ssi

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2019
  • Posts: 3378
  • Total likes: 1024
  • DansDeals.com Hat Tips 38
  • Gender: Male
    • View Profile
  • Programs: @myi so list them as Dan does!
Re: Excel Help and Problems
« Reply #1443 on: January 10, 2021, 07:26:59 PM »
I figured as well. If you do end up downloading them with the existing filenames, you can use the program mp3tag to rename them and set the metadata. It's a great program.
Thanks. The problem is the original file names are very random, so I won't know what's what..

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11943
  • Total likes: 1973
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1444 on: January 10, 2021, 07:28:46 PM »
Thanks. The problem is the original file names are very random, so I won't know what's what..
Based on your google sheet. How long is the list?
Workflowy. You won't know what you're missing until you try it.

Offline Yo ssi

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2019
  • Posts: 3378
  • Total likes: 1024
  • DansDeals.com Hat Tips 38
  • Gender: Male
    • View Profile
  • Programs: @myi so list them as Dan does!
Re: Excel Help and Problems
« Reply #1445 on: January 10, 2021, 07:30:19 PM »
Based on your google sheet. How long is the list?
My precise use case is for hundreds of files, the above was an example to make it clear. (wouldn't be asking otherwise ;))

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11943
  • Total likes: 1973
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1446 on: January 10, 2021, 07:31:08 PM »
My precise use case is for hundreds of files, the above was an example to make it clear.
Hmm, it might be worth looking into writing a google apps script for that. IDK for sure that it could do this but I would hope it can.
Workflowy. You won't know what you're missing until you try it.

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5093
  • Total likes: 532
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Help and Problems
« Reply #1447 on: January 25, 2021, 09:34:47 PM »

Online AsherO

  • Global Moderator
  • Dansdeals Lifetime 20K Presidential Platinum Elite
  • **********
  • Join Date: May 2008
  • Posts: 24578
  • Total likes: 2348
  • DansDeals.com Hat Tips 75
    • View Profile
  • Location: NYC
Re: Excel Help and Problems
« Reply #1448 on: January 25, 2021, 10:49:38 PM »
I have a google sheet with a column with links to an mp3, a column for artist and a column for title, is there any way to have it automatically download all the mp3s with the filename based on the artist and title column?

If itís a one time thing, download to Excel and find a macro that does this,  Iím confident Google will locate one.

Offline Yo ssi

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2019
  • Posts: 3378
  • Total likes: 1024
  • DansDeals.com Hat Tips 38
  • Gender: Male
    • View Profile
  • Programs: @myi so list them as Dan does!
Re: Excel Help and Problems
« Reply #1449 on: January 25, 2021, 10:52:04 PM »
If itís a one time thing, download to Excel and find a macro that does this,  Iím confident Google will locate one.
Thanks.

I ended up paying a professional since it was time sensitive. If anyone would like the code PM me.

Offline Definitions

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jan 2018
  • Posts: 1232
  • Total likes: 215
  • DansDeals.com Hat Tips 3
    • View Profile
Re: Excel Help and Problems
« Reply #1450 on: March 09, 2021, 07:19:55 PM »
Any reliable way that I can insert a screenshot into a cell then either manually make it smaller or have it pop out only when clicked on.

It has to be obvious which row it's on.

Will just pasting it into the cell mess up the positioning once I email the file?

Offline Definitions

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jan 2018
  • Posts: 1232
  • Total likes: 215
  • DansDeals.com Hat Tips 3
    • View Profile
Re: Excel Help and Problems
« Reply #1451 on: March 09, 2021, 07:37:40 PM »
Nevermind. It seems to be working fine

Offline justaregularguy

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Sep 2014
  • Posts: 999
  • Total likes: 394
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: not brooklyn
Re: Excel Help and Problems
« Reply #1452 on: March 09, 2021, 11:19:02 PM »
I dont really have a question as much as an insight.

Vlookup is NOT case sensitive. I know in general this isnít a problem for most ppl using vlookup. But I had to match hundreds of IDís and I thought i got it right w the vlookup. Then I noticed an ID  like aeB300000nK was being matched with a file thatís ID was really aeB300000nk (Notice the lower case k)
So it messed up the vlookup for those .
So there are some ways to vlookup case sensitive Iím curious to know ddfers methods on doing this. One formula I found for some reason was not working using exact() and row()

Anyway, I found a cool small macro thatís does the job quite well and works just like vlookup :)

If anyoneís interested in the macro let me know :)
nothings impossible- the word itself says Im possible

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11943
  • Total likes: 1973
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1453 on: March 09, 2021, 11:20:25 PM »
I don  really have a question as much as an insight.

Vlookup is NOT case sensitive. I know in general this isnít a problem for most ppl using vlookup. But I had to match hundreds of IDís and I thought u got it right w the vlookup. Then I noticed an ID  like aeB300000nK was being matched with a file thatís ID was really aeB300000nk (Notice the lower case k)
So it messed up the vlookup for those .
So there are some ways to vlookup case sensitive Iím curious to know ddfers methods on doing this. One formula I found for some reason was not working using exact() and row()

Anyway, I found a cool small macro thatís does the job quite well and works just like vlookup :)
You can use a combination of INDEX and MATCH for this. I don't know it offhand, I just google it when relevant and copy/paste.
Workflowy. You won't know what you're missing until you try it.

Offline justaregularguy

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Sep 2014
  • Posts: 999
  • Total likes: 394
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: not brooklyn
Re: Excel Help and Problems
« Reply #1454 on: March 09, 2021, 11:22:07 PM »
You can use a combination of INDEX and MATCH for this. I don't know it offhand, I just google it when relevant and copy/paste.
itís funny Iím familiar w some formulas and totally not others
I never use index match
What wud the formula be?
nothings impossible- the word itself says Im possible